【VBA/EXCEL】EXCELでSQLエディタの実装（SELECT命令編）

VBA

はじめに

  • EXCEL（VBA）でSQLを使いたい。
  • 開発中はテスト用SQLも気軽に使いたい。
  • SQLエディタのような機能を実装しよう！

SQLエディタの実装（SELECT命令編）

参考 excel-ubara.com qiita.com

  • VB系環境では、SQLはADO（ActiveX Data Objects）を使用します。
  • 「ツール」→「参照設定」からMicrosoft ActiveX Data Objects X.X Libraryを追加する必要があります。 X.Xは2.7などバージョンになるので、環境に合わせて選択します。
  • これにより、使用できるデータベースは


今回は、SELECT命令のみ実装していきます。


実装

※本記事では、SQLインジェクション等のセキュリティ対策は考慮していません。


  • EXCELシートの構成
    • SQL：実行したいSQLを記述及び操作
    • result：実行結果出力
    • tblTest：テスト用テーブル


  • VBAモジュールの構成
    • Com：共通の設定
    • Init：DB接続関連の処理
    • SqlBrowser：主処理部
    • Sql_select：SQL実行部


まず、プロバイダを設定します。

本記事の範囲ではモジュールを分ける必要は特にないですが、今後の拡張を考慮して、

今回はモジュール名：Comを作成し、ここに記述します。

'--DB接続
Public Const Provider As String = "MSDASQL"
Public ConnectionString As String


次に、モジュール名：Initを作成し、DB接続設定を進めます。

今回はEXCELの同Book内のシートをデータベースとして運用していきます。

'--初期処理
Sub initDB()
    Com.File_Name = ThisWorkbook.FullName
    Com.ConnectionString = "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};" & "DBQ=" & Com.File_Name & "; ReadOnly=True;"
End Sub


テスト用SQLを実行する処理は以下のとおりです。

SQLの記述にはシート名：SQL、実行結果の出力先にはシート名：resultとしています。

主処理部はモジュール名：SqlBrowserに作成し、以下のサブプロを記述します。

Sub SqlBrowser()

    Dim thisSheet As String
    thisSheet = "SQL"

    Dim outSheet As String
    outSheet = "result"
        
    Dim row As Long
    Dim col As Long
    row = 1
    col = 1

    '--一覧クリア（とりあえず空欄が10000行分見つかるまでクリアしていく）
    Dim i As Long
    i = 0
    Do While i <= 10000
        If Worksheets(outSheet).Cells(row + i, col).Value = "" Then
            i = i + 1
        End If
        Worksheets(outSheet).Rows(row + i).ClearContents '行クリア
    Loop
    
    '--DBオブジェクト作成
    Call init.initDB
    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Set cn = New ADODB.Connection
    Set rs = New ADODB.Recordset
    
    Dim sql As String
    
    '--SQL文を格納
    sql = Worksheets(thisSheet).Cells(1, 2).Value
    
    '--SQL実行
    Call Sql_select.Sql_select(cn, rs, sql)

    '--SQLの結果を出力
    Dim fld As ADODB.Field
    Dim j As Long
    j = 0

    '--列名表示
    For Each fld In rs.Fields

        If IsNull(fld.Name) Then
            Worksheets(outSheet).Cells(row, col + j).Value = "null"
        Else
            Worksheets(outSheet).Cells(row, col + j).Value = fld.Name
        End If
            
        j = j + 1
    Next fld
    
    i = 1
    j = 0

    '--レコード出力
    Do Until rs.EOF
    
        Dim f As Long
        For f = 0 To (rs.Fields.Count - 1)

            If IsNull(rs.Fields(f)) Then
                Worksheets(outSheet).Cells(row + i, col + f).Value = ""
            Else
                Worksheets(outSheet).Cells(row + i, col + f).Value = rs.Fields(f)
            End If

        Next f
        
        i = i + 1
        
        rs.MoveNext
    Loop

    rs.Close
    cn.Close
    Set rs = Nothing
    Set cn = Nothing

End Sub


主処理部からSQL実行部を呼び出します。

モジュール名：Sql_selectを作成し、以下のサブプロを記述します。

Sub Sql_select(cn As ADODB.Connection, _
                rs As ADODB.Recordset, _
                sql As String)

    cn.Provider = Com.Provider
    cn.ConnectionString = Com.ConnectionString
    cn.Open

    rs.Open sql, cn, adOpenStatic

End Sub


実行結果

テスト用のテーブルは以下のシート名：tblTestを作成し、内容は以下の通り。 f:id:indonegiyaland:20220409221152p:plain


また、SQLエディタはシート名：SQLを作成し、以下の通り。 f:id:indonegiyaland:20220409221500p:plain


今回テスト用に実行するSQLは以下の通り。

select * 
  from [tblTest$]
where id >= 200


実行ボタンを押すと、シート名：resultに列名付きで出力します。

この例では、idが200以上のレコードを抽出し出力されているはずです。 f:id:indonegiyaland:20220409221743p:plain

日付データとかの扱いや表示が課題かなあ...

おわり

  • 今回はSELECT命令のみ実装しました。
  • INSERTやUPDATE、DELETEなども使えるようにしていく予定です。
  • あとSQLインジェクション等のセキュリティ対策は考慮していません。