この記事では、MySQLのテーブルデータをExcelのシート上に設置されたActiveXコントロールのコンボボックスに登録する方法についてご説明します。
ちなみに、今回使うコンボボックスはActiveXコントロールのコンボボックスです。
フォームコントロールのコンボボックスを使った例は以下の記事で説明していますのでこちらも見て頂けたら幸いです。
【ExcelVBA】MySQLのテーブルデータをExcelのシート上に設置されたフォームコントロールのコンボボックスに登録するには
【動画】MySQLのテーブルデータをExcelのシート上に設置されたActiveXコントロールのコンボボックスに登録する実際の動き
本題に入る前に、まずは次の動画をご覧ください。
Connectionインスタンスの生成してMySQLのデータベースへの接続情報を用意したらConnectionインスタンスのConnectionStringプロパティに代入し、Openメソッドを実行することで、マクロがMySQLのデータベースに接続しています。
マクロがMySQLのデータベースに接続したらSQLのSELECT文を実行し、取得したデータをコンボボックスに登録しています。
取得したデータをコンボボックスに登録するには、AddItemメソッドの引数にコンボボックスに登録する値を指定して実行します。
マクロ作成の流れ
実行すると、ExcelのマクロがMySQLのデータベースに接続されます。
Excelファイルと、コンボボックスに登録するMySQLのテーブルデータの例
今回は次のExcelファイルを用意しました。
シート上に1つコンボボックスが配置されている状態です。
コンボボックスの右側の「▼」の部分をクリックしても何も値は登録されていません。
このコンボボックスに、MySQLのテーブルデータを登録します。
サンプルで用意したコンボボックスに登録するMySQLのテーブルデータは次の通りです。
今回は赤で囲ったフィールド「name」の値をコンボボックスに登録します。
マクロ実行後は下の通り、フィールド「name」の値がExcelのシート上に設置されたコンボボックスに登録されます。
コードの例
Option Explicit
Sub test()
Dim cn As ADODB.Connection 'Connection用変数
Dim rs As ADODB.Recordset 'レコードセット用変数
Dim sqlStr As String 'SQL用変数
'Connectionインスタンスの生成
Set cn = New ADODB.Connection
'MySQLへの接続情報の取得
cn.ConnectionString = "Driver={MySQL ODBC 8.1 ANSI Driver};" & _
"Server=localhost;" & _
"Port=3306;" & _
"Database=testdb;" & _
"User=root;" & _
"Password=testPass@123456"
'コネクションを開く
cn.Open
'Recordsetオブジェクトのインスタンスを生成する
Set rs = New ADODB.Recordset
'SELECT文を用意する(syainテーブルからデータを全件取得)
sqlStr = "select name from syain"
''SELECT文を実行してRecordsetを開く
rs.Open sqlStr, cn
If rs.EOF = False Then
'レコードセットが最後のレコードでない場合
'コンボボックスをクリアする
Worksheets("top").ComboBox1.Clear
Do Until rs.EOF
'コンボボックスにCSVファイルのデータを追加する
Worksheets("top").ComboBox1.AddItem rs.Fields(0).Value
'Recordsetのレコードのカーソルを次に移動する
rs.MoveNext
Loop
End If
End Sub
注目すべきコード①
最初に見て頂きたいのは10行目です。
'Connectionインスタンスの生成
Set cn = New ADODB.Connection
コードの説明
以上のコードは、マクロがMySQLのデータベースに接続するための接続情報を取得して接続するコードです。
このインスタンスがないとマクロがMySQLのデータベースに接続することができないので必ず生成しておきます。
注目すべきコード②
次に見て頂きたいのは13行目から18行目です。
'MySQLへの接続情報の取得
cn.ConnectionString = "Driver={MySQL ODBC 8.1 ANSI Driver};" & _
"Server=localhost;" & _
"Port=3306;" & _
"Database=testdb;" & _
"User=root;" & _
"Password=testPass@123456"
コードの説明
以上のコードは、マクロがMySQLのデータベースに接続するための接続情報を用意するコードです。
MySQLのデータベースに接続するための接続情報はいくつかの引数が必要になります。
Driver
Driverには、マクロがMySQLのデータベースに接続するのに必要なODBCドライバーの情報を指定します。
Server
Serverには、接続したいMySQLのサーバの名称を指定します。
今回はlocalhostに接続します。
【参考：MySQL Workbenchの画面】
Port
Portには、ポートの値を指定します。
【参考：MySQL Workbenchの画面】
Database
Databaseには、接続したいデータベース名を指定します。
【参考：MySQL Workbenchの画面】
User
Userには、MySQLのデータベースに接続するためのユーザ名を指定します。
【参考：MySQL Workbenchの画面】
Password
Passwordには、MySQLのデータベースに接続するためのパスワードを指定します。
注目すべきコード③
次に見て頂きたいのは21行目です。
'コネクションを開く
cn.Open
コードの説明
以上のコードは、ConnectionインスタンスのOpenメソッドを実行して、マクロがMySQLのデータベースに接続する処理のコードです。
Connectionインスタンスには「注目すべきコード②」で説明した通り、マクロがMySQLのデータベースに接続するための接続情報をConnectionStringプロパティに代入しているので、ここではOpenメソッドを実行することでマクロがMySQLのデータベースに接続されます。
注目すべきコード④
次に見て頂きたいのは24行目から30行目です。
'Recordsetオブジェクトのインスタンスを生成する
Set rs = New ADODB.Recordset
'SELECT文を用意する(syainテーブルからデータを全件取得)
sqlStr = "select name from syain"
''SELECT文を実行してRecordsetを開く
rs.Open sqlStr, cn
コードの説明
以上のコードは、MySQLのテーブルデータを取得するSQLのSELECT文を用意して実行する処理のコードです。
今回はフィールド「name」の値をコンボボックスに登録したいので、select nameとしています。
コードの詳細
24行目のコードでは、Recordsetオブジェクトのインスタンスを生成しています。
Recordsetオブジェクトのインスタンスを生成することで、マクロがSQLのSELECT文を実行したり、SELECT文を実行した後にデータを取得することができるようになります。
27行目のコードでSQLのSELECT文を用意し、30行目でSQLのSELECT文を実行します。
SQLのSELECT文が正常に実行されればデータが取得することができ、その取得したデータはRecordsetオブジェクトのインスタンスから取得することができます。
注目すべきコード⑤
次に見て頂きたいのは32行目から49行目です。
If rs.EOF = False Then
'レコードセットが最後のレコードでない場合
'コンボボックスをクリアする
Worksheets("top").ComboBox1.Clear
Do Until rs.EOF
'コンボボックスにCSVファイルのデータを追加する
Worksheets("top").ComboBox1.AddItem rs.Fields(0).Value
'Recordsetのレコードのカーソルを次に移動する
rs.MoveNext
Loop
End If
コードの説明
以上のコードは、取得したMySQLのテーブルデータをコンボボックスに登録する処理のコードです。
今回はフィールド「name」の値を取得しているので、コンボボックスにはフィールド「name」の値がコンボボックスに登録されます。
コードの詳細
32行目のコードでは、レコードセット(rs)が最後のレコードでないかを判定しています。
もし取得先のMySQLのテーブルにデータが存在しない場合(rs.EOFがTRUEの場合)は、レコードセット(rs)が最後のレコードを参照します。
もしデータがEOFの場合はデータが無いことを意味します。(最終行を参照しているので)
レコードセット(rs)が最後のレコードでない場合は、37行目でコンボボックスをクリアします。(取得したデータをコンボボックスに登録するためクリアしておく)
42行目のコードでは、取得したデータをコンボボックスに登録します。
データの取得のSELECT文が「select name from syain」なので、rs.Fields(0).Valueが返す値はフィールド「name」の値になります。
rs.Fields(0).Valueがフィールド「name」の値を返すので、AddItemメソッドの引数にrs.Fields(0).Valueを指定しAddItemメソッドを実行することでコンボボックスにrs.Fields(0).Valueの返す値が登録されます。
45行目のコードでは、recordsetに格納された次のデータを参照するためMoveNextメソッドを実行しています。
例えば1つ目のデータを参照した後に2つ目のデータを参照するにはこのMoveNextを実行しないといけません。
MoveNextを実行しないと永久に1つ目のデータを参照し続けます。
なので、2つ目、3つ目・・・と順々にデータを参照するためには忘れずにMoveNextメソッドを実行します。
動作確認
「Excelファイルと、コンボボックスに登録するMySQLのテーブルデータの例」をご覧ください。
【注意】参照設定が必要です
一つ注意点があるのですが、先ほどのコードを動かすには参照設定が必要です。
参照設定の一覧(下の画像を参考)から次の項目(ライブラリ)にチェックを付けて「OK」ボタンをクリックします。
- Microsoft ActiveX Data Objects 2.8 Library(msado28.tlb)
なぜ必要かというと、Excelのマクロのコードの5行目の「ADODB.Connection」と6行目の「ADODB.Recordset」というオブジェクトが「msado28.tlb」というファイルを参照するからです。
Dim cn As ADODB.Connection 'Connection用変数
Dim rs As ADODB.Recordset 'レコードセット用変数
この参照設定をしないと下の画像のエラーが出ますので必ず行う必要があります。
ここでは「msado28.tlb」とは何者かについては記事の本題から逸れてしまうので詳細は割愛しますが、マクロで「ADODB.Connection」「ADODB.Recordset」というオブジェクトを使う場合は参照設定しないと動かない、程度に思って頂ければと思います。
最後に
この記事では、MySQLのテーブルデータをExcelのシート上に設置されたActiveXコントロールのコンボボックスに登録する方法についてご説明しました。
コンボボックスの値に、MySQLのテーブルデータを使いたい場合はあるかと思います。
今回はシート上に設置されたActiveXコントロールのコンボボックスを使った場合でご説明しましたが、もしMySQLのテーブルデータをコンボボックスで使いたい場合は本記事を参考にしてみてくださいね。
プログラミングのスキルを習得するなら
プログラミングのスキルを習得したい、今のスキルをもっと高めたい、そう考えているなら「プログラミングスクール」がおすすめです。
プログラミングのスキルの基礎を身につけるなら「TechAcademy」で1週間の無料体験があるので、これで「プログラミングの基礎」を学ぶのにおすすめですよ。
