【ExcelVBA】Excelの表の値をMySQLのテーブルに追加するには②

7分

SHARE

この記事では、Excelの表の値をMySQLのテーブルに追加する方法についてご説明します。

すぐにソースコードを見たい場合はこちらをクリック

ちなみに、本記事と似た記事がありますのでそちらもご覧いただけたら幸いです。

【ExcelVBA】Excelの表の値をMySQLのテーブルに追加するには①

以上の記事と今回の記事の内容で何が違うのかというと、本記事ではExcelの表の値をINSERT文を使ってMySQLのテーブルに追加しているのに対し、以上の記事ではMySQLのLOAD DATAステートメントを利用して追加しています。

【動画】Excelの表の値をMySQLのテーブルに追加する実際の動き

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


Excelの表の値を1行ずつ取得してINSERT文でMySQLのテーブルに追加しています。

マクロ作成の流れ

STEP.1
Connectionインスタンスを生成する
Connectionインスタンスを生成します。
STEP.2
MySQLへの接続情報を取得する
MySQLへの接続情報を取得します。
STEP.3
STEP.3の接続情報をOpenメソッドの引数に指定してOpenメソッドを実行する
STEP.3の接続情報をOpenメソッドの引数に指定してOpenメソッドを実行します。
実行すると、ExcelのマクロがMySQLのデータベースに接続されます。
STEP.4
Excelの表の値を1行取得してINSERT文でMySQLのテーブルに追加する
Excelの表の値を1行取得してINSERT文でMySQLのテーブルに追加します。
STEP.5
STEP.4の処理を最後の行まで繰り返す
STEP.4の処理を最後の行まで繰り返します。
目次へ戻る

Excelファイルとインポート先のテーブルの例

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

Excelファイルのシートにはヘッダと10件のデータが存在しています。

また、今回用意したインポート先のテーブルは次の通りです。

このテーブルに、Excelファイルのシートにある10件のデータをインポートします。

試しに、以下のように1件もデータが無い状態を用意しておきました。

マクロを実行すると、先ほどのExcelファイルのシートにある10件のデータがインポートされました。

Excelファイルにある10件のデータがテーブル上に存在していることが分かります。

目次へ戻る

コードの例

Option Explicit

Private Sub bt_exec_Click()

    Dim dataRange   As Range                '出力するデータのセル範囲
    Dim cn          As ADODB.Connection     'Connection用変数
    Dim cnt         As Long                 'カウンタ
    Dim sqlStr      As String               'SQL用変数

    Const LastRow   As Long = 11            'データの最終行
    Const dbName    As String = "testdb"    'Excelの表のデータを追加するテーブルがあるデータベース名
    Const impTbl    As String = "syain"     'Excelのデータを追加するテーブル名
    
    '追加するデータセル範囲を指定
    Set dataRange = Worksheets("work").Range("A1:D" & LastRow)
            
    'Connectionインスタンスの生成
    Set cn = New ADODB.Connection
                              
    'MySQLへの接続情報の取得
    cn.ConnectionString = "Driver={MySQL ODBC 8.1 ANSI Driver};" & _
                          "Server=localhost;" & _
                          "Port=3306;" & _
                          "Database=" & dbName & ";" & _
                          "User=root;" & _
                          "Password=testPass@123456;"
                          
    'コネクションを開く
    cn.Open
    
    For cnt = 2 To LastRow ' A2からD11のセル範囲を対象にする場合
    
        sqlStr = "INSERT INTO " & impTbl
        sqlStr = sqlStr & " ("
        sqlStr = sqlStr & " id, name, address, age"
        sqlStr = sqlStr & " )"
        sqlStr = sqlStr & " VALUES"
        sqlStr = sqlStr & " ("
        sqlStr = sqlStr & "    " & dataRange.Cells(cnt, 1).Value
        sqlStr = sqlStr & " , '" & dataRange.Cells(cnt, 2).Value & "'"
        sqlStr = sqlStr & " , '" & dataRange.Cells(cnt, 3).Value & "'"
        sqlStr = sqlStr & " ,  " & dataRange.Cells(cnt, 4).Value
        sqlStr = sqlStr & " )"
        
        'INSERT文実行する
        cn.Execute sqlStr
        
    Next cnt

    '後処理
    cn.Close
    
    Set cn = Nothing
        
End Sub

注目すべきコード①

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

    '追加するデータセル範囲を指定
    Set dataRange = Worksheets("work").Range("A1:D" & LastRow)

コードの説明

以上のコードは、MySQLのテーブルに追加するExcelの表のセル範囲を指定するコードです。

注目すべきコード②

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

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

コードの説明

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

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

注目すべきコード③

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

    'MySQLへの接続情報の取得
    cn.ConnectionString = "Driver={MySQL ODBC 8.1 ANSI Driver};" & _
                          "Server=localhost;" & _
                          "Port=3306;" & _
                          "Database=" & dbName & ";" & _
                          "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のデータベースに接続するためのパスワードを指定します。

注目すべきコード④

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

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

コードの説明

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

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

注目すべきコード⑤

最初に見て頂きたいのは31行目から48行目です。

    For cnt = 2 To LastRow ' A2からD11のセル範囲を対象にする場合
    
        sqlStr = "INSERT INTO " & impTbl
        sqlStr = sqlStr & " ("
        sqlStr = sqlStr & " id, name, address, age"
        sqlStr = sqlStr & " )"
        sqlStr = sqlStr & " VALUES"
        sqlStr = sqlStr & " ("
        sqlStr = sqlStr & "    " & dataRange.Cells(cnt, 1).Value
        sqlStr = sqlStr & " , '" & dataRange.Cells(cnt, 2).Value & "'"
        sqlStr = sqlStr & " , '" & dataRange.Cells(cnt, 3).Value & "'"
        sqlStr = sqlStr & " ,  " & dataRange.Cells(cnt, 4).Value
        sqlStr = sqlStr & " )"
        
        'INSERT文実行する
        cn.Execute sqlStr
        
    Next cnt

コードの説明

以上のコードは、Excelの表のデータ件数分MySQLのテーブルに追加する処理のコードです。

MySQLのテーブルへの追加はSQLのINSERT文を使い、1レコードずつINSERT文を繰り返します。

INSERT文の実行は、ConnectionインスタンスのExecuteメソッドにINSERT文を引数にして実行することでINSERT文が実行されて、MySQLのテーブルにデータが追加されます。

ちなみに、33行目から43行目のコードだけではINSERT文が分かりにくいと思うので、実際に実行するINSERT文を以下にお見せします。

SELECT文(データベースにあるテーブルをすべて取得する)
INSERT INTO syain(
    id,
    name,
    address,
    age
)
VALUES(
    1,
    '田中 太一',
    '東京都港区',
    32
)

以上は、項番が1のデータをMySQLのテーブルに追加するINSERT文です。

今回のサンプルデータは10件のデータがあるので、10レコード分INSERT文を実行します。

目次へ戻る

動作確認

「Excelファイルとインポート先のテーブルの例」をご覧ください。

目次へ戻る

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

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

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

  1. Microsoft ActiveX Data Objects 2.8 Library(msado28.tlb)

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

    Dim cn          As ADODB.Connection     'Connection用変数

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

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

目次へ戻る

最後に

この記事では、Excelの表の値をMySQLのテーブルに追加する方法についてご説明しました。

SQLのINSERT文を実行することで簡単にExcelの表の値をMySQLのテーブルに追加することができます。

ただし、1レコードごとにSQLのINSERT文を実行するので、大量のデータを追加するには時間がかかってしまいます。

もし大量のデータをMySQLのテーブルに追加したい場合は、MySQLのLOAD DATAステートメントを利用して追加する方が時間を短縮することができるかもしれません。

MySQLのLOAD DATAステートメントを利用して大量のデータを追加する方法は下の記事で説明していますので、そちらも参考にして頂けたら幸いです。

【ExcelVBA】Excelの表の値をMySQLのテーブルに追加するには①

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

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

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

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



目次へ戻る

前の記事

【ExcelVBA】Excelのマクロを使ってMySQLのデー…

次の記事

【SQLite】DB Browser for SQLiteでC…