Excel VBAでSQLiteのデータを参照する

## ExcelでSQLiteのデータを参照したい

私は会社でソフトウェアのテスト項目をExcelで作成・管理しています。

様々な前提条件(製品の設定の組み合わせ)でテストを組んでいるのですが、
ある日ふと**「こんな設定の組み合わせで使っているお客さんはいるんだろうか？このテストって意味あるんかな…？」**と疑念がわきました。

お客さんが製品をどのような設定で使っているのかのデータはあります。
ですが、そのデータ量が膨大なため、Excelやcsvで扱うことが難しく、**SQLite**の形でデータベースを管理しています。

なので、例えば、
「設定A=1、設定B=3、設定C=2」で運用されている製品が市場でどのくらいあるかを調べるには、Excelとは別にSQLiteを読みこんで調べないといけないわけです。これはとても面倒です。

![before.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/634673/fc475cdc-4c40-1331-38c0-71b90f9ab425.png)

**ExcelのVBAを使ってSQLiteのデータを参照する**ことができれば、この作業はとてもスムーズになるはずです。

![after.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/634673/2c45f515-b0df-7bd0-6ed3-a291cfc1c6c4.png)

## ExcelのVBAでSQLiteのデータを参照する
では、具体的にExcelのVBAでSQLiteを参照するコードを書いていきます。

なお、今回はSQLiteに対する書き込みは行いません。読み取りのみです。

SQLiteのデータベースとしては、データ分析のコンペなどでも有名な「タイタニック号の乗客名簿」を利用します。
このタイタニックのデータでは乗客の名前、年齢、性別などの情報が記載されています。

![ADO_0.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/634673/a33eeee2-b700-86ed-9fcd-761c01952e37.png)


最終的に作りたいのは、上記のようにセルに性別と年齢を入力すると、その条件に合致する乗客の人数を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の部分は複数あると思いますが、一番新しそうなものにチェックを入れてください。
![ADO_1.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/634673/9d8c32fe-1700-b973-7c1c-7dfa56a66940.png)

### 接続オブジェクトとレコードセットの作成

SQLiteに接続するための**接続オブジェクト**と、
SQLiteから抽出したデータ(レコードセット)を格納するための**レコードセットオブジェクト**を作成します。

```visualbasic:VBA

    Dim adoConnect As New ADODB.Connection '接続オブジェクト
    Dim adoRecord As New ADODB.recordSet 'レコードセット
```

### SQLの実行
次に、作成した接続オブジェクト(adoConnect)を使って、SQLiteのデータベースに接続してみましょう。
まず、データベースに接続するための「接続文字列」を定義し、`adoConnect.Open 接続文字列`のコマンドで接続します。
接続文字列の`Database=`の後にはアクセスしたいSQLiteのファイルパスを指定します。

```visualbasic:VBA

    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が実行されます。

```visualbasic:VBA

    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`で「イミディエイトウィンドウ」に結果を出力します。
イミディエイトウィンドウが表示されていない場合は、メニューバーから「表示」−「イミディエイトウィンドウ」で開いておきましょう。

```visualbasic:VBA
    Debug.Print adoRecord.RecordCount 'レコード数を出力
```

```:実行結果
59
```
59レコード、つまり「40歳以上の男性」に該当する人は59人いるということがわかりました。

では、次はレコードセットからレコードを1行ずつ取り出してみましょう。ここではName列を取り出してみます。

```visualbasic:VBA
    '取得したレコード(行)を順に取り出す
    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が取得できていますね。

最後に、接続オブジェクトとレコードセットオブジェクトの後始末をしておきます。

```visualbasic:VBA
    'クリーンアップ処理
    adoRecord.Close
    adoConnect.Close
    
    Set adoRecord = Nothing
    Set adoConnect = Nothing
```

これで完了です！

### 全コード

ここまでの全コードをまとめて載せておきます。

```visualbasic:VBA
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のデータから抽出してセルに入力してくれるマクロでしたね。

![ADO_0.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/634673/9289dac9-4a80-c7e0-1646-4828c8dc18d5.png)

では、上記のB3セル(=Sex)とC3セル(=Age)の値を使ってSQL文を実行し、結果をD3セルに書き込むようにマクロを少し書き直してみましょう。

下記で、★をつけた行を追加/変更するだけです。

```visualbasic:VBA
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
```

このマクロを「ボタン」に設定して、ポチッと押すと…

![ADO_2.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/634673/94d44a67-9c37-99c7-3425-635e810697df.png)

はい、D3セルに人数が入力されました！