ExcelでSQLiteのデータを参照したい
私は会社でソフトウェアのテスト項目をExcelで作成・管理しています。
様々な前提条件(製品の設定の組み合わせ)でテストを組んでいるのですが、
ある日ふと「こんな設定の組み合わせで使っているお客さんはいるんだろうか？このテストって意味あるんかな…？」と疑念がわきました。
お客さんが製品をどのような設定で使っているのかのデータはあります。
ですが、そのデータ量が膨大なため、Excelやcsvで扱うことが難しく、SQLiteの形でデータベースを管理しています。
なので、例えば、
「設定A=1、設定B=3、設定C=2」で運用されている製品が市場でどのくらいあるかを調べるには、Excelとは別にSQLiteを読みこんで調べないといけないわけです。これはとても面倒です。
ExcelのVBAを使ってSQLiteのデータを参照することができれば、この作業はとてもスムーズになるはずです。
ExcelのVBAでSQLiteのデータを参照する
では、具体的にExcelのVBAでSQLiteを参照するコードを書いていきます。
なお、今回はSQLiteに対する書き込みは行いません。読み取りのみです。
SQLiteのデータベースとしては、データ分析のコンペなどでも有名な「タイタニック号の乗客名簿」を利用します。
このタイタニックのデータでは乗客の名前、年齢、性別などの情報が記載されています。
最終的に作りたいのは、上記のようにセルに性別と年齢を入力すると、その条件に合致する乗客の人数をSQLiteのデータから抽出してセルに入力してくれるマクロです。
ActiveX Data Objects Libraryの参照設定
今回はSQLiteにアクセスするために、ADO(ActiveX Data Objects)というMicrosoftのデータアクセスの仕組みを使います。
ADOとは、Microsoftが提唱しているデータアクセス技術のことである。
ADOは、OLE DBを利用して、アプリケーションからAccess、SQL Serverをはじめ、Oracleなどのさまざまなデータソースへ統一的で高性能なアクセスインタフェースを提供するCOMベースのデータベースアクセス技術である。
(IT用語辞典より引用)
ADOを使うためには、ActiveX Data Objects Libraryの参照を有効にする必要があります。
Visual Basic Editorのメニューバーから「ツール」−「参照設定」をクリックし、
「Microsoft ActiveX Data Objects x.x Library」にチェックを入れましょう。
※x.xの部分は複数あると思いますが、一番新しそうなものにチェックを入れてください。
接続オブジェクトとレコードセットの作成
SQLiteに接続するための接続オブジェクトと、
SQLiteから抽出したデータ(レコードセット)を格納するためのレコードセットオブジェクトを作成します。
Dim adoConnect As New ADODB.Connection '接続オブジェクト
Dim adoRecord As New ADODB.recordSet 'レコードセット
SQLの実行
次に、作成した接続オブジェクト(adoConnect)を使って、SQLiteのデータベースに接続してみましょう。
まず、データベースに接続するための「接続文字列」を定義し、adoConnect.Open 接続文字列のコマンドで接続します。
接続文字列のDatabase=の後にはアクセスしたいSQLiteのファイルパスを指定します。
Dim connectionString As String '接続文字列
connectionString = "DRIVER=SQLite3 ODBC Driver;Database=C:\Users\konitech\titanic.db"
adoConnect.Open connectionString '接続オープン
これで、SQLiteデータベースに接続ができました。
次にSQL文を実行して目当てのデータを抽出してみましょう。
本来はExcelのセルに記載されている性別と年齢の値を取得するところですが、まずは簡単のために「40歳以上の男性(Sex='male' & Age>=40)」と決め打ちでSQLを書いてしまいます。
なお、本記事ではSQL自体の説明は割愛させていただきます。SQLの基本を知りたい方は、「SQL 入門」などでGoogle検索すると情報がたくさん出てきますので調べてみてください。
adoRecord.Openに「SQL文」と「先ほど接続オープンした接続オブジェクト」を指定することで、SQLが実行されます。
Dim sSQL As String 'SQL
sSQL = "SELECT * FROM titanic WHERE Sex='male' AND Age>=40"
adoRecord.CursorLocation = adUseClient 'クライアントカーソルを設定
adoRecord.Open sSQL, adoConnect '読み取り専用でSQLを取得
SQLで取得したデータの確認
レコードセット(adoRecord)にSQL実行で取得したデータが格納されています。
まずは取得したレコード数を確認してみましょう。
Debug.Printで「イミディエイトウィンドウ」に結果を出力します。
イミディエイトウィンドウが表示されていない場合は、メニューバーから「表示」−「イミディエイトウィンドウ」で開いておきましょう。
Debug.Print adoRecord.RecordCount 'レコード数を出力
59
59レコード、つまり「40歳以上の男性」に該当する人は59人いるということがわかりました。
では、次はレコードセットからレコードを1行ずつ取り出してみましょう。ここではName列を取り出してみます。
'取得したレコード(行)を順に取り出す
Do While Not adoRecord.EOF ' 最終行までループ
Debug.Print adoRecord("Name") 'Name列の値を出力
adoRecord.MoveNext '次の行へ移動
Loop
Myles, Mr. Thomas Francis
Jones, Mr. Charles Cresson
Howard, Mr. Benjamin
Rothschild, Mr. Martin
Olsen, Master. Artur Karl
Robins, Mr. Alexander A
Brady, Mr. John Bertram
…
ちゃんとNameが取得できていますね。
最後に、接続オブジェクトとレコードセットオブジェクトの後始末をしておきます。
'クリーンアップ処理
adoRecord.Close
adoConnect.Close
Set adoRecord = Nothing
Set adoConnect = Nothing
これで完了です！
全コード
ここまでの全コードをまとめて載せておきます。
Sub accessSQLite()
Dim adoConnect As New ADODB.Connection '接続オブジェクト
Dim adoRecord As New ADODB.recordSet 'レコードセット
Dim connectionString As String '接続文字列
connectionString = "DRIVER=SQLite3 ODBC Driver;Database=C:\Users\konitech\titanic.db"
adoConnect.Open connectionString '接続オープン
Dim sSQL As String 'SQL
sSQL = "SELECT * FROM titanic WHERE Sex='male' AND Age>=40"
adoRecord.CursorLocation = adUseClient 'クライアントカーソルを設定
adoRecord.Open sSQL, adoConnect '読み取り専用でSQLを取得
Debug.Print adoRecord.RecordCount 'レコード数を出力
'取得したレコード(行)を順に取り出す
Do While Not adoRecord.EOF ' 最終行までループ
Debug.Print adoRecord("Name") 'Name列の値を出力
adoRecord.MoveNext '次の行へ移動
Loop
'クリーンアップ処理
adoRecord.Close
adoConnect.Close
Set adoRecord = Nothing
Set adoConnect = Nothing
End Sub
Excelのセルを入出力するように少し改良
さて、もともと作りたかったのは、EXCELのセルに性別と年齢を入力して、その条件に合致する乗客の人数をSQLiteのデータから抽出してセルに入力してくれるマクロでしたね。
では、上記のB3セル(=Sex)とC3セル(=Age)の値を使ってSQL文を実行し、結果をD3セルに書き込むようにマクロを少し書き直してみましょう。
下記で、★をつけた行を追加/変更するだけです。
Sub accessSQLite2()
Dim adoConnect As New ADODB.Connection '接続オブジェクト
Dim adoRecord As New ADODB.recordSet 'レコードセット
Dim connectionString As String '接続文字列
connectionString = "DRIVER=SQLite3 ODBC Driver;Database=C:\Users\konitech\titanic.db"
adoConnect.Open connectionString '接続オープン
Dim sSQL As String 'SQL
Dim input_sex As String '★追加
Dim input_age As String '★追加
input_sex = Sheets("Sheet1").Range("B3") '★追加
input_age = Sheets("Sheet1").Range("C3") '★追加
sSQL = "SELECT * FROM titanic WHERE Sex='" & input_sex & "' AND Age>= " & input_age '★変更
adoRecord.CursorLocation = adUseClient 'クライアントカーソルを設定
adoRecord.Open sSQL, adoConnect '読み取り専用でSQLを取得
Debug.Print adoRecord.RecordCount 'レコード数を出力
Sheets("Sheet1").Range("D3").Value = adoRecord.RecordCount '★追加
'取得したレコード(行)を順に取り出す
Do While Not adoRecord.EOF ' 最終行までループ
Debug.Print adoRecord("Name") 'Name列の値を出力
adoRecord.MoveNext '次の行へ移動
Loop
'クリーンアップ処理
adoRecord.Close
adoConnect.Close
Set adoRecord = Nothing
Set adoConnect = Nothing
End Sub
このマクロを「ボタン」に設定して、ポチッと押すと…
はい、D3セルに人数が入力されました！