LoginSignup
60
67

More than 5 years have passed since last update.

@hisayuki

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

Last updated at Posted at 2016-01-30

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

どんな時に使う？

  • Excelにインターフェースもデータも全部まとめててExcelブックそのものが重い時
  • Excelにデータをまとめているけど、複雑な検索をしたい時
  • 上記２つの時、データとアプリを分けたいけどインターフェースは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化してます。

60
67
1

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
60
67