MENU
Life and Digital

Excel VBA × MySQLでテーブル作成・レコード追加までを徹底解説！

広告
Excel-VBA データベース 解説-動画
  1. ホーム
  2. 解説-ブログ
  3. Excel-VBA
  4. Excel VBA × MySQLでテーブル作成・レコード追加までを徹底解説！

Excel VBA × MySQLでテーブル作成・レコード追加までを徹底解説！

目次

MySQL Community Editionとは？

MySQL Community Editionは、世界中で最も利用されているオープンソースのリレーショナルデータベースです。
無償でダウンロードでき、GPLライセンスのもと自由に使用できます。
巨大な開発コミュニティのサポートも魅力の一つです。

公式サイトはこちら：
https://www.mysql.com/jp/products/community/

Excel VBAからMySQLを使うために必要な準備

Excel VBAからMySQLに接続するには、以下が必要です。

  • MySQL Serverのインストール
  • MySQL Connector/ODBCのインストール
  • Excel VBAで「ActiveX Data Objects（ADO）」ライブラリを有効化

これにより、Excelから直接MySQLにアクセスできるようになります。

MySQLデータベースを作成する

まず、テスト用にデータベース「eng001」を作成します。
コマンドプロンプトでMySQLにログインし、以下のSQLを実行します。

CREATE DATABASE eng001
  DEFAULT CHARACTER SET utf8
  COLLATE utf8_general_ci;

補足で、以下の操作もよく使います。

  • データベース一覧表示： SHOW DATABASES;
  • データベース削除： DROP DATABASE eng001;
  • データベース切り替え： USE eng001;

MySQLユーザー作成と権限付与

新しいユーザーを作成し、特定のデータベースへの権限を設定します。

CREATE USER 'db_user'@'%' IDENTIFIED BY 'db_user';
GRANT ALL ON eng001.* TO 'db_user';
SELECT user, host FROM mysql.user;

作成したdb_userで再ログインして操作を続行することも可能です。

Excel VBAからMySQLに接続する方法

Excel VBAでMySQLに接続するには、まずADOライブラリを設定します。

  1. VBAエディタを開き（Alt + F11）
  2. [ツール] → [参照設定] から「Microsoft ActiveX Data Objects x.x Library」にチェックを入れます。（通常は最新の6.1）

次に、接続文字列を作成します。

Function mysqlDsn() As String
    mysqlDsn = "Driver={MySQL ODBC 8.0 Unicode Driver};" & _
               "Server=localhost;" & _
               "Database=eng001;" & _
               "User=db_user;" & _
               "Password=db_user;" & _
               "Option=3;"
End Function

VBAでテーブルを作成する

MySQLにテーブル「user_master」を作成するVBAコード例です。

Sub ado_CreateTable()
    Dim ado As Object
    Dim strDsn As String
    Dim strSql As String

    strDsn = mysqlDsn()
    Set ado = CreateObject("ADODB.Connection")
    ado.Open mysqlDsn

    strSql = "CREATE TABLE user_master (" & _
             "user_id VARCHAR(8) NOT NULL PRIMARY KEY," & _
             "user_name VARCHAR(128)," & _
             "password VARCHAR(128)," & _
             "email VARCHAR(128)," & _
             "authority TINYINT" & _
             ");"

    On Error GoTo ErrorHandler
    ado.Execute strSql
    MsgBox "テーブル 'user_master' が作成されました。"
    ado.Close
    Set ado = Nothing
    Exit Sub

ErrorHandler:
    MsgBox "エラー: " & Err.Description
    If Not ado Is Nothing Then
        If ado.State = 1 Then ado.Close
        Set ado = Nothing
    End If
End Sub

VBAでレコードを追加する

次に、作成したテーブルにレコードを挿入する例です。

Sub ado_Insert()
    Dim ado As Object
    Dim strDsn As String
    Dim strSql As String

    strDsn = mysqlDsn()
    Set ado = CreateObject("ADODB.Connection")
    ado.Open strDsn

    strSql = "INSERT INTO user_master (user_id, user_name, password, email, authority) " & _
             "VALUES ('u001', '山田太郎', 'pass1234', 'yamada@example.com', 1);"
    ado.Execute strSql

    strSql = "INSERT INTO user_master (user_id, user_name, password, email, authority) " & _
             "VALUES ('u002', '田中一郎', 'tanaka111', 'tanaka@example.com', 1);"
    ado.Execute strSql

    MsgBox "データ登録完了"
    ado.Close
    Set ado = Nothing
End Sub

VBAでレコードを検索する

登録されたレコードを検索・取得する方法です。

Sub SelectUserMaster()
    Dim ado As Object
    Dim res As Object
    Dim strDsn As String
    Dim strSql As String

    strDsn = mysqlDsn()
    strSql = "SELECT * FROM user_master;"

    Set ado = CreateObject("ADODB.Connection")
    ado.Open strDsn
    Set res = ado.Execute(strSql)

    Do Until res.EOF
        Debug.Print res.Fields(0).Value, res.Fields(1).Value, res.Fields(2).Value, res.Fields(3).Value, res.Fields(4).Value
        res.MoveNext
    Loop

    res.Close
    Set res = Nothing
    ado.Close
    Set ado = Nothing
End Sub

サンプルコード

接続文字列（関数）

Function mysqlDsn() As String
  Dim strDsn As String
  strDsn = ""
  strDsn = strDsn + "Driver={MySQL ODBC 8.0 Unicode Driver};"
  strDsn = strDsn + "Server=localhost;" ' サーバーアドレス
  strDsn = strDsn + "Database=eng001;" ' テーブル名
  strDsn = strDsn + "User=db_user;"     ' ユーザー名
  strDsn = strDsn + "Password=db_user;" ' ユーザーバスワード
  strDsn = strDsn + "Option=3;"
    
  mysqlDsn = strDsn
 
End Function

テーブル一覧の取得

Sub GetTableList()
    Dim cn As Object  ' ADO Connection
    Dim rs As Object  ' ADO Recordset
    Dim strDsn As String
    Dim sql As String

    On Error GoTo ErrHandler

    ' 接続文字列を取得
    strDsn = mysqlDsn()

    ' ADO接続作成
    Set cn = CreateObject("ADODB.Connection")
    cn.Open strDsn

    ' テーブル一覧を取得するSQL
    sql = "SHOW TABLES;"

    ' クエリ実行
    Set rs = cn.Execute(sql)

    ' テーブル名をDebug出力
    Do Until rs.EOF
        Debug.Print "テーブル一覧"
        Debug.Print rs.Fields(0).Value
        rs.MoveNext
    Loop

    ' 正常終了
    GoTo Cleanup

ErrHandler:
    MsgBox "エラーが発生しました: " & Err.Description, vbExclamation, "エラー"

Cleanup:
    ' 後始末
    On Error Resume Next
    If Not rs Is Nothing Then
        If rs.State = 1 Then rs.Close
        Set rs = Nothing
    End If
    If Not cn Is Nothing Then
        If cn.State = 1 Then cn.Close
        Set cn = Nothing
    End If
End Sub

テーブル作成

Sub ado_CreateTable()
  Dim ado As Object ' ADO Connectionオブジェクト
  Dim res As Object ' ADO Recordsetオブジェクト
  Dim strDsn, strSql As String
    
  ' MySQLへの接続文字列
  strDsn = mysqlDsn()
  
  ' ADO Connectionの作成
  Set ado = CreateObject("ADODB.Connection")
      ado.Open strDsn
    
  ' テーブル作成のSQL文
  strSql = ""
  strSql = strSql + "CREATE TABLE user_master ("
  strSql = strSql + " id CHAR(16) not null primary key,"
  strSql = strSql + " name TEXT, "
  strSql = strSql + " pass TEXT, "
  strSql = strSql + " mail TEXT, "
  strSql = strSql + " rights INT "
  strSql = strSql + "); "

  ' SQL文を実行
  On Error GoTo ErrorHandler
  ado.Execute strSql
  
  ' メッセージを表示
  MsgBox "テーブル 'user_master' が作成されました。"
  
  ' 終了処理
  ado.Close: Set ado = Nothing
  Exit Sub
  
ErrorHandler:
    MsgBox "エラー: " & Err.Description
    If Not res Is Nothing Then
        If res.State = 1 Then res.Close
        Set res = Nothing
    End If
    If Not ado Is Nothing Then
        If ado.State = 1 Then ado.Close
        Set ado = Nothing
    End If
End Sub

Sub ado_Insert()
    Dim ado As Object ' ADO Connectionオブジェクト
    Dim res As Object ' ADO Recordsetオブジェクト
    Dim strDsn, strSql As String
    
    ' MySQLへの接続文字列
    strDsn = mysqlDsn()
   
    ' ADO Connectionの作成
    Set ado = CreateObject("ADODB.Connection")
        ado.Open strDsn
    
    ' SQL文を実行
    On Error GoTo ErrorHandler

    strSql = "INSERT INTO user_master (id, name, pass, mail,rights ) "
    strSql = strSql + "VALUES ('user000','三田　さやか','pas000','user00@gmail.com',0 ); "
    ado.Execute strSql

    strSql = "INSERT INTO user_master (id, name, pass, mail,rights )"
    strSql = strSql + " VALUES ('user001','須田　翼','pas001','mail01@gmail.com',1 ); "
    ado.Execute strSql

    strSql = "INSERT INTO user_master (id, name, pass, mail,rights )"
    strSql = strSql + " VALUES ('user002','前田　直行','pas002','mail02@gmail.com',1 );"
    ado.Execute strSql

    strSql = "INSERT INTO user_master (id, name, pass, mail,rights )"
    strSql = strSql + " VALUES ('user003','須田　翼','pas003','mail03@gmail.com',1 );"
    ado.Execute strSql

    strSql = "INSERT INTO user_master (id, name, pass, mail,rights )"
    strSql = strSql + "VALUES ('user004','林　香奈子','pas004','mail04@gmail.com',1 );"
    ado.Execute strSql

    strSql = "INSERT INTO user_master (id, name, pass, mail,rights )"
    strSql = strSql + "VALUES ('user005','中川　修子','pas005','mail05@gmail.com',1 );"
    ado.Execute strSql

    ' 終了処理
    ado.Close: Set ado = Nothing
    MsgBox "データ登録完了"
    Exit Sub

ErrorHandler:
    MsgBox "エラー: " & Err.Description
    If Not res Is Nothing Then
        If res.State = 1 Then res.Close
        Set res = Nothing
    End If
    If Not ado Is Nothing Then
        If ado.State = 1 Then ado.Close
        Set ado = Nothing
    End If
End Sub

レコード追加

Sub ado_Insert()
    Dim ado As Object ' ADO Connectionオブジェクト
    Dim res As Object ' ADO Recordsetオブジェクト
    Dim strDsn, strSql As String
    
    ' MySQLへの接続文字列
    strDsn = mysqlDsn()
   
    ' ADO Connectionの作成
    Set ado = CreateObject("ADODB.Connection")
        ado.Open strDsn
    
    ' SQL文を実行
    On Error GoTo ErrorHandler

    strSql = "INSERT INTO user_master (id, name, pass, mail,rights ) "
    strSql = strSql + "VALUES ('user000','三田　さやか','pas000','user00@gmail.com',0 ); "
    ado.Execute strSql

    strSql = "INSERT INTO user_master (id, name, pass, mail,rights )"
    strSql = strSql + " VALUES ('user001','須田　翼','pas001','mail01@gmail.com',1 ); "
    ado.Execute strSql

    strSql = "INSERT INTO user_master (id, name, pass, mail,rights )"
    strSql = strSql + " VALUES ('user002','前田　直行','pas002','mail02@gmail.com',1 );"
    ado.Execute strSql

    strSql = "INSERT INTO user_master (id, name, pass, mail,rights )"
    strSql = strSql + " VALUES ('user003','須田　翼','pas003','mail03@gmail.com',1 );"
    ado.Execute strSql

    strSql = "INSERT INTO user_master (id, name, pass, mail,rights )"
    strSql = strSql + "VALUES ('user004','林　香奈子','pas004','mail04@gmail.com',1 );"
    ado.Execute strSql

    strSql = "INSERT INTO user_master (id, name, pass, mail,rights )"
    strSql = strSql + "VALUES ('user005','中川　修子','pas005','mail05@gmail.com',1 );"
    ado.Execute strSql

    ' 終了処理
    ado.Close: Set ado = Nothing
    MsgBox "データ登録完了"
    Exit Sub

ErrorHandler:
    MsgBox "エラー: " & Err.Description
    If Not res Is Nothing Then
        If res.State = 1 Then res.Close
        Set res = Nothing
    End If
    If Not ado Is Nothing Then
        If ado.State = 1 Then ado.Close
        Set ado = Nothing
    End If
End Sub

データの検索

Sub ado_Select_Item()
    Dim ado As Object ' ADO Connectionオブジェクト
    Dim res As Object   ' ADO Recordsetオブジェクト
    Dim strDsn, strSql As String
    Dim ws As Worksheet
    
    Set ws = Worksheets("sheet3") ' 出力するシート
    
    Call ClearSheet(ws) ' シートをクリアする
    
    strDsn = mysqlDsn() ' 接続文字列の取得
  
    ' 実行するSQL文の作成
    strSql = "SELECT * FROM user_master WHERE id = 'user002' ;"
    
    ' ADO オブジェクトの作成
    Set ado = CreateObject("ADODB.Connection")
    ado.Open strDsn 'データベースへの接続
    
    ' Recordsetの作成
    Set res = CreateObject("ADODB.Recordset")
        
    ' クエリを実行してデータを取得
    On Error GoTo ErrorHandler
    res.Open strSql, ado, adOpenKeyset, adLockOptimistic
    
    ' データをExcelシートに出力
    Dim i As Integer
    Dim row As Integer
    
    ' ヘッダーを書き込む
    For i = 0 To res.Fields.Count - 1
        ws.Cells(1, i + 1).Value = res.Fields(i).Name
    Next i
    
    ' データをExcelシートに出力
    row = 2
    While Not res.EOF
        For i = 0 To res.Fields.Count - 1
            ws.Cells(row, i + 1).Value = res.Fields(i).Value
        Next i
        row = row + 1
        res.MoveNext
    Wend
    
    ' 終了処理
    res.Close: Set res = Nothing
    ado.Close: Set ado = Nothing
    Call AutoFitWidthAndHeight(ws)
    Set ws = Nothing
    MsgBox "データ取得完了！"
    Exit Sub

ErrorHandler:
    MsgBox "エラー: " & Err.Description
    If Not res Is Nothing Then
        If res.State = 1 Then res.Close
        Set res = Nothing
    End If
    If Not ado Is Nothing Then
        If ado.State = 1 Then ado.Close
        Set ado = Nothing
    End If
End Sub

まとめ

本記事では、Excel VBAからMySQLを使い、
「データベース作成 → ユーザー作成 → テーブル作成 → レコード追加 → 検索」
までの基本操作を一通り解説しました。

データベース操作がVBAからできるようになると、
日々の業務自動化や大規模データ管理が一気に効率化できます！

ぜひこの記事を参考に、VBA × MySQLの活用にチャレンジしてみてください。

興味を持った方は、いいねやコメントをお願いします！
ご質問があればこちらまでお気軽にどうぞ。

📧 lifeanddigital.net@gmail.com
🐦 https://x.com/LifeAndDigital1

Excel-VBA データベース 解説-動画
よかったらシェアしてね！

関連記事

目次