【ExcelVBA】ExcelのマクロからSQLiteのテーブルにデータを追加するには

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

Table of Contents

【動画】ExcelのマクロからSQLiteのテーブルにデータを追加する実際の動き

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

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

マクロがSQLiteのデータベースに接続したらSQLのINSERT文を実行し、Excelのセルに入力されたデータをSQLiteのテーブルに追加しています。

目次へ戻る

マクロ作成の流れ

STEP.1

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

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

STEP.2

SQLiteへの接続情報を取得する

SQLiteへの接続情報を取得します。

STEP.3

STEP.2の接続情報をOpenメソッドの引数に指定してOpenメソッドを実行する

STEP.2の接続情報をOpenメソッドの引数に指定してOpenメソッドを実行します。
実行すると、ExcelのマクロがSQLiteのデータベースに接続されます。

STEP.4

SQLのINSERT文を実行する

SQLのINSERT文を実行します。
正常に処理が実行されると、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文(サンプルのExcelのシートに入力された値を追加する)

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週間の無料体験があるので、これで「プログラミングの基礎」を学ぶのにおすすめですよ。

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

目次へ戻る