Excel VBA シートをSQLで読みこんで結果を配列で返すFunction

同じディレクトリ上のエクセルファイルのシート

商品マスタ.xlsxのテーブル[tbl_item]
item_id item_name item_unit_price
1 商品1 ¥4,000
2 商品2 ¥3,500
3 商品3 ¥3,300

Module

'エクセルのシートをSQLで検索し結果を受取り結果を多次元配列で返す
Function getItemMst(getSQL As String) As Variant
    Dim cn As ADODB.Connection
    Dim cnStr As String
    Dim rs As ADODB.Recordset
    
    Dim FileName As String
    Dim FilePath As String
    Dim strSQL As String
    
    FileName = "商品マスタ.xlsx"
    FilePath = ThisWorkbook.Path & "\" & FileName
    
    'Connection
    Set cn = New ADODB.Connection
    Set rs = New ADODB.Recordset
    
    cn.Open "Provider = Microsoft.ACE.OLEDB.12.0;" & _
            "Data source=" & FilePath & ";" & _
            "Extended Properties=""Excel 12.0;"";"
    
    rs.Source = getSQL
    rs.ActiveConnection = cn
    rs.Open
    
    'レコードセットを配列に入れる(GetRows)
    'さらに配列の行列を入れ替える(Transpose)
    getItemMst = WorksheetFunction.Transpose(rs.GetRows)
    
   
    rs.Close
    Set rs = Nothing
    cn.Close
    Set cn = Nothing

End Function

このSheetが表示された時に読み込まれる

Private Sub Worksheet_Activate()
    Dim recSet As Variant
    
    Dim strSQL As String
    strSQL = "SELECT item_id,item_name,item_unit_price FROM [tbl_item$]"
    
    recSet = getItemMst(strSQL)
    
    Dim sheetRow As Integer
    Dim sheetcol As Integer
    
    Dim x As Long, y As Long


    Range("D3:G20") = recSet


End Sub

コメントを残す

メールアドレスが公開されることはありません。