VBAでExcelシートデータをSQLで取得する方法

はじめに

　Excelのシート範囲を指定して、その中のデータをSQLを使って取得する方法を知ったので備忘録として載せます。

サンプルコード

下記はExcelシートでSQLを使用して値を取得し、別シートにデータを出力するサンプルコードです。

Sub GetExcelDataWithSQL_1()
    Dim cn As Object
    Dim rs As Object
    Dim strSQL As String
    Dim strConnection As String
    Dim ws As Worksheet

    ' ADO Connectionオブジェクトを作成
    Set cn = CreateObject("ADODB.Connection")
    
    ' 接続文字列を設定
    strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.FullName & ";Extended Properties=""Excel 12.0;HDR=Yes;IMEX=1"";"
    
    ' 接続を開く
    cn.Open strConnection
    
    ' SQLクエリの作成
    strSQL = "SELECT * FROM [取得$A1:C10]"  ' シート名と範囲を指定
    
    ' ADO Recordsetオブジェクトを作成してSQLクエリを実行
    Set rs = CreateObject("ADODB.Recordset")
    rs.Open strSQL, cn, 1, 1 ' CursorTypeとLockTypeを指定（1=adOpenKeyset, 1=adLockReadOnly）

    ' 結果をシートに出力
    Set ws = ThisWorkbook.Sheets.Add
    ws.Name = "SQL Results１"
    ws.Cells(1, 1).CopyFromRecordset rs

    ' クリーンアップ
    rs.Close
    cn.Close
    Set rs = Nothing
    Set cn = Nothing
End Sub

解説

• 接続文字列
  　通常のSQLと同じで、下記の文字列を使い、接続したいExcelファイルを指定します。

' 接続文字列を設定
    strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.FullName & ";Extended Properties=""Excel 12.0;HDR=Yes;IMEX=1"";"

• SQL文
  下記のSQL文で通常と違うのが、テーブル名が"[シート名$データ範囲]"となっており、この範囲がDBで言うテーブルの部分になります。

' SQLクエリの作成
    strSQL = "SELECT * FROM [取得$A1:C10]"  ' シート名と範囲を指定

あとは通常のDB操作と同じ要領でRecordsetオブジェクトに値を受け取り、出力などの操作後、接続を閉じるだけです。