【ExcelVBA】Excelのマクロを使ってSQLiteのデータベースにあるテーブル名を全て取得するには

7分

SHARE

この記事では、Excelのマクロを使ってSQLiteのデータベースにあるテーブル名を全て取得する方法についてご説明します。

すぐにソースコードを見たい場合はこちらをクリック

【動画】Excelのマクロを使ってSQLiteのデータベースにあるテーブル名を全て取得する実際の動き

本題に入る前に、まずは次の動画をご覧ください。


Connectionインスタンスの生成してSQLiteのデータベースへの接続情報を用意したらConnectionインスタンスのConnectionStringプロパティに代入し、Openメソッドを実行することで、マクロがSQLiteのデータベースに接続しています。

マクロがSQLiteのデータベースに接続したら「sqlite_master」のテーブルに対して、接続したデータベース名(「B2」のセルに入力されたデータベース名)にあるテーブルを全て取得します。

マクロ作成の流れ

STEP.1
Connectionインスタンスを生成する
Connectionインスタンスを生成します。
STEP.2
SQLiteへの接続情報を取得する
SQLiteへの接続情報を取得します。
STEP.3
STEP.2の接続情報をOpenメソッドの引数に指定してOpenメソッドを実行する
STEP.2の接続情報をOpenメソッドの引数に指定してOpenメソッドを実行します。
実行すると、ExcelのマクロがSQLiteのデータベースに接続されます。
STEP.4
「sqlite_master」のテーブルに対して、検索する先のデータベース名にあるテーブルを全て取得する
「sqlite_master」のテーブルに対して、検索する先のデータベース名にあるテーブルを全て取得します。
目次へ戻る

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文(データベースにあるテーブルをすべて取得する)
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」ボタンをクリックします。

  1. 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週間の無料体験があるので、これで「プログラミングの基礎」を学ぶのにおすすめですよ。

→　TechAcademyの「1週間 無料体験」はこちら



目次へ戻る

前の記事

【C#】C#からAccessのテーブルデータをdataGrid…

次の記事

【ExcelVBA】Excelのマクロを使ってAccessのフ…