この記事では、ExcelのマクロからSQLiteのテーブルにデータを追加する方法についてご説明します。
【動画】ExcelのマクロからSQLiteのテーブルにデータを追加する実際の動き
本題に入る前に、まずは次の動画をご覧ください。
Connectionインスタンスの生成してSQLiteのデータベースへの接続情報を用意したらConnectionインスタンスのConnectionStringプロパティに代入し、Openメソッドを実行することで、マクロがSQLiteのデータベースに接続しています。
マクロがSQLiteのデータベースに接続したらSQLのINSERT文を実行し、Excelのセルに入力されたデータをSQLiteのテーブルに追加しています。
マクロ作成の流れ
実行すると、ExcelのマクロがSQLiteのデータベースに接続されます。
正常に処理が実行されると、SQLiteのテーブルにデータが追加されます。
ExcelファイルとSQLiteのテーブルデータの例
今回は次のExcelファイルを用意しました。
また、SQLiteのテーブルデータは次の通りです。
テーブルにデータが10件存在しています。
今回はこのテーブルに、Excelのセルに入力されたデータをマクロが追加します。
追加された結果は下の画面の通りです。
コードの例
Option Explicit Private Sub btn_exec_Click() Dim conn As adodb.Connection 'ADODB.Connectionオブジェクトのインスタンス用変数 Dim sqlStr As String 'SQL文用変数 Dim ws As Worksheet Dim ws As Worksheet 'シートを取得する Set ws = Worksheets("work") 'Connectionオブジェクトのインスタンスを生成する Set conn = New adodb.Connection 'SQLiteへの接続情報を取得する conn.ConnectionString = "DRIVER=SQLite3 ODBC Driver;Database=" & _ ws.Range("dbName").Value 'コネクションを開く conn.Open 'INSERT文を用意する(syainテーブルにデータを追加) sqlStr = "INSERT INTO" sqlStr = sqlStr & " syain" sqlStr = sqlStr & " (id, name, address, age)" sqlStr = sqlStr & " VALUES" sqlStr = sqlStr & " (" sqlStr = sqlStr & CLng(ws.Range("valID").Value) sqlStr = sqlStr & ", '" & ws.Range("valName").Value & "'" sqlStr = sqlStr & ", '" & ws.Range("valAddress").Value & "'" sqlStr = sqlStr & ", " & CLng(ws.Range("valAge").Value) sqlStr = sqlStr & ")" 'INSERT文を実行する conn.Execute sqlStr '後処理 conn.Close End Sub
注目すべきコード①
最初に見て頂きたいのは13行目です。
'Connectionオブジェクトのインスタンスを生成する Set conn = New adodb.Connection
コードの説明
以上のコードは、マクロがSQLiteのデータベースに接続するための接続情報を取得して接続するコードです。
このインスタンスがないとマクロがSQLiteのデータベースに接続することができないので必ず生成しておきます。
注目すべきコード②
次に見て頂きたいのは16行目から17行目です。
'SQLiteへの接続情報を取得する conn.ConnectionString = "DRIVER=SQLite3 ODBC Driver;Database=" & _ ws.Range("dbName").Value
コードの説明
以上のコードは、マクロがSQLiteのデータベースに接続するための接続情報を用意するコードです。
接続情報のDriverには、マクロがSQLiteのデータベースに接続するのに必要なODBCドライバーの情報を指定します。
「SQLite3 ODBC Driver」は下のODBC データソースアドミニストレーター上で表示されているSQLiteのドライバの名前を指定します。
また、SQLiteのデータベースファイルのフルパスも指定します。
今回はB2のセル(今回はセルの名前を「dbName」としています)を接続情報で指定しています。
注目すべきコード③
次に見て頂きたいのは24行目です。
'コネクションを開く conn.Open
コードの説明
以上のコードは、ConnectionインスタンスのOpenメソッドを実行して、マクロがSQLiteのデータベースに接続する処理のコードです。
Connectionインスタンスには「注目すべきコード②」で説明した通り、マクロがSQLiteのデータベースに接続するための接続情報をConnectionStringプロパティに代入しているので、ここではOpenメソッドを実行することでマクロがSQLiteのデータベースに接続されます。
注目すべきコード④
次に見て頂きたいのは23行目から32行目です。
'INSERT文を用意する(syainテーブルにデータを追加) sqlStr = "INSERT INTO" sqlStr = sqlStr & " syain" sqlStr = sqlStr & " (id, name, address, age)" sqlStr = sqlStr & " VALUES" sqlStr = sqlStr & " (" sqlStr = sqlStr & CLng(ws.Range("valID").Value) sqlStr = sqlStr & ", '" & ws.Range("valName").Value & "'" sqlStr = sqlStr & ", '" & ws.Range("valAddress").Value & "'" sqlStr = sqlStr & ", " & CLng(ws.Range("valAge").Value) sqlStr = sqlStr & ")"
コードの説明
以上のコードは、Excelのセルに入力されたデータをSQLiteのテーブルに追加するInsert文を組み立てているコードです。
4つの値(項番(id)、名前(name)、住所(address)、年齢(age))を1レコードとしてsyainテーブルに追加します。
4つの値は28行目から31行目で指定しています。
以上のコードだけではINSERT文が分かりにくいと思うので、実際に実行するINSERT文をサンプルで以下にお見せします。
INSERT INTO syain( id, name, address, age ) VALUES( 11, '吉田 大地', '埼玉県熊谷市', 35 )
注目すべきコード⑤
次に見て頂きたいのは35行目です。
'INSERT文を実行する cn.Execute sqlStr
コードの説明
以上のコードは、INSERT文をマクロがSQLite側で実行するよう要求し実行する処理のコードです。
ConnectionインスタンスのExecuteメソッドにINSERT文を引数に指定し実行することでSQLite側でINSERT文が実行されます。
動作確認
「ExcelファイルとSQLiteのテーブルデータの例」をご覧ください。
【注意】参照設定が必要です
一つ注意点があるのですが、先ほどのコードを動かすには参照設定が必要です。
参照設定の一覧(下の画像を参考)から次の項目(ライブラリ)にチェックを付けて「OK」ボタンをクリックします。
- Microsoft ActiveX Data Objects 2.8 Library(msado28.tlb)
なぜ必要かというと、Excelのマクロのコードの5行目の「ADODB.Connection」というオブジェクトが「msado28.tlb」というファイルを参照するからです。
Dim conn As adodb.Connection 'ADODB.Connectionオブジェクトのインスタンス用変数
この参照設定をしないと下の画像のエラーが出ますので必ず行う必要があります。
ここでは「msado28.tlb」とは何者かについては記事の本題から逸れてしまうので詳細は割愛しますが、マクロで「ADODB.Connection」というオブジェクトを使う場合は参照設定しないと動かない、程度に思って頂ければと思います。
最後に
この記事では、ExcelのマクロからSQLiteのテーブルにデータを追加する方法についてご説明しました。
ExcelのマクロからSQLiteのテーブルにデータを追加したいときは本記事を参考にしてみてくださいね。
プログラミングのスキルを習得するなら
プログラミングのスキルを習得したい、今のスキルをもっと高めたい、そう考えているなら「プログラミングスクール」がおすすめです。
プログラミングのスキルの基礎を身につけるなら「TechAcademy」で1週間の無料体験があるので、これで「プログラミングの基礎」を学ぶのにおすすめですよ。