VBAでMysql操作

投稿日: 2024年7月6日

Excel VBAを使用してMySQLからデータを取得する方法は、ODBC (Open Database Connectivity) ドライバを使用して接続し、SQLクエリを実行してデータを取得する方法が一般的です。

MySQLの設定

1.ODBCドライバのインストール

MySQL ODBCドライバをインストールします。MySQL公式サイトからダウンロードできます。
MySQL :: Download Connector/ODBC

最新版はGeneral Availabilityタブにあります。過去のドライバーはArchivesにあります。

ダウンロードが完了後「mysql-connector-odbc-xxx-winx64」を実行します。実行後セットアップウィザードが表示されたら

規約になります。チェックを入れてNextボタンをクリックします。

セットアップタイプを選択します。Tpypical(標準)を選択してNextボタンをクリックします。

Installボタンをクリックします。

暫くするとセットアップウィザードが終了します。Finishボタンをクリックします。

2.ODBCデータソースの設定

Windowsの検索欄で「odbc」と入力します。「ODBCデータソース(64ビット)」を開き、新しいシステムDSNを作成します。

システムDSNタブをクリックし、次に追加ボタンをクリックします。

MySQL ODBCドライバ(Unicode Driver)を選択し、完了ボタンをクリックします。

Data Source Nameに「MySql」と入力します。ここは好きな名前でOKです。ここでの設定は以上になるのでOKボタンを押します。

MySQLのVBAコード

ここのコードは例でパスワードなどをハードコーティングしています。基本的にVBAはパスワードをコード内にハードコーディングしないように注意します。
このページの最後にハードコーディング対策としてiniファイルを使用して接続情報を管理する例を載せます。

ODBC接続文字列を設定します。

' ODBC接続文字列を設定します
    connStr = "Driver={MySQL ODBC 9.0 Unicode Driver};Server=localhost;Database=purchasing_management;User=root;Password=rootpass;Option=3;"

ODBC接続文字列を設定

  • Driver={MySQL ODBC 9.0 Unicode Driver}
    DriverにはODBCのドライバーを入力します。
  • Server=localhost
    サーバー名またはサーバーアドレスを入力します。
  • Database＝purchasing_management
    データベースを指定します。
  • User=root
    MySqlのUser名を入れます。
  • Password=rootpass
    MysqlのPasswordを入れます。
  • Port=your_port_number
    ※必要な場合。例: Port=3306
  • Option=3: 追加のオプションを指定します。通常はこの設定で問題ありません。

全体のコード

Sub GetDataFromMySQL()
    Dim conn As Object
    Dim rs As Object
    Dim connStr As String
    Dim sql As String
    Dim ws As Worksheet

    ' ODBC接続文字列を設定します
    connStr = "Driver={MySQL ODBC 9.0 Unicode Driver};Server=localhost;Database=purchasing_management;User=root;Password=rootpass;Option=3;"

    ' SQLクエリを設定します
    sql = "SELECT * FROM products"

    ' Excelシートを設定します
    Set ws = ThisWorkbook.Sheets("Sheet1")
    
    ' ADOオブジェクトを作成します
    Set conn = CreateObject("ADODB.Connection")
    Set rs = CreateObject("ADODB.Recordset")

    ' データベースに接続します
    conn.Open connStr

    ' SQLクエリを実行し、結果をレコードセットに取得します
    rs.Open sql, conn

    ' レコードセットの内容をシートに書き込みます
    ws.Range("A1").CopyFromRecordset rs

    ' リソースを解放します
    rs.Close
    conn.Close
    Set rs = Nothing
    Set conn = Nothing

    MsgBox "データが正常に取得されました!", vbInformation
End Sub
コードの解説
    ' SQLクエリを設定します
    sql = "SELECT * FROM products"

sql 変数に実行するSQLクエリを設定します。ここでは、products テーブルの全データを取得するクエリを設定しています。

    ' ADOオブジェクトを作成します
    Set conn = CreateObject("ADODB.Connection")
    Set rs = CreateObject("ADODB.Recordset")

conn 変数に新しいADO接続オブジェクトを作成します。rs 変数に新しいADOレコードセットオブジェクトを作成します。

    ' データベースに接続します
    conn.Open connStr

connStr に基づいてデータベースに接続します。

    ' SQLクエリを実行し、結果をレコードセットに取得します
    rs.Open sql, conn

sql クエリを実行し、その結果を rs レコードセットに取得します。

    ' レコードセットの内容をシートに書き込みます
    ws.Range("A1").CopyFromRecordset rs

rs レコードセットの内容を、Excelシート ws のセル範囲 “A1” から開始して書き込みます。

    ' リソースを解放します
    rs.Close
    conn.Close
    Set rs = Nothing
    Set conn = Nothing

rs レコードセットを閉じ、リソースを解放します。conn 接続を閉じ、リソースを解放します。rs と conn 変数を Nothing に設定して、オブジェクトを解放します。

ハードコーディングを避ける方法

ハードコーディングとは、コード内に固定の値を直接書き込むことを指します。
先ほどのコードはデータベース接続に必要な情報（ドライバ、サーバー、データベース、ユーザー名、パスワード）がコード内に直接書かれており、変更が必要な場合にはコードを編集する必要があります。

以下は、.iniファイルを使用して接続情報を管理する例です。
dbconfig.ini という名前で、以下の内容のファイルを作成します。(メモ帳などで作成できます。)

VBAコードで.iniファイルを読み込む

[Database]
Driver=MySQL ODBC 9.0 Unicode Driver
Server=localhost
Database=purchasing_management
User=root
Password=rootpass
Option=3

iniファイルを使用することで、パスワードを直接VBAコードに書くことを避けられますが、.iniファイル自体が暗号化されていないため、完全なセキュリティ対策とは言えません。
より安全な方法として、以下のような対策を検討することをお勧めします。
・ファイルのアクセス権限を適切に設定する
・暗号化を使用して機密情報を保存
・環境変数を利用する
・安全な外部ストレージ（例えば、セキュアなサーバーやクラウドサービス）を利用する

Private Declare PtrSafe Function GetPrivateProfileString Lib "kernel32" Alias "GetPrivateProfileStringA" ( _
    ByVal lpApplicationName As String, ByVal lpKeyName As String, ByVal lpDefault As String, _
    ByVal lpReturnedString As String, ByVal nSize As Long, ByVal lpFileName As String) As Long


Sub GetDataFromMySQL()
    Dim conn As Object
    Dim rs As Object
    Dim connStr As String
    Dim sql As String
    Dim ws As Worksheet
    Dim iniFile As String
    Dim driver As String
    Dim server As String
    Dim database As String
    Dim user As String
    Dim password As String
    Dim optionStr As String
    
    ' .iniファイルのパスを設定します
    iniFile = ThisWorkbook.Path & "\dbconfig.ini"

    ' .iniファイルから設定を読み取ります
    driver = GetIniValue("Database", "Driver", iniFile)
    server = GetIniValue("Database", "Server", iniFile)
    database = GetIniValue("Database", "Database", iniFile)
    user = GetIniValue("Database", "User", iniFile)
    password = GetIniValue("Database", "Password", iniFile)
    optionStr = GetIniValue("Database", "Option", iniFile)

    ' ODBC接続文字列を設定します
    connStr = "Driver={" & driver & "};Server=" & server & ";Database=" & database & ";User=" & user & ";Password=" & password & ";Option=" & optionStr & ";"

    ' SQLクエリを設定します
    sql = "SELECT * FROM products"

    ' Excelシートを設定します
    Set ws = ThisWorkbook.Sheets("Sheet1")
    
    ' ADOオブジェクトを作成します
    Set conn = CreateObject("ADODB.Connection")
    Set rs = CreateObject("ADODB.Recordset")

    On Error GoTo ErrorHandler

    ' データベースに接続します
    conn.Open connStr

    ' SQLクエリを実行し、結果をレコードセットに取得します
    rs.Open sql, conn

    ' レコードセットの内容をシートに書き込みます
    ws.Range("A1").CopyFromRecordset rs

    ' リソースを解放します
    rs.Close
    conn.Close
    Set rs = Nothing
    Set conn = Nothing

    MsgBox "データが正常に取得されました!", vbInformation
    Exit Sub

ErrorHandler:
    If Not rs Is Nothing Then
        If rs.State = 1 Then rs.Close
        Set rs = Nothing
    End If
    If Not conn Is Nothing Then
        If conn.State = 1 Then conn.Close
        Set conn = Nothing
    End If
    MsgBox "Error: " & Err.Description, vbCritical
End Sub

Function GetIniValue(section As String, key As String, iniFile As String) As String
    Dim buffer As String * 255
    Dim valueLength As Long
    valueLength = GetPrivateProfileString(section, key, "", buffer, 255, iniFile)
    GetIniValue = Left(buffer, valueLength)
End Function

以上でハードコーディングを避ける方法になります。

注意事項
64ビットのMySQL ODBCドライバがインストールされていることを確認してください。
.iniファイルの場所は、VBAコード内の ThisWorkbook.Path & “\dbconfig.ini” に設定しています。dbconfig.ini ファイルがExcelファイルと同じディレクトリにあることを確認してください。
エラーハンドリングを追加して、エラー発生時に適切にリソースを解放するようにしています。

記事