【ExcelVBA】ExcelのマクロからMySQLのテーブルデータを取得するには

この記事では、ExcelのマクロからMySQLのテーブルデータを取得する方法についてご説明します。

【動画】ExcelのマクロからMySQLのテーブルデータを取得する実際の動き

本題に入る前に、まずは次の動画をご覧ください。


Connectionインスタンスの生成してMySQLのデータベースへの接続情報を用意したらConnectionインスタンスのConnectionStringプロパティに代入し、Openメソッドを実行することで、マクロがMySQLのデータベースに接続しています。

マクロがMySQLのデータベースに接続したらSQLのSELECT文を実行し、取得したデータをExcelのシートに出力しています。

マクロ作成の流れ

STEP.1
Connectionインスタンスを生成する
Connectionインスタンスを生成します。
STEP.2
MySQLへの接続情報を取得する
MySQLへの接続情報を取得します。
STEP.3
STEP.2の接続情報をOpenメソッドの引数に指定してOpenメソッドを実行する
STEP.2の接続情報をOpenメソッドの引数に指定してOpenメソッドを実行します。
実行すると、ExcelのマクロがMySQLのデータベースに接続されます。
STEP.4
SQLのSELECT文を実行する
SQLのSELECT文を実行します。
STEP.5
STEP.4で取得したデータをExcelのシートに出力する
STEP.4で取得したデータをExcelのシートに出力します。

Excelファイルの例

今回は次のExcelファイルを用意しました。

以上の表に、MySQLのテーブルデータを出力します。

また、以上の表に出力するMySQLのテーブルデータは下の通りです。

【参考:MySQL Workbench上で表示されたテーブルデータ】

syainというテーブルにデータが存在しており、そのテーブルからSQLのSELECT文でデータを取得してExcelの表に出力します。

マクロを実行すると、下の通りにMySQLのテーブルデータがExcelの表に出力されます。

コードの例

Option Explicit

Sub sample()

    Dim cn          As ADODB.Connection     'Connection用変数
    Dim rs          As ADODB.Recordset      'レコードセット用変数
    Dim sqlStr      As String               'SQL用変数
    
    'シートをクリアする
    Worksheets("work").Range("B3:E25").ClearContents
    
    '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 * from syain"
    
    ''SELECT文を実行してRecordsetを開く
    rs.Open sqlStr, cn
    
    'テーブルデータをシートに出力する
    Worksheets("work").Range("B3").CopyFromRecordset rs
    
    '後処理
    rs.Close
    cn.Close
    
End Sub

注目すべきコード①

最初に見て頂きたいのは13行目です。

    'Connectionインスタンスの生成
    Set cn = New ADODB.Connection

コードの説明

以上のコードは、マクロがMySQLのデータベースに接続するための接続情報を取得して接続するコードです。

このインスタンスがないとマクロがMySQLのデータベースに接続することができないので必ず生成しておきます。

注目すべきコード②

次に見て頂きたいのは16行目から21行目です。

    '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のデータベースに接続するためのパスワードを指定します。

注目すべきコード③

次に見て頂きたいのは24行目です。

    'コネクションを開く
    cn.Open

コードの説明

以上のコードは、ConnectionインスタンスのOpenメソッドを実行して、マクロがMySQLのデータベースに接続する処理のコードです。

Connectionインスタンスには「注目すべきコード②」で説明した通り、マクロがMySQLのデータベースに接続するための接続情報をConnectionStringプロパティに代入しているので、ここではOpenメソッドを実行することでマクロがMySQLのデータベースに接続されます。

注目すべきコード④

次に見て頂きたいのは27行目から33行目です。

    'Recordsetオブジェクトのインスタンスを生成する
    Set rs = New ADODB.Recordset
    
    'SELECT文を用意する(syainテーブルからデータを全件取得)
    sqlStr = "select * from syain"
    
    ''SELECT文を実行してRecordsetを開く
    rs.Open sqlStr, cn

コードの説明

以上のコードは、MySQLのテーブルデータを取得するSQLのSELECT文を用意して実行する処理のコードです。

コードの詳細

27行目のコードでは、Recordsetオブジェクトのインスタンスを生成しています。

Recordsetオブジェクトのインスタンスを生成することで、マクロがSQLのSELECT文を実行したり、SELECT文を実行した後にデータを取得することができるようになります。

30行目のコードでSQLのSELECT文を用意し、33行目でSQLのSELECT文を実行します。

SQLのSELECT文が正常に実行されればデータが取得することができ、その取得したデータはRecordsetオブジェクトのインスタンスから取得することができます。

注目すべきコード⑤

次に見て頂きたいのは36行目です。

    'テーブルデータをシートに出力する
    Worksheets("work").Range("B3").CopyFromRecordset rs

コードの説明

以上のコードは、RecordsetのデータをExcelのシートに出力する処理のコードです。

コードの詳細

取得したMySQLのテーブルデータはrecordsetに格納されており、CopyFromRecordsetプロパティにrSをセットすることでB3のセルからrecordsetに格納されているCSVファイルのデータが出力されます。

動作確認

マクロ実行前

今回は以下のExcelファイルを用意しました。

以上の表に、MySQLのテーブルデータを出力します。

また、以上の表に出力するMySQLのテーブルデータは下の通りです。

【参考:MySQL Workbench上で表示されたテーブルデータ】

syainというテーブルにデータが存在しており、そのテーブルからSQLのSELECT文でデータを取得してExcelの表に出力します。

マクロを実行すると、下の通りにMySQLのテーブルデータがExcelの表に出力されます。

【注意】参照設定が必要です

一つ注意点があるのですが、先ほどのコードを動かすには参照設定が必要です。

参照設定の一覧(下の画像を参考)から次の項目(ライブラリ)にチェックを付けて「OK」ボタンをクリックします。

  1. 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」というオブジェクトを使う場合は参照設定しないと動かない、程度に思って頂ければと思います。

最後に

この記事では、ExcelのマクロからMySQLのテーブルデータを取得する方法についてご説明しました。

ExcelのマクロからMySQLのテーブルデータを取得したいときは本記事を参考にしてみてくださいね。

プログラミングのスキルを習得するなら

プログラミングのスキルを習得したい、今のスキルをもっと高めたい、そう考えているなら「プログラミングスクール」がおすすめです。

プログラミングのスキルの基礎を身につけるなら「TechAcademy」で1週間の無料体験があるので、これで「プログラミングの基礎」を学ぶのにおすすめですよ。

→ TechAcademyの「1週間 無料体験」はこちら