この記事では、ExcelのマクロからSQLiteのテーブルデータを更新する方法についてご説明します。
【動画】ExcelのマクロからSQLiteのテーブルデータを更新する実際の動き
本題に入る前に、まずは次の動画をご覧ください。
Connectionインスタンスの生成してSQLiteのデータベースへの接続情報を用意したらConnectionインスタンスのConnectionStringプロパティに代入し、Openメソッドを実行することで、マクロがSQLiteのデータベースに接続しています。
マクロがSQLiteのデータベースに接続したらSQLのUPDATE文を実行し、テーブルデータを更新しています。
マクロ作成の流れ
正常にUPDATE文が実行されると、SQLiteのテーブルデータが更新されます。
更新先のSQLiteテーブルデータとExcelファイルの例
今回更新先のSQLiteのテーブルデータは下の画像の通りに用意しました。
今回はidが9のデータを更新します。
更新するために、以下のExcelファイルを用意しました。
B2の黄色のセルには、マクロが読み込むSQLiteのデータベースファイルのフルパスを記述しています。
更新ボタンをクリックすると、Excelのシートの値でSQLiteテーブルデータが更新されます。
なお、今回は「名前」(name)、「住所」(address)、「年齢」(age)の3つの項目(フィールド)を更新するUPDATE文を実行します。
また、項番についてはどのデータを更新するのかを条件に使うために更新の対象にはしていません。
実際のUPDATE文をマクロで実装したコードは下の通りです。
'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文が分かりにくいと思うので、実際に実行するUPDATE文をサンプルで以下にお見せします。
UPDATE文(サンプルのExcelのシートに入力された値で更新する)
UPDATE
syain
Set
name = '中山 武',
address = '山梨県甲斐市',
age = 33
WHERE
id = 9;
以上のUPDATE文が実行されると、下の通りに値が更新されます。
更新前は下の通りです。
idが9のデータが更新されていることが分かると思います。
コードの例
Option Explicit
Private Sub btn_exec_Click()
Dim conn As adodb.Connection 'ADODB.Connectionオブジェクトのインスタンス用変数
Dim sqlStr As String 'SQL文用変数
Dim ws As Worksheet
'シートを取得する
Set ws = Worksheets("top")
'Connectionオブジェクトのインスタンスを生成する
Set conn = New adodb.Connection
'SQLiteへの接続情報を取得する
conn.connectionString = "DRIVER=SQLite3 ODBC Driver;Database=" & _
Worksheets("top").Range("dbName").Value
'SQLiteに接続する
conn.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文を実行する
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=" & _
Worksheets("top").Range("dbName").Value
コードの説明
以上のコードは、マクロがSQLiteのデータベースに接続するための接続情報を用意するコードです。
接続情報のDriverには、マクロがSQLiteのデータベースに接続するのに必要なODBCドライバーの情報を指定します。
「SQLite3 ODBC Driver」は下のODBC データソースアドミニストレーター上で表示されているSQLiteのドライバの名前を指定します。
また、SQLiteのデータベースファイルのフルパスも指定します。
今回はB2のセル(今回はセルの名前を「dbName」としています)を接続情報で指定しています。
注目すべきコード③
次に見て頂きたいのは20行目です。
'コネクションを開く
cn.Open
コードの説明
以上のコードは、ConnectionインスタンスのOpenメソッドを実行して、マクロがSQLiteのデータベースに接続する処理のコードです。
Connectionインスタンスには「注目すべきコード②」で説明した通り、マクロがSQLiteのデータベースに接続するための接続情報をConnectionStringプロパティに代入しているので、ここではOpenメソッドを実行することでマクロがSQLiteのデータベースに接続されます。
注目すべきコード④
次に見て頂きたいのは23行目から31行目です。
'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文を実行する
conn.Execute sqlStr
コードの説明
以上のコードは、SQLiteのテーブルデータを更新するSQLのUPDATE文を用意して更新を実行するコードです。
コードの詳細
23行目のコードは、UPDATE文の「UPDATE」と更新対象のテーブル名syainを指定しています。
24行目でsetを記述し、25行目では更新する1つ目のフィールドnameと、そのnameの値を更新するExcelのシートで入力された名前を指定します。
26行目では更新する2つ目のフィールドaddressと、そのaddressの値を更新するExcelのシートで入力された住所を指定します。
27行目では更新する3つ目のフィールドageと、そのageの値を更新するExcelのシートで入力された年齢を指定します。
28行目のコードは、idの値がExcelのシートの項番の値が合致するデータに対して以上の更新する条件を記述しているコードです。
以上のUPDATE文が用意出来たら、31行目でConnectionインスタンスのExcuteメソッドの引数にUPDATE文を指定して実行します。
正常にUPDATE文が実行されると、SQLiteのテーブルデータが更新されます。
動作確認
「更新先のSQLiteテーブルデータとExcelファイルの例」をご覧ください。
【注意】参照設定が必要です
一つ注意点があるのですが、先ほどのコードを動かすには参照設定が必要です。
参照設定の一覧(下の画像を参考)から次の項目(ライブラリ)にチェックを付けて「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週間の無料体験があるので、これで「プログラミングの基礎」を学ぶのにおすすめですよ。
