この記事では、Excelのマクロを使ってMySQLのデータベースからテーブルを検索する方法についてご説明します。
【動画】Excelのマクロを使ってMySQLのデータベースからテーブルを検索する実際の動き
本題に入る前に、まずは次の動画をご覧ください。
Connectionインスタンスの生成してMySQLのデータベースへの接続情報を用意したらConnectionインスタンスのConnectionStringプロパティに代入し、Openメソッドを実行することで、マクロがMySQLのデータベースに接続しています。
マクロがMySQLのデータベースに接続したら「INFORMATION_SCHEMA」の「TABLES」のテーブルに対して、検索したいテーブル名(「B2」のセルに入力されたテーブル名)をテーブルを検索します。
マクロ作成の流れ
実行すると、ExcelのマクロがMySQLのデータベースに接続されます。
ExcelファイルとMySQLのデータベースの例
今回は次のExcelファイルを用意しました。
B2のセルに、検索したいテーブル名を入力して検索ボタンを押すと、もしテーブルが存在している場合はD列のピンク色のセルにデータベース名が出力されます。
以上は、B2のセルに入力された「city」というテーブルが「sakila」「testdb」「world」の3つのデータベースに存在している、ということを意味しています。
もし検索したテーブルが存在しない場合は下のように「テーブルは存在しません」とメッセージが表示されます。
コードの例
Option Explicit
Private Sub btn_exec_Click()
Dim ws As Worksheet 'ワークシート変数
Dim cnt As Long 'カウンタ
Dim cn As ADODB.Connection 'Connection用変数
Dim rs As ADODB.Recordset 'レコードセット用変数
Dim sqlStr As String 'SQL用変数
'シートを取得する
Set ws = Worksheets("work")
'カウンタを初期化する
cnt = 2
'セルをクリアする
ws.Range("D2:D7").ClearContents
'Connectionインスタンスの生成
Set cn = New ADODB.Connection
'MySQLへの接続情報の取得
cn.ConnectionString = "Driver={MySQL ODBC 8.1 ANSI Driver};" & _
"Server=localhost;" & _
"Port=3306;" & _
"User=root;" & _
"Password=testPass@123456;"
'コネクションを開く
cn.Open
'Recordsetオブジェクトのインスタンスを生成する
Set rs = New ADODB.Recordset
'SQLクエリを実行して、データベース名を取得する
sqlStr = "SELECT table_schema"
sqlStr = sqlStr & " FROM information_schema.tables"
sqlStr = sqlStr & " WHERE table_name = '" & Worksheets("work").Range("fTable").Value & "'"
'SELECT文を実行してRecordsetを開く
rs.Open sqlStr, cn, adOpenStatic
If rs.EOF Then
'テーブルが存在しない場合
MsgBox "テーブルは存在しません"
Else
'テーブルが存在する場合
Do Until rs.EOF
'データベース名をセルに出力する
Worksheets("work").Range("D" & cnt).Value = rs.Fields(0).Value
'Recordsetのレコードのカーソルを次に移動する
rs.MoveNext
cnt = cnt + 1
DoEvents
Loop
End If
'後処理
cn.Close
Set cn = Nothing
Set rs = Nothing
End Sub
注目すべきコード①
最初に見て頂きたいのは21行目です。
'Connectionインスタンスの生成
Set cn = New ADODB.Connection
コードの説明
以上のコードは、マクロがMySQLのデータベースに接続するための接続情報を取得して接続するコードです。
このインスタンスがないとマクロがMySQLのデータベースに接続することができないので必ず生成しておきます。
注目すべきコード②
次に見て頂きたいのは24行目から28行目です。
'MySQLへの接続情報の取得
cn.ConnectionString = "Driver={MySQL ODBC 8.1 ANSI Driver};" & _
"Server=localhost;" & _
"Port=3306;" & _
"User=root;" & _
"Password=testPass@123456"
コードの説明
以上のコードは、マクロがMySQLのデータベースに接続するための接続情報を用意するコードです。
MySQLのデータベースに接続するための接続情報はいくつかの引数が必要になります。
Driver
Driverには、マクロがMySQLのデータベースに接続するのに必要なODBCドライバーの情報を指定します。
Server
Serverには、接続したいMySQLのサーバの名称を指定します。
今回はlocalhostに接続します。
【参考：MySQL Workbenchの画面】
Port
Portには、ポートの値を指定します。
【参考：MySQL Workbenchの画面】
User
Userには、MySQLのデータベースに接続するためのユーザ名を指定します。
【参考：MySQL Workbenchの画面】
Password
Passwordには、MySQLのデータベースに接続するためのパスワードを指定します。
注目すべきコード③
次に見て頂きたいのは31行目です。
'コネクションを開く
cn.Open
コードの説明
以上のコードは、ConnectionインスタンスのOpenメソッドを実行して、マクロがMySQLのデータベースに接続する処理のコードです。
Connectionインスタンスには「注目すべきコード②」で説明した通り、マクロがMySQLのデータベースに接続するための接続情報をConnectionStringプロパティに代入しているので、ここではOpenメソッドを実行することでマクロがMySQLのデータベースに接続されます。
注目すべきコード④
次に見て頂きたいのは34行目から42行目です。
'Recordsetオブジェクトのインスタンスを生成する
Set rs = New ADODB.Recordset
'SQLクエリを実行して、データベース名を取得する
sqlStr = "SELECT table_schema"
sqlStr = sqlStr & " FROM information_schema.tables"
sqlStr = sqlStr & " WHERE table_name = '" & Worksheets("work").Range("fTable").Value & "'"
'SELECT文を実行してRecordsetを開く
rs.Open sqlStr, cn, adOpenStatic
コードの説明
以上のコードは、Recordsetオブジェクトのインスタンスを生成した後にデータベース名を取得するSELECT文を用意してそのSELECT文を実行する処理のコードです。
コードの詳細
34行目のコードでは、Recordsetオブジェクトのインスタンスを生成しています。
Recordsetオブジェクトのインスタンスを生成することで、マクロがSQLのSELECT文を実行したり、SELECT文を実行した後にデータを取得することができるようになります。
37行目では、MySQLのデータベースからテーブルを検索するSELECT文を用意しています。
MySQLのデータベースからテーブルを検索するには、「INFORMATION_SCHEMA」の「TABLES」のテーブルから検索します。
検索するテーブル名は、「TABLES」のテーブルにあるフィールド「TABLE_NAME」にあるので、WHERE句にしているフィールド名にtable_nameを、条件値に「Worksheets(“work”).Range(“fTable”).Value」(B2のセルの値)を指定しています。
データベース名はフィールド名「TABLE_SCHEMA」にあるので、SELECT table_schemaとしています。
「INFORMATION_SCHEMA」の「TABLES」のテーブルの中身は下の通りです。
(※分かりやすく見せるため「SELECT *」(全てのフィールドを表示)しています)
B2のセルに入力された「city」というテーブルが「sakila」「testdb」「world」の3つのデータベースに存在していることが分かります。
今回のマクロでは、「city」というテーブルがどのデータベースにあるのかを特定するため「SELECT table_schema」としています。
ちなみに、37行目から39行目のコードだけではSELECT文が分かりにくいと思うので、実際に実行するSELECT文を以下にお見せします。
SELECT
table_schema
FROM
information_schema.tables
WHERE
table_name = 'city'
42行目のコードでは、SELECT文をOpenメソッドの引数に指定して実行することで、SELECT文が実行されます。
注目すべきコード⑤
次に見て頂きたいのは44行目から50行目です。
If rs.EOF Then
'テーブルが存在しない場合
MsgBox "テーブルは存在しません"
Else
'テーブルが存在する場合
以上のコードでは、recordsetに格納されているデータが存在しているかを判定している処理のコードです。
もしデータが無い場合は48行目でテーブルが存在していない旨のメッセージを出力します。
データがある場合は50行目のELSEに該当し、ELSE内で「注目すべきコード⑥」の処理を行います。
注目すべきコード⑥
次に見て頂きたいのは54行目から66行目です。
Do Until rs.EOF
'データベース名をセルに出力する
Worksheets("work").Range("D" & cnt).Value = rs.Fields(0).Value
'Recordsetのレコードのカーソルを次に移動する
rs.MoveNext
cnt = cnt + 1
DoEvents
Loop
コードの説明
以上のコードは、「INFORMATION_SCHEMA」の「TABLES」のテーブルから取得したデータベース名をExcelのセルに出力する処理のコードです。
コードの詳細
54行目のコードでは、recordsetに格納されたデータ(今回はテーブル名)の数分ループ処理を行うDo文で、例えばデータ件数が3件ある場合はループを3回繰り返します。
57行目のコードでは、recordsetに格納されたテーブルの名称をD列のセルに出力します。
60行目のコードでは、recordsetに格納された次のデータを参照するためMoveNextメソッドを実行しています。
例えば1つ目のデータを参照した後に2つ目のデータを参照するにはこのMoveNextを実行しないといけません。
MoveNextを実行しないと永久に1つ目のデータを参照し続けます。
なので、2つ目、3つ目・・・と順々にデータを参照するためには忘れずにMoveNextメソッドを実行します。
動作確認
「ExcelファイルとMySQLのデータベースの例」をご覧ください。
【注意】参照設定が必要です
一つ注意点があるのですが、先ほどのコードを動かすには参照設定が必要です。
参照設定の一覧(下の画像を参考)から次の項目(ライブラリ)にチェックを付けて「OK」ボタンをクリックします。
- Microsoft ActiveX Data Objects 2.8 Library(msado28.tlb)
なぜ必要かというと、Excelのマクロのコードの7行目の「ADODB.Connection」と8行目の「ADODB.Recordset」というオブジェクトが「msado28.tlb」というファイルを参照するからです。
Dim cn As ADODB.Connection 'Connection用変数
Dim rs As ADODB.Recordset 'レコードセット用変数
この参照設定をしないと下の画像のエラーが出ますので必ず行う必要があります。
ここでは「msado28.tlb」とは何者かについては記事の本題から逸れてしまうので詳細は割愛しますが、マクロで「ADODB.Connection」「ADODB.Recordset」というオブジェクトを使う場合は参照設定しないと動かない、程度に思って頂ければと思います。
最後に
この記事では、Excelのマクロを使ってMySQLのデータベースからテーブルを検索する方法についてご説明しました。
Excelのマクロを使ってMySQLのデータベースからテーブルを検索したいときは本記事を参考にしてみてくださいね。
プログラミングのスキルを習得するなら
プログラミングのスキルを習得したい、今のスキルをもっと高めたい、そう考えているなら「プログラミングスクール」がおすすめです。
プログラミングのスキルの基礎を身につけるなら「TechAcademy」で1週間の無料体験があるので、これで「プログラミングの基礎」を学ぶのにおすすめですよ。
