Excelでデータベース(SQLite3)を扱う

1.概要

扱うデータ件数が数十万件となると、Excelのシート内では、読み込みにも時間が掛かるし、場合によってはパソコンの性能に左右されて固まってしまいます。読みんだ後も動作が緩慢で使い勝手が悪くなります。数十万件のデータでも必要な部分は数百から数千件のデータである場合が多いものです。このような場合は、データベースを利用するとページ単位の読み込みや必要なデータの検索などが比較的容易にできるようになります。今回は、手軽に利用できるSQLite3によるデータベースをExcelで扱う方法について紹介します。

2.事前準備作業

ExcelなどからSQLiteのデータベースを扱うには、本体であるsqlite.exeやsqlite.dllなどが必要な場合もありますが、今回、紹介するものは、色んな言語で汎用的に利用できるODBCドライバをインストールしてデータベースを扱います。一度、インストールしてしまえば、Excel上ではDSN(データソース)の設定なども必要ありません。

2-1.ODBCドライバーの入手とインストール

まずは、ODBCドライバーの入手とインストールです。ODBCドライバーは海外サイトですが、下記のサイトからダウンロードできます。海外サイトということで怪しいと思われますが、広告や他のサイトへの誘導などはありません。

ダウンロード先 : http://www.ch-werner.de/sqliteodbc/

このサイトの真ん中あたりにCurrent versionという部分の下に2つのodbcドライバーがあります。 sqliteodbc.exeは32bit版で、sqliteodbc w64.exeは64bit版です。WindowsのOSが32bitのものか64bitのものか、また、Excelのバージョンが 32bit版なのか64bit版なのか判断に迷います。
※(どれを選択すれば良いか筆者には適切に指示するこはできません)
両方インストールしても問題なさそうですが、たぶん、利用しているExcelのバージョンによるものだと思います。筆者は、Windows10の64bit版で、Excelも64bit版なので、sqliteodbc w64.exeをダウンロードしてインストールしました。

ダウンロードしたら、sqliteodbc w64.exe(64bit版の場合)をダブルクリックして実行して下さい。専用のインストーラが立ち上がり、インストーラの指示に従って、次へをクリックして下さい。ネットを検索すれば、インストール画面例などがあるサイトもありますので、インストール操作が不安な場合は、手順を調べてみて下さい。筆者は過去にすでにインストール済みなので画面例などは割愛させて頂きます。
※インストール作業は、全て自己責任でお願いします。万一、何らかのトラブルや損害が生じても
筆者は、一切の責任も負いません。

2-2.データベースの作成ツールの紹介

データベースですが、SQLite3用のデータベースを作成する必要があります。作成方法には、いくつかの方法がありますが、初期作成時は、csvファイルなどを準備して一括でインポートする方法が一般的です。コマンドラインで行うには、SQLite3のコマンドラインツールである sqlite3.exeが必要です。

SQLiteの公式ページへアクセスして入手して下さい。

公式サイト : https://www.sqlite.org/
ダウンロードページ : https://www.sqlite.org/download.html

32bit版と64bit版の2種類のアーカイブが提供されているので、ご自身のOSのものをダウンロードとて解凍します。任意のフォルダ(例 : sqlit3)に展開して下さい。展開すると、3つのファイルが格納されています。

下記のようなSQLite管理ツールもそろえておくと便利です。
SQLiteのデータベースをGUIで操作できるWindows用のSQLite管理ツール(TkSQLite)もデータベースの中身の確認やテーブルスキーマの設定などが簡単に行えるようになります。もちろん、外部ファイルからのインポートやエクスポート機能があるので、CSVファイルなどから簡単にデータベースを作成することができます。

ダウンロードページ : http://reddog.s35.xrea.com/wiki/TkSQLite.html#download/

ダウンロードしたら、任意フォルダで解凍して下さい。TkSQLite.exeのみしか格納されていません。デスクトップにショートカットを作成しておくと良いでしょう。

2-3.データベースの作成

SQLite.exeでコマンドラインから毎回、手入力するは面倒なのと、指定パラメータを忘れたりするので、簡単なバッチファイルのテンプレートを作成してデータベースファイル名、インポート用CSVファイル名、テーブル名の指定部分を書き換えた、インポート用のバッチファイルを利用しています。今回はデータベース作成の実例として、leaflet地図作成時に使った震度観測点のCSVファイルがあったので、作成手順の例題として使って見ました。

(1)SQLite3.exe、import.bat、eqmpoint.csvの3つのファイルを同じフォルダにおいて下さい。
(パスをいちいち指定するのが面倒なため)

(2)データベース作成用のインポートCSVファイルを準備します。インポート用のCSVファイルの
     サンプルです。データベースでCSVの1行目を列名として使うので設定しています。
     インポートするCSVファイルのエンコードは、UTF-8として下さい。もし、DB作成後文字化け
     してた場合は、TkSQLite.exeでエンコード指定し直せば解消されます。

(3)import.batの中身を確認して、必要であれば修正後、上書き保存します。ダブルクリックして
     実行します。数万件規模ならば、一瞬で作成が完了します。
     ※【注意】バッチファイルの実行には、sqlite3.exeが必要ですのでインポート元ファイルと
     同一フォルダにおいて下さい。

import.bat@echo off
setlocal
cd /d %~dp0
 
echo.
echo.
echo ***********************************************************
echo.
echo    SQLite3用データインポートバッチファイル (雛形)
echo.
echo.
echo ***********************************************************
echo.
echo.
 
rem memo : 作成するDBファイル名 インポートCSVファイル名 テーブル名
rem ***** インポート開始 *****
sqlite3 -separator , eqmpoint.db ".import eqmpoint.csv eqmaptbl"
 
echo 処理が完了しました。何かキーを押してください。
echo.
 
pause >nul
endlocal
exit

import.bat
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25	`@echo off` `setlocal` `cd /d %~dp0` `echo.` `echo.` `echo *********************************************************` `echo.` `echo SQLite3用データインポートバッチファイル (雛形)` `echo.` `echo.` `echo *******************************************************` `echo.` `echo.` `rem memo : 作成するDBファイル名インポートCSVファイル名テーブル名` `rem * インポート開始 ***` `sqlite3 -separator , eqmpoint.db ".import eqmpoint.csv eqmaptbl"` `echo 処理が完了しました。何かキーを押してください。` `echo.` `pause >nul` `endlocal` `exit`

(4)iTkSQLite.exeを起動して作成したデータベースの中身を確認します。
メニューバーのファイル → 開くをクリックして下さい。

(5)ファイル選択ダイアログが開くので、作成済みのeqmpoint.dbを選択して開いて下さい。

(6)テーブル名のeqmaptblをダブルクリックすると、テーブル編集画面にデータが読み込まれ表示
されます。

(7)読み込まれたテーブルの文字が文字化けしてた場合は、メニューバーのデータベース → 文字
コードで確認します。

以上の説明でデータベースの作成については終わりです。TkSQLite.exeのメニューバーのSQLite3DBの新規作成 → データベースファイル名のみを登録 → 読み込み → テキストファイルと辿っていくと外部ファイルからのインポートによるデータベースの作成ができます。今回は、この手順については説明しません。初回の構築時は、バッチファイルの方が簡単で高速です。

3.ExcelでのSQliteの操作

テスト用のデータベースを準備して、リストの動作確認サンプル.xlsmを実行します。うまくデータベースと接続出来てテーブルの内容が正しく表示できれば、ExcelでSQLite3のデータベースが動作しています。
※テスト用のデータベーサンプルと動作確認サンプル.xlsmはダウンロード配布しています。

下記リストの動作確認サンプル.xlsmのハイライト部分をご自身で作成したデータベースに合わせて変更すれば、テーブルの内容確認として利用できます。

動作確認サンプル.xlsmSub SQLiteRead()
Dim filename   As String
Dim i          As Integer
Dim nCount     As Integer
Dim RecCount   As Long
Dim DBName     As String
Dim Sql        As String
Dim Db         As Object
Dim rs         As Object
 
'***** データベース名 *****
filename = ThisWorkbook.Path & "¥function.db"
 
'***** ODBCで接続 *****
DBName = "DRIVER=SQLite3 ODBC Driver;Database=" & filename & ";"
Set Db = CreateObject("ADODB.Connection")
Db.CursorLocation = 3
Db.Open DBName
 
'***** SQL文発行 *****
Sql = "SELECT * FROM TFunction;"
Set rs = CreateObject("ADODB.Recordset")
rs.Open Sql, Db, 3, 3
Set rs = Db.Execute(Sql)
RecCount = rs.RecordCount
 
Application.ScreenUpdating = False      ' 画面更新を一時停止
 
'結果をセルに表示する (実験のため、先頭から500件の読み込み表示)
'A列の2行目からF列まで
i = 2: nCount = 0
Do Until rs.EOF = True
    Cells(i, 1).Value = rs.Fields(0).Value
    Cells(i, 2).Value = rs.Fields(1).Value
    Cells(i, 3).Value = rs.Fields(2).Value
    Cells(i, 4).Value = rs.Fields(3).Value
    Cells(i, 5).Value = rs.Fields(4).Value
    Cells(i, 6).Value = rs.Fields(5).Value
    i = i + 1
    nCount = nCount + 1
    rs.MoveNext
    If nCount = 500 Then Exit Do
Loop
 
Application.ScreenUpdating = True  ' 画面更新一時停止を解除
 
'***** 接続を閉じる *****
rs.Close
Db.Close
Set rs = Nothing
Set Db = Nothing
End Sub

動作確認サンプル.xlsm
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52	`Sub` `SQLiteRead()` `Dim` `filename` `As` `String` `Dim` `i` `As` `Integer` `Dim` `nCount` `As` `Integer` `Dim` `RecCount` `As` `Long` `Dim` `DBName` `As` `String` `Dim` `Sql` `As` `String` `Dim` `Db` `As` `Object` `Dim` `rs` `As` `Object` `'*** データベース名 *` `filename = ThisWorkbook.Path &` `"¥function.db"` `'* ODBCで接続 *` `DBName =` `"DRIVER=SQLite3 ODBC Driver;Database="` `& filename &` `";"` `Set` `Db = CreateObject("ADODB.Connection")` `Db.CursorLocation = 3` `Db.Open DBName` `'* SQL文発行 **` `Sql =` `"SELECT FROM TFunction;"` `Set` `rs = CreateObject("ADODB.Recordset")` `rs.Open Sql, Db, 3, 3` `Set` `rs = Db.Execute(Sql)` `RecCount = rs.RecordCount` `Application.ScreenUpdating =` `False` `' 画面更新を一時停止` `'結果をセルに表示する (実験のため、先頭から500件の読み込み表示)` `'A列の2行目からF列まで` `i = 2: nCount = 0` `Do` `Until` `rs.EOF =` `True` `Cells(i, 1).Value = rs.Fields(0).Value` `Cells(i, 2).Value = rs.Fields(1).Value` `Cells(i, 3).Value = rs.Fields(2).Value` `Cells(i, 4).Value = rs.Fields(3).Value` `Cells(i, 5).Value = rs.Fields(4).Value` `Cells(i, 6).Value = rs.Fields(5).Value` `i = i + 1` `nCount = nCount + 1` `rs.MoveNext` `If` `nCount = 500` `Then` `Exit` `Do` `Loop` `Application.ScreenUpdating =` `True` `' 画面更新一時停止を解除` `'*** 接続を閉じる ***` `rs.Close` `Db.Close` `Set` `rs =` `Nothing` `Set` `Db =` `Nothing` `End` `Sub`

動作確認サンプル.xlsmは、何の制御もしていない(実際は、500件で表示ストップ)ので、データ件数分、読みだして表示します。下記のsqlite3test.xlsmリストは、設定したページ単位の制御と先頭ページや最終ページへのジャンプ制御などを追加したテンプレートサンプルです。 sqlite3test.xlsmのSheet1(MAIN)には、それぞれのボタンをクリックした時のモードフラグに応じたページ制御を行うコードです。標準モジュールのSQLiteCtrが本体部分となります。
標準モジュール : SQLiteCtrの下記部分を変更すれば、汎用的に利用できるようにテンプレートとしています。

今回のサンプルでは、検索・抽出部分は省略していますが、65行目のSQL文に与えられた条件にて検索できるように構文を追加して改造すれば可能です。複数検索条件を設定(指定)する部分は、シート画面上に作る必要があります。
SQL文の新規追加(INSERT)、削除(DELETE)、更新(UPDATE)を利用すれば、Excel上からでもデータベースのメンテ機能を持たせることができますが、データベースのメンテは、別の言語で作成した方が使い勝手が良さそうなので、 Excelで機能を実装するのはお勧めしません。
尚、下記のサンプルで利用しているデータベースは、筆者がだいぶ以前にHSP言語で作ったWindowsAPI関数の検索用として作成したものです。今回のサンプルのテスト用として利用することとしました。特別、何か意味のあるものではありません。データ件数も約17,000件登録してあり、サンプルとして利用するのに適度な件数があります。

sqlite3test.xlsm : Sheet1(MAIN)'***** 実行開始 *****
Private Sub StartDB_Click()
   Call DataBase_Init
   Call SQLite(0)
End Sub
 
'***** 前ページ表示制御 *****
Private Sub BeforePage_Click()
   Call SQLite(0)
End Sub
 
'***** 次ページ表示制御 *****
Private Sub NextPage_Click()
   Call SQLite(1)
End Sub
 
'***** 先頭ページ表示制御 *****
Private Sub FirstPage_Click()
   Call StartDB_Click
End Sub
 
'***** 最終ページ表示制御 *****
Private Sub LastPage_Click()
   Call SQLite(3)
End Sub
 
'***** 各変数の初期化 *****
Private Sub DataBase_Init()
   StartPoint = 0
   PageNo = 0
   PageCount = 0
   BackPoint = 0
   ReadRec = 0
   DispRec = 0
   N_Page = 0
   N_Num = 0
   B_Page = 0
   B_Num = 0
   LastPage = 0
   StartFLG = 0
   KeepPage = 0
End Sub

sqlite3test.xlsm : Sheet1(MAIN)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42	`'*** 実行開始 *` `Private` `Sub` `StartDB_Click()` `Call` `DataBase_Init` `Call` `SQLite(0)` `End` `Sub` `'* 前ページ表示制御 *` `Private` `Sub` `BeforePage_Click()` `Call` `SQLite(0)` `End` `Sub` `'* 次ページ表示制御 *` `Private` `Sub` `NextPage_Click()` `Call` `SQLite(1)` `End` `Sub` `'* 先頭ページ表示制御 *` `Private` `Sub` `FirstPage_Click()` `Call` `StartDB_Click` `End` `Sub` `'* 最終ページ表示制御 *` `Private` `Sub` `LastPage_Click()` `Call` `SQLite(3)` `End` `Sub` `'* 各変数の初期化 ***` `Private` `Sub` `DataBase_Init()` `StartPoint = 0` `PageNo = 0` `PageCount = 0` `BackPoint = 0` `ReadRec = 0` `DispRec = 0` `N_Page = 0` `N_Num = 0` `B_Page = 0` `B_Num = 0` `LastPage = 0` `StartFLG = 0` `KeepPage = 0` `End` `Sub`

sqlite3test.xlsm : 標準モジュール : SQLiteCtr'*************************************************************
'*       標準モジュール : SQLiteCtr
'*
'*       ●Excelでのデータベース操作 (SQLite3による実験)
'*
'*
'*************************************************************
 
'***** グローバル変数 *****
Public My_Row    As Long        ' 行位置
Public My_Col    As Long        ' 列位置
Public StarPoint As Integer     ' レコード開始位置
Public PageNo    As Integer     ' ページ番号
Public PageCount As Integer     ' ページカウンタ
Public BackPoint As Integer     ' 前ページ開始位置
Public B_Page    As Integer     ' 前ページ開始位置一時保存
Public B_Num     As Integer     ' 前ページ番号一時保存
Public N_Page    As Integer     ' 次ページ開始位置一時保存
Public N_Num     As Integer     ' 次ページ番号一時保存
Public StartFLG  As Integer     ' 前ページ、次ページ判定フラグ
Public KeepPage  As Integer     ' 最終ページ番号保存用
 
Public filename  As String      ' DataBase FileName
Public i         As Long        ' セルの表示開始位置制御カウンタ
Public DBName    As String      ' DataBace ODBC定義 (SQLite3)
Public Sql       As String      ' SQL
Public Db        As Object      ' DataBase
Public rs        As Object      ' Recordset
    
'***** データベース処理開始 (全件表示 : 100件単位) *****
Public Sub SQLite(ByVal StartFLG As Integer)
   Dim S_PageCnt  As Integer    ' 開始ページカウンタ保存用
   Dim S_PageNum  As Integer    ' 開始ページ番号保存用
   Dim ReadRec    As Integer    ' 読み出しレコードカウンタ
   Dim DispRec    As Integer    ' 表示件数制御カウンタ
   Dim LastPage   As Integer    ' 最終ページ番号取得用
    
   '***** 前ページをセット *****
   If StartFLG = 0 Then
      S_PageCnt = B_Page
      S_PageNum = B_Num
   End If
   '***** 次ページをセット *****
   If StartFLG = 1 Then
      S_PageCnt = N_Page
      S_PageNum = N_Num
   End If
   '***** 最終ページをセット *****
   If StartFLG = 3 Then
      S_PageCnt = (KeepPage * PageCount) - PageCount + 1
      S_PageNum = KeepPage
   End If
    
   Sheets("MAIN").Select
   Worksheets("MAIN").Activate
   If Range("A5") <> "" Then
      '※表示するデータの項目数によって適時変更のこと
      Range("A5:F110").SpecialCells(xlCellTypeConstants, 23).ClearContents
   End If
    
   '***** DB FileName設定 (※変更のこと) *****
   Call SQLiteDB_Open("function.db")
    
   '***** テーブル名設定 (※変更のこと)  *****
   Sql = "SELECT * FROM TFunction;"
   Call Table_Set
    
   '***** 総レコード件数 *****
   RecCount = rs.RecordCount
   Range("F3") = RecCount
    
   '***** 画面の表示更新停止開始 *****
   Application.ScreenUpdating = False
    
   '***** ページ制御カウンタ (100件/1ページに設定している) *****
   StartPoint = S_PageCnt
   PageNo = S_PageNum
   PageCount = 100
   BackPoint = StartPoint - PageCount
   ReadRec = 0
   DispRec = 0
 
   '***** 初期値確認 (StartPoint) *****
   If StartPoint = 0 Then
      StartPoint = 1
      PageNo = 1
      Call BttonCTN_off
   End If
   '***** 初期値確認 (PageNo) *****
   If PageNo = 0 Then
      PageNo = 1
      Call BttonCTN_off
   End If
   '***** ページ番号を表示 *****
   Range("A3") = PageNo
   ' 最終ページ番号
   LastPage = (RecCount / PageCount)
   '最終ページ保存
   KeepPage = LastPage
   Range("B3") = PageNo & " / " & KeepPage
 
   If rs.EOF Then
      StartPoint = 0
      PageNo = 0
      PageCount = 0
      BackPoint = 0
      ReadRec = 0
      DispRec = 0
      StartFLG = 0
      ' 全制御ボタンを不活性化
      Call BttonCTN_off
      Call ButtonSE_off
   Else
      '***** 結果をセルに書き込む (表示) *****
      i = 5  ' 表示開始位置 : 5行目～104行目
      Do Until rs.EOF = True
         ' 読み込みレコードカウンタ
         ReadRec = ReadRec + 1
         ' データ開始レコードまで読み飛ばし
         If StartPoint <= ReadRec Then
            ' 設定(100件/頁)になったら表示を終了
            If DispRec = PageCount Then
               Exit Do
            End If
            ' 表示レコードカウンタ
            DispRec = DispRec + 1
            ' 結果の表示 (※項目数によって適時変更のこと)
            Cells(i, 1).Value = ReadRec
            Cells(i, 2).Value = rs.Fields(1).Value
            Cells(i, 3).Value = rs.Fields(2).Value
            Cells(i, 4).Value = rs.Fields(3).Value
            Cells(i, 5).Value = rs.Fields(4).Value
            Cells(i, 6).Value = rs.Fields(5).Value
            i = i + 1
         End If
         rs.MoveNext
      Loop
       
      '***** 画面の表示更新停止解除 *****
      Application.ScreenUpdating = True
 
      '***** レコードの開始位置 *****
      StartPoint = PageCount * PageNo + 1
    
      '***** ページ制御 *****
      ' 前ページの場合
      If PageNo > 1 Then
         Call BttonCTN_on
         StartFLG = 0
         B_Page = BackPoint
         B_Num = PageNo - 1
      End If
      ' 次ページの場合
      If PageNo < LastPage Then
         Call ButtonSE_on
         StartFLG = 1
         N_Page = StartPoint
         N_Num = PageNo + 1
      End If
   End If
    
   '***** 前ページボタン不活性化 *****
   If PageNo = 1 Then
      Call BttonCTN_off
   End If
   '***** 次ページボタン不活性化 *****
   If PageNo >= LastPage Then
      Call ButtonSE_off
   End If
 
   Range("A1").Select
    
   '***** 接続を閉じる *****
   Call DB_Close
End Sub
 
'***** データベースオープン *****
Sub SQLiteDB_Open(ByVal DBSetName As String)
   filename = ThisWorkbook.Path & "¥" & DBSetName
   DBName = "DRIVER=SQLite3 ODBC Driver;Database=" & filename & ";"
   Set Db = CreateObject("ADODB.Connection")
   Db.CursorLocation = 3
   Db.Open DBName
End Sub
 
'***** テーブルセット *****
Sub Table_Set()
   Set rs = CreateObject("ADODB.Recordset")
   rs.Open Sql, Db, 3, 3
   Set rs = Db.Execute(Sql)
End Sub
 
'***** データベースクローズ *****
Sub DB_Close()
   rs.Close
   Db.Close
   Set rs = Nothing
   Set Db = Nothing
End Sub
 
'***** 次/前ページボタン不活性化 *****
Sub BttonCTN_off()
      Worksheets("MAIN").BeforePage.Enabled = False
      Worksheets("MAIN").FirstPage.Enabled = False
End Sub
 
'***** 次/前ページボタン活性化 *****
Sub BttonCTN_on()
      Worksheets("MAIN").BeforePage.Enabled = True
      Worksheets("MAIN").FirstPage.Enabled = True
End Sub
 
'***** 先頭/最終ページボタン不活性化 *****
Sub ButtonSE_off()
      Worksheets("MAIN").NextPage.Enabled = False
      Worksheets("MAIN").LastPage.Enabled = False
End Sub
 
'***** 先頭/最終ページボタン活性化 *****
Sub ButtonSE_on()
      Worksheets("MAIN").NextPage.Enabled = True
      Worksheets("MAIN").LastPage.Enabled = True
End Sub

sqlite3test.xlsm : 標準モジュール : SQLiteCtr
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223	`'************************************************************` `' 標準モジュール : SQLiteCtr` `'` `' ●Excelでのデータベース操作 (SQLite3による実験)` `'` `'` `'***********************************************************` `'* グローバル変数 *` `Public` `My_Row` `As` `Long` `' 行位置` `Public` `My_Col` `As` `Long` `' 列位置` `Public` `StarPoint` `As` `Integer` `' レコード開始位置` `Public` `PageNo` `As` `Integer` `' ページ番号` `Public` `PageCount` `As` `Integer` `' ページカウンタ` `Public` `BackPoint` `As` `Integer` `' 前ページ開始位置` `Public` `B_Page` `As` `Integer` `' 前ページ開始位置一時保存` `Public` `B_Num` `As` `Integer` `' 前ページ番号一時保存` `Public` `N_Page` `As` `Integer` `' 次ページ開始位置一時保存` `Public` `N_Num` `As` `Integer` `' 次ページ番号一時保存` `Public` `StartFLG` `As` `Integer` `' 前ページ、次ページ判定フラグ` `Public` `KeepPage` `As` `Integer` `' 最終ページ番号保存用` `Public` `filename` `As` `String` `' DataBase FileName` `Public` `i` `As` `Long` `' セルの表示開始位置制御カウンタ` `Public` `DBName` `As` `String` `' DataBace ODBC定義 (SQLite3)` `Public` `Sql` `As` `String` `' SQL` `Public` `Db` `As` `Object` `' DataBase` `Public` `rs` `As` `Object` `' Recordset` `'* データベース処理開始 (全件表示 : 100件単位) *` `Public` `Sub` `SQLite(ByVal` `StartFLG` `As` `Integer)` `Dim` `S_PageCnt` `As` `Integer` `' 開始ページカウンタ保存用` `Dim` `S_PageNum` `As` `Integer` `' 開始ページ番号保存用` `Dim` `ReadRec` `As` `Integer` `' 読み出しレコードカウンタ` `Dim` `DispRec` `As` `Integer` `' 表示件数制御カウンタ` `Dim` `LastPage` `As` `Integer` `' 最終ページ番号取得用` `'* 前ページをセット *` `If` `StartFLG = 0` `Then` `S_PageCnt = B_Page` `S_PageNum = B_Num` `End` `If` `'* 次ページをセット *` `If` `StartFLG = 1` `Then` `S_PageCnt = N_Page` `S_PageNum = N_Num` `End` `If` `'* 最終ページをセット **` `If` `StartFLG = 3` `Then` `S_PageCnt = (KeepPage PageCount) - PageCount + 1` `S_PageNum = KeepPage` `End` `If` `Sheets("MAIN").Select` `Worksheets("MAIN").Activate` `If` `Range("A5") <>` `""` `Then` `'※表示するデータの項目数によって適時変更のこと` `Range("A5:F110").SpecialCells(xlCellTypeConstants, 23).ClearContents` `End` `If` `'*** DB FileName設定 (※変更のこと) *` `Call` `SQLiteDB_Open("function.db")` `'* テーブル名設定 (※変更のこと) **` `Sql =` `"SELECT FROM TFunction;"` `Call` `Table_Set` `'*** 総レコード件数 *` `RecCount = rs.RecordCount` `Range("F3") = RecCount` `'* 画面の表示更新停止開始 *` `Application.ScreenUpdating =` `False` `'* ページ制御カウンタ (100件/1ページに設定している) *` `StartPoint = S_PageCnt` `PageNo = S_PageNum` `PageCount = 100` `BackPoint = StartPoint - PageCount` `ReadRec = 0` `DispRec = 0` `'* 初期値確認 (StartPoint) *` `If` `StartPoint = 0` `Then` `StartPoint = 1` `PageNo = 1` `Call` `BttonCTN_off` `End` `If` `'* 初期値確認 (PageNo) *` `If` `PageNo = 0` `Then` `PageNo = 1` `Call` `BttonCTN_off` `End` `If` `'* ページ番号を表示 *` `Range("A3") = PageNo` `' 最終ページ番号` `LastPage = (RecCount / PageCount)` `'最終ページ保存` `KeepPage = LastPage` `Range("B3") = PageNo &` `" / "` `& KeepPage` `If` `rs.EOF` `Then` `StartPoint = 0` `PageNo = 0` `PageCount = 0` `BackPoint = 0` `ReadRec = 0` `DispRec = 0` `StartFLG = 0` `' 全制御ボタンを不活性化` `Call` `BttonCTN_off` `Call` `ButtonSE_off` `Else` `'* 結果をセルに書き込む (表示) *` `i = 5` `' 表示開始位置 : 5行目～104行目` `Do` `Until` `rs.EOF =` `True` `' 読み込みレコードカウンタ` `ReadRec = ReadRec + 1` `' データ開始レコードまで読み飛ばし` `If` `StartPoint <= ReadRec` `Then` `' 設定(100件/頁)になったら表示を終了` `If` `DispRec = PageCount` `Then` `Exit` `Do` `End` `If` `' 表示レコードカウンタ` `DispRec = DispRec + 1` `' 結果の表示 (※項目数によって適時変更のこと)` `Cells(i, 1).Value = ReadRec` `Cells(i, 2).Value = rs.Fields(1).Value` `Cells(i, 3).Value = rs.Fields(2).Value` `Cells(i, 4).Value = rs.Fields(3).Value` `Cells(i, 5).Value = rs.Fields(4).Value` `Cells(i, 6).Value = rs.Fields(5).Value` `i = i + 1` `End` `If` `rs.MoveNext` `Loop` `'* 画面の表示更新停止解除 *` `Application.ScreenUpdating =` `True` `'* レコードの開始位置 **` `StartPoint = PageCount PageNo + 1` `'*** ページ制御 *` `' 前ページの場合` `If` `PageNo > 1` `Then` `Call` `BttonCTN_on` `StartFLG = 0` `B_Page = BackPoint` `B_Num = PageNo - 1` `End` `If` `' 次ページの場合` `If` `PageNo < LastPage` `Then` `Call` `ButtonSE_on` `StartFLG = 1` `N_Page = StartPoint` `N_Num = PageNo + 1` `End` `If` `End` `If` `'* 前ページボタン不活性化 *` `If` `PageNo = 1` `Then` `Call` `BttonCTN_off` `End` `If` `'* 次ページボタン不活性化 *` `If` `PageNo >= LastPage` `Then` `Call` `ButtonSE_off` `End` `If` `Range("A1").Select` `'* 接続を閉じる *` `Call` `DB_Close` `End` `Sub` `'* データベースオープン *` `Sub` `SQLiteDB_Open(ByVal` `DBSetName` `As` `String)` `filename = ThisWorkbook.Path &` `"¥"` `& DBSetName` `DBName =` `"DRIVER=SQLite3 ODBC Driver;Database="` `& filename &` `";"` `Set` `Db = CreateObject("ADODB.Connection")` `Db.CursorLocation = 3` `Db.Open DBName` `End` `Sub` `'* テーブルセット *` `Sub` `Table_Set()` `Set` `rs = CreateObject("ADODB.Recordset")` `rs.Open Sql, Db, 3, 3` `Set` `rs = Db.Execute(Sql)` `End` `Sub` `'* データベースクローズ *` `Sub` `DB_Close()` `rs.Close` `Db.Close` `Set` `rs =` `Nothing` `Set` `Db =` `Nothing` `End` `Sub` `'* 次/前ページボタン不活性化 *` `Sub` `BttonCTN_off()` `Worksheets("MAIN").BeforePage.Enabled =` `False` `Worksheets("MAIN").FirstPage.Enabled =` `False` `End` `Sub` `'* 次/前ページボタン活性化 *` `Sub` `BttonCTN_on()` `Worksheets("MAIN").BeforePage.Enabled =` `True` `Worksheets("MAIN").FirstPage.Enabled =` `True` `End` `Sub` `'* 先頭/最終ページボタン不活性化 *` `Sub` `ButtonSE_off()` `Worksheets("MAIN").NextPage.Enabled =` `False` `Worksheets("MAIN").LastPage.Enabled =` `False` `End` `Sub` `'* 先頭/最終ページボタン活性化 ***` `Sub` `ButtonSE_on()` `Worksheets("MAIN").NextPage.Enabled =` `True` `Worksheets("MAIN").LastPage.Enabled =` `True` `End` `Sub`

【参考補足事項】です。

今回のサンプルでは、検索・抽出部分のコードは省略していますが、機能の実装や追加の方法がわからないという人のために筆者が実際に利用している作成済みの実例を元に参考程度ですが少し説明します。これまで使用したサンプルとはデータベースファイルは異なりますが、ソースコードは、上記リストのsqlite3test.xlsmに追加しているだけですので理解はしやすいと思います。
実例として説明するサンプルは、競馬のレース結果のデータベースです。20万件程度を収集して蓄積しています。実際、筆者は競馬はやっていません。統計的なデータ分析をする教材としてデータ量が豊富なためデータを蓄積しています。

下記の画面は、競馬場、距離、コースを条件にレース結果を検索・抽出をする画面の一部です。検索条件部分は、プルダウンで条件を選択します。

下記の画面は、データベーステーブルの内容の一部です。Kaisaiとkyoriのフィールドから複合条件のSQL文を発行して抽出させています。

起動時ブックが開いた時に設置しているコンボボックスに値を設定します。

コンボボックスに起動時値を設定'(ThisWorkbook)
'***** ブックを開いた時の初期設定処理 *****
Private Sub Workbook_Open()
     
    Sheets("レース基本").Select
    Worksheets("レース基本").Activate
 
    '***** 検索条件用のコンボボックスを設定 *****
    ' 競馬場
    Dim CBRaceKaisai(10)
        CBRaceKaisai(0) = "東京"
        CBRaceKaisai(1) = "中山"
        CBRaceKaisai(2) = "京都"
        CBRaceKaisai(3) = "阪神"
        CBRaceKaisai(4) = "中京"
        CBRaceKaisai(5) = "札幌"
        CBRaceKaisai(6) = "函館"
        CBRaceKaisai(7) = "福島"
        CBRaceKaisai(8) = "新潟"
        CBRaceKaisai(9) = "小倉"
    ' 距離
    Dim CBKyori(20)
        CBKyori(0) = "1000"
        CBKyori(1) = "1150"
        CBKyori(2) = "1200"
        CBKyori(3) = "1300"
        CBKyori(4) = "1400"
        CBKyori(5) = "1500"
        CBKyori(6) = "1600"
        CBKyori(7) = "1700"
        CBKyori(8) = "1800"
        CBKyori(9) = "2000"
        CBKyori(10) = "2100"
        CBKyori(11) = "2200"
        CBKyori(12) = "2300"
        CBKyori(13) = "2400"
        CBKyori(14) = "2500"
        CBKyori(15) = "2600"
        CBKyori(16) = "3000"
        CBKyori(17) = "3200"
        CBKyori(18) = "3400"
        CBKyori(19) = "3600"
 
    ' コース
    Dim CBCource(3)
        CBCource(0) = "ダート"
        CBCource(1) = "芝"
        CBCource(2) = "障害"
 
    ' 各コンボボックスに設定値をセット
    Worksheets("レース基本").KaisaiComb.List = CBRaceKaisai
    Worksheets("レース基本").KyoriComb.List = CBKyori
    Worksheets("レース基本").CourceComb.List = CBCource
 
End Sub

コンボボックスに起動時値を設定
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55	`'(ThisWorkbook)` `'*** ブックを開いた時の初期設定処理 *` `Private` `Sub` `Workbook_Open()` `Sheets("レース基本").Select` `Worksheets("レース基本").Activate` `'* 検索条件用のコンボボックスを設定 ***` `' 競馬場` `Dim` `CBRaceKaisai(10)` `CBRaceKaisai(0) =` `"東京"` `CBRaceKaisai(1) =` `"中山"` `CBRaceKaisai(2) =` `"京都"` `CBRaceKaisai(3) =` `"阪神"` `CBRaceKaisai(4) =` `"中京"` `CBRaceKaisai(5) =` `"札幌"` `CBRaceKaisai(6) =` `"函館"` `CBRaceKaisai(7) =` `"福島"` `CBRaceKaisai(8) =` `"新潟"` `CBRaceKaisai(9) =` `"小倉"` `' 距離` `Dim` `CBKyori(20)` `CBKyori(0) =` `"1000"` `CBKyori(1) =` `"1150"` `CBKyori(2) =` `"1200"` `CBKyori(3) =` `"1300"` `CBKyori(4) =` `"1400"` `CBKyori(5) =` `"1500"` `CBKyori(6) =` `"1600"` `CBKyori(7) =` `"1700"` `CBKyori(8) =` `"1800"` `CBKyori(9) =` `"2000"` `CBKyori(10) =` `"2100"` `CBKyori(11) =` `"2200"` `CBKyori(12) =` `"2300"` `CBKyori(13) =` `"2400"` `CBKyori(14) =` `"2500"` `CBKyori(15) =` `"2600"` `CBKyori(16) =` `"3000"` `CBKyori(17) =` `"3200"` `CBKyori(18) =` `"3400"` `CBKyori(19) =` `"3600"` `' コース` `Dim` `CBCource(3)` `CBCource(0) =` `"ダート"` `CBCource(1) =` `"芝"` `CBCource(2) =` `"障害"` `' 各コンボボックスに設定値をセット` `Worksheets("レース基本").KaisaiComb.List = CBRaceKaisai` `Worksheets("レース基本").KyoriComb.List = CBKyori` `Worksheets("レース基本").CourceComb.List = CBCource` `End` `Sub`

sqlite3test.xlsmの標準モジュールにコンボボックスで選択した条件の値のセットと検索部分のSELECT文です。 COLLATE NOCASEは、sqlite3では大文字と小文字が区別されるので、指定しています。また、最大5,000件でリミットも指定しています。結果の表示部分は、シートに表示したい部分のテーブルのフィールドを必要分追加します。 SQLについては、解説しているサイトも多いので、調べて見て下さい。

標準モジュール' コンボボックスの各設定値をセット
Kaisai = Worksheets("レース基本").KaisaiComb.Text
cource = Left(Worksheets("レース基本").CourceComb.Text, 1)
sKyori = Worksheets("レース基本").KyoriComb.Text
 
' SQL文を発行
Sql = "SELECT * FROM jraracedb WHERE Kaisai COLLATE NOCASE LIKE '%" & Kaisai & "%'" _
      & " AND Kyori COLLATE NOCASE LIKE " & "'" & cource & "%'" _
      & " AND Kyori COLLATE NOCASE LIKE " & "'%" & sKyori & "%'" _
      & " ORDER BY ID ASC LIMIT 5000"
 
         ～途中省略～
 
         ' 結果の表示
         Cells(i, 1).Value = ReadRec
         Cells(i, 2).Value = rs.Fields(1).Value
         Cells(i, 3).Value = rs.Fields(2).Value
         Cells(i, 4).Value = rs.Fields(3).Value
         Cells(i, 5).Value = rs.Fields(4).Value
         Cells(i, 6).Value = rs.Fields(5).Value
          
         TKyori = Mid(rs.Fields(7).Value, 2, 4)
         If Left(rs.Fields(7).Value, 1) = "ダ" Then
            TCource = "ダート"
         Else
         If Left(rs.Fields(7).Value, 1) = "障" Then
            TCource = "障害"
         Else
            TCource = Left(rs.Fields(7).Value, 1)
         End If
         End If
          
         Cells(i, 7).Value = TKyori
         Cells(i, 8).Value = TCource
          
         Cells(i, 9).Value = rs.Fields(8).Value
         Cells(i, 10).Value = rs.Fields(9).Value
         Cells(i, 11).Value = rs.Fields(10).Value
         Cells(i, 12).Value = rs.Fields(11).Value
         Cells(i, 13).Value = rs.Fields(12).Value
         Cells(i, 14).Value = rs.Fields(13).Value
         Cells(i, 15).Value = rs.Fields(14).Value
         Cells(i, 16).Value = rs.Fields(15).Value
         Cells(i, 17).Value = rs.Fields(16).Value

標準モジュール
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44	`' コンボボックスの各設定値をセット` `Kaisai = Worksheets("レース基本").KaisaiComb.Text` `cource = Left(Worksheets("レース基本").CourceComb.Text, 1)` `sKyori = Worksheets("レース基本").KyoriComb.Text` `' SQL文を発行` `Sql =` `"SELECT * FROM jraracedb WHERE Kaisai COLLATE NOCASE LIKE '%"` `& Kaisai &` `"%'"` `_` `&` `" AND Kyori COLLATE NOCASE LIKE "` `&` `"'"` `& cource &` `"%'"` `_` `&` `" AND Kyori COLLATE NOCASE LIKE "` `&` `"'%"` `& sKyori &` `"%'"` `_` `&` `" ORDER BY ID ASC LIMIT 5000"` `～途中省略～` `' 結果の表示` `Cells(i, 1).Value = ReadRec` `Cells(i, 2).Value = rs.Fields(1).Value` `Cells(i, 3).Value = rs.Fields(2).Value` `Cells(i, 4).Value = rs.Fields(3).Value` `Cells(i, 5).Value = rs.Fields(4).Value` `Cells(i, 6).Value = rs.Fields(5).Value` `TKyori = Mid(rs.Fields(7).Value, 2, 4)` `If` `Left(rs.Fields(7).Value, 1) =` `"ダ"` `Then` `TCource =` `"ダート"` `Else` `If` `Left(rs.Fields(7).Value, 1) =` `"障"` `Then` `TCource =` `"障害"` `Else` `TCource = Left(rs.Fields(7).Value, 1)` `End` `If` `End` `If` `Cells(i, 7).Value = TKyori` `Cells(i, 8).Value = TCource` `Cells(i, 9).Value = rs.Fields(8).Value` `Cells(i, 10).Value = rs.Fields(9).Value` `Cells(i, 11).Value = rs.Fields(10).Value` `Cells(i, 12).Value = rs.Fields(11).Value` `Cells(i, 13).Value = rs.Fields(12).Value` `Cells(i, 14).Value = rs.Fields(13).Value` `Cells(i, 15).Value = rs.Fields(14).Value` `Cells(i, 16).Value = rs.Fields(15).Value` `Cells(i, 17).Value = rs.Fields(16).Value`

以上、長々とダラダラとした説明に付き合って下さり、ありがとうございます。

4.ダウンロード

掲載しているソースコードのライセンスは、CC0 (クレジット表示不要、改変可、商用可) とします。自由に利用して頂いてかまいません。筆者の限られた環境での動作確認はしていますが、ご利用環境によっては、掲載リストやダウンロード提供するサンプルが動作しないことも考えられます。動作時のトラブルやサンプルを実行して万一損害等が発生しても筆者は一切、責任を負いません。 全て自己責任でお願いします。

掲載している動作確認サンプル等をダウンロードサービス致します。必要であればダウンロードして参考として頂けば幸いです。

ダウンロード

No.	記事内デモコンテンツ一覧
1	気象庁震度観測点マップ
2	都道府県別人口統計マップ
3	都道府県庁所在地一覧
4	全国地方自治体所在地 (北海道)
5	全国地方自治体所在地 (東北)
6	全国地方自治体所在地 (関東・甲信)
7	全国地方自治体所在地 (北陸)
8	全国地方自治体所在地 (東海)
9	全国地方自治体所在地 (関西)
10	全国地方自治体所在地 (中国)
11	全国地方自治体所在地 (四国)
12	全国地方自治体所在地 (九州)
13	全国地方自治体所在地 (沖縄)
14	東海道五十三次浮世絵マップ
15	東海道五十三次宿場の概要
16	東海道五十三次距離換算表
17	富嶽三十六景浮世絵マップ
18	四国遍路巡礼マップ
19	日本鳥類目録分類図 (D3.js)-[1]
20	日本鳥類目録分類図 (D3.js)-[2]
21	日本鳥類目録分類図 (HTML)[全体]
22	日本哺乳類目録分類図 (D3.js)-[1]
23	日本哺乳類目録分類図 (D3.js)-[2]
24	日本哺乳類目録分類図 (HTML)[全体]
25	日本海産魚類目録分類図 (D3.js)-[1]
26	日本海産魚類目録分類図 (D3.js)-[2]
27	日本海産魚類目録分類図 (D3.js)-[3]
28	日本海産魚類目録分類図 (D3.js)-[4]
29	日本海産魚類目録分類図 (HTML)[全体]
30	地震発生履歴統計データ
31	震度別地震発生履歴統計データ
32	キーボードのキーコード表
33	伝統色(近似色)の色見本コード一覧表
34	Google Chartsグラフサンプル
35	日本の各種統計データの可視化(1)
36	日本の各種統計データの可視化(2)
37	日本の各種統計データの可視化(3)
38	日本の各種統計データの可視化(4)

このブログを検索

グレおやぢの日常

Excelでデータベース(SQLite3)を扱う

コメント

コメントを投稿

このブログの人気の投稿

Excelアドインで日本語形態素解析

階層構造JSONファイルの作成

キーボードのキーコードの一覧表