Excel
sqlite
VBA

ExcelからODBCなしでSQLiteを操作する(設定編)

More than 3 years have passed since last update.

ExcelVBAを使って、SQLiteを操作する方法です。

今回はODBCなしで操作する方法を書いていきます。


どんな時に使う?


  • Excelにインターフェースもデータも全部まとめててExcelブックそのものが重い時

  • Excelにデータをまとめているけど、複雑な検索をしたい時

  • 上記2つの時、データとアプリを分けたいけどインターフェースはExcelのままにしたい時

等など...


準備するもの


  1. SQLite for Excel:SQLiteForExcel-0.9.zip
    こちらのサイトからダウンロードできます。

  2. SQLiteの本体(DLL版):sqlite-dll-win32-x86-3100200.zip
    こちらのサイトからダウンロードできます。
    今回は32bit版で紹介します。


環境

使用環境
Bit

OS
Windows 10
64bit

Office
Excel 2013
32bit

DB
SQLite
32bit

ツール
SQLiteForExcel
32bit


手順


SQLiteForExcel-0.9.zipを解凍

中身は以下のような感じ。

パス:SQLiteForExcel\Distribution

image

パス:SQLiteForExcel\Source\SQLite3VBAModules\

image

その他にもSQLite3_StdCall.dllのソースとか入ってますけど、割愛します。

sqlite3.dllも入ってますが、今回はDLしてきた最新版を使います。

なので用するのは以下の4つ。


  • SQLite3_StdCall.dll

  • Sqlite3_64.bas

  • Sqlite3Demo_64.bas

  • SQLiteForExcel_64.xlsm(サンプルExel)


ファイルを集約

上記のファイルを1箇所にまとめます。そこにDLしてきた32bitのSQLiteを追加します。

image

なんで、Excelとかは64bitって書いてあるの使うのかってところなんですが、

32bit版のサンプルがxlsだったので、xlsmで実験してみました。

ちなみに、.xlsmと.basの64の表記は32bitと64bit両方に対応するために、分岐プログラムが書かれてます。


VBA

    #If Win64 Then

#Else
If hSQLiteStdCallLibrary = 0 Then
hSQLiteStdCallLibrary = LoadLibrary(libDir + "SQLite3_StdCall.dll")
If hSQLiteStdCallLibrary = 0 Then
Debug.Print "SQLite3Initialize Error Loading " + libDir + "SQLite3_StdCall.dll:", Err.LastDllError
SQLite3Initialize = SQLITE_INIT_ERROR
Exit Function
End If
End If
#End If

分岐は書いてあるけど、Excel本体が64bitだった場合SQLite3_StdCall.dllを読み込んでない用に見えますが・・・


モジュール読み込み

本来ならExcelを起動して、VBAのエディタ画面で Sqlite3_64.basとSqlite3Demo_64.basを読み込みます。

今回、サンプルExelのSQLiteForExcel_64.xlsmを使うので最初から読み込まれてます。

image

実際に使用する際は、必要なのはSqlite3なのでDemoはどちらでもOKです。


テスト実行

Sqlite3DemoのAllTestsはテストモジュールなので当然このまま動くのですが

デフォルトでは、DBファイルの作成先がC:\TestSqlite3ForExcel.db3になってます。

予めC:のパスを変更します。

image

パス変更をしたら、AllTestsを実行してみます。


testlog

SQLite3Close returned 0

----- TestError Start -----
SQLite3Open returned 14
SQLite3Open error messagE:\sqlite\db unable to open database file
SQLite3Close returned 0
----- TestError End -----
----- TestInsert Start -----
SQLite3Open returned 0
SQLite3PrepareV2 returned 0
SQLite3Step Done
SQLite3Finalize returned 0
SQLite3PrepareV2 returned 0
SQLite3Step Done
SQLite3Finalize returned 0
SQLite3Execute - Insert affected 1 record(s).
----- TestInsert End -----
----- TestSelect Start -----
SQLite3Open returned 0
SQLite3PrepareV2 returned 0
SQLite3Step Done
SQLite3Finalize returned 0
SQLite3PrepareV2 returned 0
SQLite3Step Done
SQLite3Finalize returned 0
SQLite3PrepareV2 returned 0
SQLite3Step Done
SQLite3Finalize returned 0
SQLite3PrepareV2 returned 0
SQLite3Step Row Ready
Column count: 3
Column 0: TheId INTEGER 123
Column 1: TheText TEXT ABC
Column 2: TheValue FLOAT 42.1
SQLite3Step Row Ready
Column count: 3
Column 0: TheId INTEGER 987654
Column 1: TheText TEXT ZXCVBNM
Column 2: TheValue NULL Null
SQLite3Step Done
SQLite3Finalize returned 0
----- TestSelect End -----
----- TestBinding Start -----
SQLite3Open returned 0
SQLite3Finalize returned 0
Insert Elapsed: 00:00:02
SQLite3PrepareV2 returned 0
At row 1
------------
Column count: 4
Column 0: TheId INTEGER 42501
Column 1: datetime(TheDate) TEXT 2001-05-16 00:00:00
Column 2: TheText TEXT The quick brown fox jumped over the lazy dog.
Column 3: TheValue FLOAT 333.240926265717
============
At row 10001
------------
Column count: 4
Column 0: TheId INTEGER 42501
Column 1: datetime(TheDate) TEXT 2001-05-16 00:00:00
Column 2: TheText TEXT The quick brown fox jumped over the lazy dog.
Column 3: TheValue FLOAT 333.240926265717
============
At row 20001
------------
Column count: 4
Column 0: TheId INTEGER 42501
Column 1: datetime(TheDate) TEXT 2001-05-16 00:00:00
Column 2: TheText TEXT The quick brown fox jumped over the lazy dog.
Column 3: TheValue FLOAT 333.240926265717
============
At row 30001
------------
Column count: 4
Column 0: TheId INTEGER 42501
Column 1: datetime(TheDate) TEXT 2001-05-16 00:00:00
Column 2: TheText TEXT The quick brown fox jumped over the lazy dog.
Column 3: TheValue FLOAT 333.240926265717
============
At row 40001
------------
Column count: 4
Column 0: TheId INTEGER 42501
Column 1: datetime(TheDate) TEXT 2001-05-16 00:00:00
Column 2: TheText TEXT The quick brown fox jumped over the lazy dog.
Column 3: TheValue FLOAT 333.240926265717
============
At row 50001
------------
Column count: 4
Column 0: TheId INTEGER 42501
Column 1: datetime(TheDate) TEXT 2001-05-16 00:00:00
Column 2: TheText TEXT The quick brown fox jumped over the lazy dog.
Column 3: TheValue FLOAT 333.240926265717
============
At row 60001
------------
Column count: 4
Column 0: TheId INTEGER 42501
Column 1: datetime(TheDate) TEXT 2001-05-16 00:00:00
Column 2: TheText TEXT The quick brown fox jumped over the lazy dog.
Column 3: TheValue FLOAT 333.240926265717
============
At row 70001
------------
Column count: 4
Column 0: TheId INTEGER 42501
Column 1: datetime(TheDate) TEXT 2001-05-16 00:00:00
Column 2: TheText TEXT The quick brown fox jumped over the lazy dog.
Column 3: TheValue FLOAT 333.240926265717
============
At row 80001
------------
Column count: 4
Column 0: TheId INTEGER 42501
Column 1: datetime(TheDate) TEXT 2001-05-16 00:00:00
Column 2: TheText TEXT The quick brown fox jumped over the lazy dog.
Column 3: TheValue FLOAT 333.240926265717
============
At row 90001
------------
Column count: 4
Column 0: TheId INTEGER 42501
Column 1: datetime(TheDate) TEXT 2001-05-16 00:00:00
Column 2: TheText TEXT The quick brown fox jumped over the lazy dog.
Column 3: TheValue FLOAT 333.240926265717
============
SQLite3Finalize returned 0
Select Elapsed: 00:00:04
----- TestBinding End -----
----- TestDates Start -----
SQLite3Open returned 0
SQLite3Finalize returned 0
SQLite3PrepareV2 returned 0
Event: Nice trip somewhere DatE:\sqlite\db 2010/06/19
SQLite3Finalize returned 0
----- TestDates End -----
----- TestStrings Start -----
SQLite3Open returned 0
SQLite3Finalize returned 0
SQLite3PrepareV2 returned 0
Result1:
Result2:
Long String is the samE:\sqlite\db True
SQLite3Finalize returned 0
----- TestStrings End -----
----- TestBackup Start -----
SQLite3Open returned 0
SQLite3PrepareV2 returned 0
SQLite3Step Row Ready
Column count: 2
Column 0: Key INTEGER 1
Column 1: Value TEXT First
SQLite3Step Row Ready
Column count: 2
Column 0: Key INTEGER 2
Column 1: Value TEXT Second
SQLite3Step Done
SQLite3Finalize returned 0
SQLite3Open returned 0
SQLite3BackupStep returned 101
SQLite3BackupFinish returned 0
Backup result 0
Selecting from backup:
SQLite3PrepareV2 returned 0
SQLite3Step Row Ready
Column count: 2
Column 0: Key INTEGER 1
Column 1: Value TEXT First
SQLite3Step Row Ready
Column count: 2
Column 0: Key INTEGER 2
Column 1: Value TEXT Second
SQLite3Step Done
SQLite3Finalize returned 0
----- TestBackup End -----
----- TestBlob Start -----
SQLite3Open returned 0
SQLite3Finalize returned 0
SQLite3PrepareV2 returned 0
Blob byte 0: 90
Blob byte 1: 91
Blob byte 2: 92
SQLite3Finalize returned 0
----- TestBlob End -----
SQLite3Open returned 0
SQLite3OpenV2 returned 0
SQLite3PrepareV2 returned 0
SQLite3Step returned 101
SQLite3Finalize returned 0
SQLite3PrepareV2 returned 0
SQLite3Step returned 8
Cannot Write in Read Only database
SQLite3Finalize returned 8
SQLite3PrepareV2 returned 0
SQLite3Step returned 101
But Reading is granted on Read Only database
SQLite3Finalize returned 0
SQLite3Close V2 returned 0
SQLite3Close returned 0

正常にテストができれば、これで疎通は問題ありません。

あとはテストのモジュールからどの関数が何をやっているのか

紐解いていけば好きに使えると思います!


まとめ

今回は環境を整えるまでの内容です。

実際にテーブル作ったり、SELECTやINSERT等の説明は

また次回にしようとおもいます!

テストモジュール内でCREATEやINSERT、SELECTはやっているので

今はそれを紐解きつつ、手元のデータをDB化してます。