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

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

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

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

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

マクロがMySQLのデータベースに接続したらSQLのUPDATE文を実行し、テーブルデータを更新しています。

目次へ戻る

マクロ作成の流れ

STEP.1

Connectionインスタンスを生成する

Connectionインスタンスを生成します。

STEP.2

MySQLへの接続情報をConnectionStringプロパティに設定する

MySQLへの接続情報をConnectionStringプロパティに設定します。

STEP.3

ConnectionインスタンスのOpenメソッドを実行してMySQLのデータベースに接続する

ConnectionインスタンスのOpenメソッドを実行してMySQLのデータベースに接続します。

STEP.4

SQLのUPDATE文を実行する

SQLのUPDATE文を実行します。
正常にUPDATE文が実行されると、MySQLのテーブルデータが更新されます。

目次へ戻る

更新先のMySQLテーブルデータとExcelファイルの例

今回更新先のMySQLのテーブルデータは下の画像の通りに用意しました。

今回はidが3のデータを更新します。

更新するために、以下のExcelファイルを用意しました。

更新ボタンをクリックすると、Excelのシートの値でMySQLテーブルデータが更新されます。

なお、今回は「名前」(name)、「住所」(address)、「年齢」(age)の3つの項目(フィールド)を更新するUPDATE文を実行します。

また、項番についてはどのデータを更新するのかを条件に使うために更新の対象にはしていません。

実際のUPDATE文をマクロで実装したコードは下の通りです。

    'UPDATE文を用意する(syainテーブルのデータを更新する)
    sqlStr = "UPDATE syain"
    sqlStr = sqlStr & " Set"
    sqlStr = sqlStr & "  name = '" & ws.Range("valName").Value & "'"
    sqlStr = sqlStr & ", address = '" & ws.Range("valAddress").Value & "'"
    sqlStr = sqlStr & ", age = " & CLng(ws.Range("valAge").Value)
    sqlStr = sqlStr & " WHERE id = " & CLng(ws.Range("valID").Value) & ";"

以上のコードだけではUPDATE文が分かりにくいと思うので、実際に実行するUPDATE文をサンプルで以下にお見せします。

UPDATE文(サンプルのExcelのシートに入力された値で更新する)

UPDATE
    syain
Set
    name = '佐藤 浩二',
    address = '埼玉県新座市',
    age = 36
WHERE
    id = 3

以上のUPDATE文が実行されると、下の通りに値が更新されます。

更新前は下の通りです。

idが3のデータが更新されていることが分かると思います。

目次へ戻る

コードの例

Option Explicit

Private Sub btn_exec_Click()

    Dim cn          As ADODB.Connection     'Connection用変数
    Dim sqlStr      As String               'SQL用変数
    Dim ws          As Worksheet
    
    'シートを取得する
    Set ws = Worksheets("work")
        
    '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
        
    'SELECT文を用意する(syainテーブルからデータを全件取得)
    sqlStr = "UPDATE syain"
    sqlStr = sqlStr & " Set"
    sqlStr = sqlStr & "  name = '" & ws.Range("valName").Value & "'"
    sqlStr = sqlStr & ", address = '" & ws.Range("valAddress").Value & "'"
    sqlStr = sqlStr & ", age = " & CLng(ws.Range("valAge").Value)
    sqlStr = sqlStr & " WHERE id = " & CLng(ws.Range("valID").Value) & ";"
    
    'UPDATE文を実行する
    cn.Execute sqlStr
    
    '後処理
    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行目から32行目です。

    'SELECT文を用意する(syainテーブルからデータを全件取得)
    sqlStr = "UPDATE syain"
    sqlStr = sqlStr & " Set"
    sqlStr = sqlStr & "  name = '" & ws.Range("valName").Value & "'"
    sqlStr = sqlStr & ", address = '" & ws.Range("valAddress").Value & "'"
    sqlStr = sqlStr & ", age = " & CLng(ws.Range("valAge").Value)
    sqlStr = sqlStr & " WHERE id = " & CLng(ws.Range("valID").Value) & ";"
    
    'UPDATE文を実行する
    cn.Execute sqlStr

コードの説明

以上のコードは、MySQLのテーブルデータを更新するSQLのUPDATE文を用意して更新を実行するコードです。

コードの詳細

27行目のコードは、UPDATE文の「UPDATE」と更新対象のテーブル名syainを指定しています。

28行目でsetを記述し、29行目では更新する1つ目のフィールドnameと、そのnameの値を更新するExcelのシートで入力された名前を指定します。

30行目では更新する2つ目のフィールドaddressと、そのaddressの値を更新するExcelのシートで入力された住所を指定します。

31行目では更新する3つ目のフィールドageと、そのageの値を更新するExcelのシートで入力された年齢を指定します。

32行目のコードは、idの値がExcelのシートの項番の値が合致するデータに対して以上の更新する条件を記述しているコードです。

以上のUPDATE文が用意出来たら、35行目でConnectionインスタンスのExcuteメソッドの引数にUPDATE文を指定して実行します。

正常にUPDATE文が実行されると、MySQLのテーブルデータが更新されます。

目次へ戻る

動作確認

「更新先のMySQLテーブルデータとExcelファイルの例」をご覧ください。

目次へ戻る

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

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

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

Microsoft ActiveX Data Objects 2.8 Library(msado28.tlb)

なぜ必要かというと、Excelのマクロのコードの5行目の「ADODB.Connection」というオブジェクトが「msado28.tlb」というファイルを参照するからです。

    Dim cn          As ADODB.Connection     'Connection用変数

この参照設定をしないと下の画像のエラーが出ますので必ず行う必要があります。

ここでは「msado28.tlb」とは何者かについては記事の本題から逸れてしまうので詳細は割愛しますが、マクロで「ADODB.Connection」というオブジェクトを使う場合は参照設定しないと動かない、程度に思って頂ければと思います。

目次へ戻る

最後に

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

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

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

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

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

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

目次へ戻る