この記事では、Excelのマクロを使ってSQLiteのデータベースにあるテーブル名を全て取得する方法についてご説明します。
【動画】Excelのマクロを使ってSQLiteのデータベースにあるテーブル名を全て取得する実際の動き
本題に入る前に、まずは次の動画をご覧ください。
Connectionインスタンスの生成してSQLiteのデータベースへの接続情報を用意したらConnectionインスタンスのConnectionStringプロパティに代入し、Openメソッドを実行することで、マクロがSQLiteのデータベースに接続しています。
マクロがSQLiteのデータベースに接続したら「sqlite_master」のテーブルに対して、接続したデータベース名(「B2」のセルに入力されたデータベース名)にあるテーブルを全て取得します。
マクロ作成の流れ
実行すると、ExcelのマクロがSQLiteのデータベースに接続されます。
ExcelファイルとSQLiteのデータベースの例
今回は次のExcelファイルを用意しました。
B2のセルに、検索先のデータベースファイルのフルパスを入力して検索ボタンを押すと、データベースにある全てのテーブル名がD列のピンク色のセルに出力されます。
データベースファイル「sqlite_DB001.db」にあるテーブルを確認すると、D列のピンク色のセルに出力されたテーブルがデータベース「sqlite_DB001.db」にあることが確認できます。
もしデータベースにテーブルが存在しない場合は下のように「テーブルは存在しません」とメッセージが表示されます。
コードの例
Option Explicit Private Sub btn_exec_Click() Dim ws As Worksheet 'ワークシート変数 Dim cnt As Long 'カウンタ Dim cn As ADODB.Connection 'Connection用変数 Dim rs As ADODB.Recordset 'レコードセット用変数 Dim sqlStr As String 'SQL用変数 'シートを取得する Set ws = Worksheets("work") 'カウンタを初期化する cnt = 5 'セルをクリアする ws.Range("B5:B10").ClearContents 'Connectionオブジェクトのインスタンスを生成する Set cn = New ADODB.Connection 'SQLiteへの接続情報を取得する cn.ConnectionString = "DRIVER=SQLite3 ODBC Driver;Database=" & _ ws.Range("dbName").Value 'コネクションを開く cn.Open 'Recordsetオブジェクトのインスタンスを生成する Set rs = New ADODB.Recordset 'SQLクエリを実行して、テーブル名を取得する sqlStr = "SELECT name" sqlStr = sqlStr & " FROM sqlite_master" sqlStr = sqlStr & " WHERE type='table';" 'SELECT文を実行してRecordsetを開く rs.Open sqlStr, cn, adOpenStatic If rs.EOF Then 'テーブルが存在しない場合 MsgBox "テーブルは存在しません" Else 'テーブルが存在する場合 Do Until rs.EOF 'テーブル名をセルに出力する Worksheets("work").Range("B" & cnt).Value = rs.Fields(0).Value 'Recordsetのレコードのカーソルを次に移動する rs.MoveNext cnt = cnt + 1 DoEvents Loop End If '後処理 cn.Close Set cn = Nothing Set rs = Nothing End Sub
注目すべきコード①
最初に見て頂きたいのは21行目です。
'Connectionインスタンスの生成 Set cn = New ADODB.Connection
コードの説明
以上のコードは、マクロがSQLiteのデータベースに接続するための接続情報を取得して接続するコードです。
このインスタンスがないとマクロがSQLiteのデータベースに接続することができないので必ず生成しておきます。
注目すべきコード②
次に見て頂きたいのは24行目から25行目です。
'SQLiteへの接続情報を取得する cn.ConnectionString = "DRIVER=SQLite3 ODBC Driver;Database=" & _ ws.Range("dbName").Value
コードの説明
以上のコードは、マクロがSQLiteのデータベースに接続するための接続情報を用意するコードです。
接続情報のDriverには、マクロがSQLiteのデータベースに接続するのに必要なODBCドライバーの情報を指定します。
「SQLite3 ODBC Driver」は下のODBC データソースアドミニストレーター上で表示されているSQLiteのドライバの名前を指定します。
また、SQLiteのデータベースファイルのフルパスも指定します。
今回はB2のセル(今回はセルの名前を「dbName」としています)を接続情報で指定しています。
注目すべきコード③
次に見て頂きたいのは28行目です。
'コネクションを開く cn.Open
コードの説明
以上のコードは、ConnectionインスタンスのOpenメソッドを実行して、マクロがSQLiteのデータベースに接続する処理のコードです。
Connectionインスタンスには「注目すべきコード②」で説明した通り、マクロがSQLiteのデータベースに接続するための接続情報をConnectionStringプロパティに代入しているので、ここではOpenメソッドを実行することでマクロがSQLiteのデータベースに接続されます。
注目すべきコード④
次に見て頂きたいのは31行目から39行目です。
'Recordsetオブジェクトのインスタンスを生成する Set rs = New ADODB.Recordset 'SQLクエリを実行して、テーブル名を取得する sqlStr = "SELECT name" sqlStr = sqlStr & " FROM sqlite_master" sqlStr = sqlStr & " WHERE type='table';" 'SELECT文を実行してRecordsetを開く rs.Open sqlStr, cn, adOpenStatic
コードの説明
以上のコードは、Recordsetオブジェクトのインスタンスを生成した後にデータベース名を取得するSELECT文を用意してそのSELECT文を実行する処理のコードです。
コードの詳細
31行目のコードでは、Recordsetオブジェクトのインスタンスを生成しています。
Recordsetオブジェクトのインスタンスを生成することで、マクロがSQLのSELECT文を実行したり、SELECT文を実行した後にデータを取得することができるようになります。
34行目から26行目では、SQLiteのデータベースにあるテーブル名を全て取得するSELECT文を用意しています。
SQLiteのデータベースにあるテーブル名を全て取得する取得先のテーブルは「sqlite_master」のテーブルです。(35行目で指定)
検索するテーブル名は、「sqlite_master」のテーブルにあるフィールド「name」にあるので、WHERE句にしているフィールド名にtypeを、条件値に「table」を指定しています。(36行目で指定)
ちなみに、34行目から36行目のコードだけではSELECT文が分かりにくいと思うので、実際に実行するSELECT文を以下にお見せします。
SELECT name FROM sqlite_master WHERE type = 'table';
39行目のコードでは、SELECT文をOpenメソッドの引数に指定して実行することで、SELECT文が実行されます。
注目すべきコード⑤
次に見て頂きたいのは44行目から52行目です。
If rs.EOF Then 'テーブルが存在しない場合 MsgBox "テーブルは存在しません" Else 'テーブルが存在する場合
以上のコードでは、recordsetに格納されているデータが存在しているかを判定している処理のコードです。
もしデータが無い場合は45行目でテーブルが存在していない旨のメッセージを出力します。
データがある場合は52行目のELSEに該当し、ELSE内で「注目すべきコード⑥」の処理を行います。
注目すべきコード⑥
次に見て頂きたいのは56行目から68行目です。
Do Until rs.EOF 'データベース名をセルに出力する Worksheets("work").Range("D" & cnt).Value = rs.Fields(0).Value 'Recordsetのレコードのカーソルを次に移動する rs.MoveNext cnt = cnt + 1 DoEvents Loop
コードの説明
以上のコードは、「sqlite_master」のテーブルから取得したテーブル名をExcelのセルに出力する処理のコードです。
コードの詳細
51行目のコードでは、recordsetに格納されたデータ(今回はテーブル名)の数分ループ処理を行うDo文で、例えばデータ件数が3件ある場合はループを3回繰り返します。
54行目のコードでは、recordsetに格納されたテーブルの名称をB列のセルに出力します。
57行目のコードでは、recordsetに格納された次のデータを参照するためMoveNextメソッドを実行しています。
例えば1つ目のデータを参照した後に2つ目のデータを参照するにはこのMoveNextを実行しないといけません。
MoveNextを実行しないと永久に1つ目のデータを参照し続けます。
なので、2つ目、3つ目・・・と順々にデータを参照するためには忘れずにMoveNextメソッドを実行します。
動作確認
「ExcelファイルとSQLiteのデータベースの例」をご覧ください。
【注意】参照設定が必要です
一つ注意点があるのですが、先ほどのコードを動かすには参照設定が必要です。
参照設定の一覧(下の画像を参考)から次の項目(ライブラリ)にチェックを付けて「OK」ボタンをクリックします。
- Microsoft ActiveX Data Objects 2.8 Library(msado28.tlb)
なぜ必要かというと、Excelのマクロのコードの7行目の「ADODB.Connection」と8行目の「ADODB.Recordset」というオブジェクトが「msado28.tlb」というファイルを参照するからです。
Dim cn As ADODB.Connection 'Connection用変数 Dim rs As ADODB.Recordset 'レコードセット用変数
この参照設定をしないと下の画像のエラーが出ますので必ず行う必要があります。
ここでは「msado28.tlb」とは何者かについては記事の本題から逸れてしまうので詳細は割愛しますが、マクロで「ADODB.Connection」「ADODB.Recordset」というオブジェクトを使う場合は参照設定しないと動かない、程度に思って頂ければと思います。
最後に
この記事では、Excelのマクロを使ってSQLiteのデータベースにあるテーブル名を全て取得する方法についてご説明します。
Excelのマクロを使ってSQLiteのデータベースにあるテーブル名を全て取得したいときは本記事を参考にしてみてくださいね。
プログラミングのスキルを習得するなら
プログラミングのスキルを習得したい、今のスキルをもっと高めたい、そう考えているなら「プログラミングスクール」がおすすめです。
プログラミングのスキルの基礎を身につけるなら「TechAcademy」で1週間の無料体験があるので、これで「プログラミングの基礎」を学ぶのにおすすめですよ。