この記事では、ExcelのマクロがSQLiteのデータベースに接続する方法についてご説明します。
作業の流れ
ExcelのマクロがSQLiteのデータベースに接続するにはいくつか準備が必要なため、その準備も含め作業全ての流れを下に記述します。
なお、ExcelのマクロがSQLiteのデータベースに接続するのに必要なドライバーがパソコンにインストールされていれば、STEP.1からSTEP.3の手順は不要です。
【STEP.1】ExcelのマクロがSQLiteのデータベースに接続するためのドライバーをダウンロードする
ExcelのマクロがSQLiteのデータベースに接続するためのドライバーをダウンロードするため、以下のURLにアクセスします。
http://www.ch-werner.de/sqliteodbc/
URLにアクセスしたら「sqliteodbc_w64.exe」のリンクをクリックしてダウンロードします。
ダウンロードが完了したら以下のファイルが保存先に生成されます。
【STEP.2】STEP.1のドライバーファイルをインストールする
先ほどダウンロードしたドライバーファイルを使ってインストールをします。
(1)ドライバーファイルをダブルクリックする
ドライバーファイルをダブルクリックすると、以下の画面が表示されるので「Next」ボタンをクリックします。
(2)ライセンスの承諾確認画面が開くので「I Agree」ボタンをクリックする
ライセンスの承諾確認画面が開くので「I Agree」ボタンをクリックします。
(3)インストール先の確認画面でドライバーのインストール先を確認し問題なければ「Next>」ボタンをクリックする
インストール先の確認画面が開くので、ドライバーのインストール先を確認し問題なければ「Next>」ボタンをクリックします
(4)コンポーネント画面で何もチェックを付けず「Install」ボタンをクリックする
コンポーネント画面が開くと、「SQLite 2 Drivers」のチェックを付けるか求められますが、この項目はSQLite2のドライバーも必要な場合にチェックを付けます。
今回扱うSQLiteのバージョンは(SQLite)3なので特に必要はありません。
どうしてもSQLite2のドライバーも必要な場合だけチェックを付けます。
今回は何もチェックを付けず「Install」ボタンをクリックして先に進みます。
(5)ドライバーのインストールの実行
ドライバーのインストールが実行されます。
(6)ドライバーのインストールの完了
ドライバーのインストールが完了したので「FInish」ボタンをクリックして画面を閉じます。
【STEP.3】STEP.1のドライバーファイルが正常にインストールされたかを確認する
STEP.1のドライバーファイルが正常にインストールされたかを確認します。
確認は、「プログラムのアンインストールまたは変更」の画面上で確認します。
一覧に「SQLite ODBC Driver for Win64(remove only)」が表示されていればOKです。
ちなみに「プログラムのアンインストールまたは変更」の画面を表示させるには、次の通りです
※以下の画面はWindows10での画面です。
(1)適当にフォルダを開いてアドレスバーの一番左の矢印をクリックし、コントロールパネルをクリックする
適当にフォルダを開いてアドレスバーの一番左の矢印をクリックし、コントロールパネルをクリックします。
(2)コントロールパネルの画面で「プログラムのアンインストール」をクリックする
コントロールパネルの画面で「プログラムのアンインストール」をクリックします。
(3)「プログラムのアンインストールまたは変更」の画面が開く
「プログラムのアンインストールまたは変更」の画面が開きました。
【STEP.4】SQLiteのデータベースにあるテーブルに対してマクロが接続する
SQLiteのデータベースにあるテーブルに対してマクロを接続させてみます。
接続させるマクロのサンプルコードは次の通りです。
コードの例
Option Explicit Private Sub btn_exec_Click() Dim conn As ADODB.Connection 'ADODB.Connectionオブジェクトのインスタンス用変数 Dim rs As ADODB.Recordset 'レコードセット用変数 Dim sqlStr As String 'SQL文用変数 'Connectionオブジェクトのインスタンスを生成する Set conn = New ADODB.Connection 'Recordsetオブジェクトのインスタンスを生成する Set rs = New ADODB.Recordset 'SQLiteへの接続情報を取得する conn.connectionString = "DRIVER=SQLite3 ODBC Driver;Database=" & _ Worksheets("top").Range("dbName").Value 'SQLiteに接続する conn.Open 'SELECT文を用意する sqlStr = "SELECT * FROM T_社員" 'クライアントサイドカーソルに変更する rs.CursorLocation = adUseClient 'Recordsetを開く rs.Open sqlStr, conn '取得したデータを貼り付ける Worksheets("top").Cells(8, 1).CopyFromRecordset rs '後処理 Set conn = Nothing Set rs = Nothing End Sub
注目すべきコード①
最初に見て頂きたいのは16行目です。
'Connectionオブジェクトのインスタンスを生成する Set conn = New ADODB.Connection 'Recordsetオブジェクトのインスタンスを生成する Set rs = New ADODB.Recordset
以上のコードはSQLiteへの接続やテーブルのデータを取得するのに必要なConnectionとRecordsetオブジェクトのインスタンスを生成するコードです。
Connectionオブジェクトのインスタンスは、マクロがSQLiteに接続するのに必要です。
Recordsetオブジェクトのインスタンスは、SELECT文を実行したり、取得したデータを扱うなどするのに必要です。
注目すべきコード②
次に見て頂きたいのは16行目から20行目です。
'SQLiteへの接続情報を取得する conn.connectionString = "DRIVER=SQLite3 ODBC Driver;Database=" & _ Worksheets("top").Range("dbName").Value 'SQLiteに接続する conn.Open
以上のコードは、SQLiteへの接続情報を取得してマクロがSQLiteに接続する処理のコードです。
16行目のコードでは、シート「top」のdbNameという名前のセルに入力されたSQLiteのデータベースファイルを取得し、SQLiteのドライバー介して20行目でSQLiteにマクロが接続します。
ちなみに今回参照するSQLiteのデータベースのファイルは以下に格納されたものを使用しています。
なお、16行目のコードにある「SQLite3 ODBC Driver」は、「【STEP.2】STEP.1のドライバーファイルをインストールする」の手順でインストールしたドライバー「SQLite ODBC Driver for Win64(remove only)」のことを指します。
注目すべきコード③
次に見て頂きたいのは23行目から29行目です。
'SELECT文を用意する sqlStr = "SELECT * FROM T_社員" 'クライアントサイドカーソルに変更する rs.CursorLocation = adUseClient 'Recordsetを開く rs.Open sqlStr, conn
以上のコードは、SQLiteのデータベースにあるテーブルからデータを取得する処理のコードです。
23行目のコードでは、「T_社員」テーブルから全件データを抽出するSELECT文を用意しています。
29行目のコードでは、SELECT文を実行してデータを取得しています。
注目すべきコード④
次に見て頂きたいのは32行目です。
'取得したデータを貼り付ける Worksheets("top").Cells(8, 1).CopyFromRecordset rs
以上のコードは、取得したデータをExcelのシートに貼り付けているコードです。
マクロが正常にSQLiteのデータベースに接続できたのか確認するために、試しにテーブルから取得してExcelのシートに貼り付けてみます。
今回のサンプルではA8のセルにデータを貼り付けるため「Cells(8, 1)」となっています。
A8のセルにデータを貼り付けた結果は下の画面の通りです。
ちなみに、取得元のSQLiteのテーブルのデータは下の画面の通りです。
※下の画面はこちらのページの「DB Browser for SQLite – .zip (no installer) for 64-bit Windows」を使った画面です。
取得したデータがセルA8のセルから貼り付けられました。
動作確認
マクロ実行前
今回使うExcelファイルは次のとおりで、セルA2にSQLiteのデータベースファイルの置き場をフルパスで入力しています。
実行ボタンをクリックするとマクロが実行されます。
取得の元のSQLiteのデータは下のとおりです。
※下の画面はこちらのページの「DB Browser for SQLite – .zip (no installer) for 64-bit Windows」を使った画面です。
マクロ実行後
マクロを実行すると以下の通り、SQLiteのデータベースにある「T_社員」のテーブルデータがExcelのセルに貼り付けられました。
マクロがSQLiteのデータベースに接続してテーブルからデータを取得し、Excelのシートに貼り付けるまでの動作を動画でお見せします。
【注意】参照設定が必要です
一つ注意点があるのですが、先ほどのコードを動かすには参照設定が必要です。
参照設定の一覧(下の画像を参考)から次の項目(ライブラリ)にチェックを付けて「OK」ボタンをクリックします。
- Microsoft ActiveX Data Objects 2.8 Library(msado28.tlb)
なぜ必要かというと、Excelのマクロのコードの5行目の「ADODB.Connection」と6行目の「ADODB.Recordset」というオブジェクトが「msado28.tlb」というファイルを参照するからです。
Dim conn As ADODB.Connection 'ADODB.Connectionオブジェクトのインスタンス用変数 Dim rs As ADODB.Recordset 'レコードセット用変数
この参照設定をしないと下の画像のエラーが出ますので必ず行う必要があります。
ここでは「msado28.tlb」とは何者かについては記事の本題から逸れてしまうので詳細は割愛しますが、マクロで「ADODB.Connection」「ADODB.Recordset」というオブジェクトを使う場合は参照設定しないと動かない、程度に思って頂ければと思います。
最後に
本記事では、ExcelのマクロがSQLiteのデータベースに接続する方法についてご説明しました。
ExcelのマクロがSQLiteのデータベースに接続するには、接続するためのドライバーが必要なのでドライバーを入手してインストールする必要があります。
ただし手順は難しくないので本記事に書いたこちらの手順を参考にしてみてください。
SQLiteのデータベースにあるテーブルデータをマクロが接続する方法を知りたいときは本記事を記事を参考にしてみてくださいね。
Excelのスキル向上やExcelの基礎知識をしっかりと学びたいなら
Excelのスキルを習得したい、Excelの基礎知識をもっと理解したい、そう考えているなら「無期限サポート付きExcel講座【すごい改善】」がおすすめです。
Excelのスキルの基礎を身につけるなら【すごい改善】で無期限サポート付きがあるので、これで「Excelのスキルや基礎」を学ぶのにおすすめですよ。