【VBA】SQL Serverのデータを取得してシートへ設定する【列名も設定】

VBAコード

ここでは例として、
　・SQL Server上のDB「sampleDB」の
　・テーブル「m_product」のデータを取得して
　・新規作成したシート「data」へ設定
します。

Option Explicit

Sub sample()

    'プロバイダ
    Const PROVIDER As String = "MSOLEDBSQL"
    'サーバー名
    Const SERVER_NAME As String = "localhost\SQLEXPRESS"
    'DB名
    Const DB_NAME As String = "sampleDB"
    'ユーザー名
    Const USER_NAME As String = "XXXXX"
    'パスワード
    Const PASSWORD As String = "XXXXX"
    
    Dim dataSheeName As String
    Dim sql As String
    Dim sheet As Worksheet
    Dim dataSheet As Worksheet
    Dim conStr As String
    Dim n As Name
    
    'SELECT文の結果を設定するシート名
    dataSheeName = "data"
    '実行するSELECT文
    sql = "SELECT * FROM m_product"
    
    '既にシート「date」が存在する場合は削除
    For Each sheet In ThisWorkbook.Worksheets
        If sheet.Name = dataSheeName Then
            '確認メッセージを非表示
            Application.DisplayAlerts = False
            'シート削除
            Worksheets(dataSheeName).Delete
            '確認メッセージを表示
            Application.DisplayAlerts = True
        End If
    Next

    'シート「date」を新規作成
    Set dataSheet = Worksheets.Add(After:=Worksheets(Worksheets.Count))
    dataSheet.Name = dataSheeName
    
    '接続文字列の組み立て(Windows認証)
'    conStr = "Provider=" & PROVIDER & ";" & _
'              "Data Source=" & SERVER_NAME & ";" & _
'              "Initial Catalog=" & DB_NAME & ";" & _
'              "Integrated Security=SSPI;" & _
'              "DataTypeCompatibility=80;"
              
    '接続文字列の組み立て(SQL Server認証)
    conStr = "Provider=" & PROVIDER & ";" & _
              "Data Source=" & SERVER_NAME & ";" & _
              "Initial Catalog=" & DB_NAME & ";" & _
              "User ID=" & USER_NAME & ";" & _
              "Password=" & PASSWORD & ";" & _
              "DataTypeCompatibility=80;"
              
    
    '「QueryTableオブジェクト(＝クエリと接続)」を作成
    With dataSheet.QueryTables.Add(Connection:="OLEDB;" & conStr, _
                                              Destination:=Range("B2"), _
                                              sql:=sql)
        'SELECT文を実行
        .Refresh BackgroundQuery:=False
        '名前(後続で削除できるように名前を設定)
        .Name = "仮テーブル"
        '作成された「QueryTableオブジェクト(＝クエリと接続)」を削除
        .Delete
    End With
    
    '上記で作成されてしまう名前定義(仮テーブル)を削除
    For Each n In ActiveWorkbook.Names
        If n.Name = dataSheeName & "!" & "仮テーブル" Then
            n.Delete '
        End If
    Next
    
End Sub

上記のVBAコードに記載の「接続文字列」の詳細は、以下のWebサイトの記事をご確認ください。

実行結果

SQL Serverのデータを取得してシートへ設定できました。
※列名も取得できました。

参考①

シート上の表(一連のセル範囲)をテーブル化することで、
　・スタイルの設定
　・テーブル化した範囲の名前定義
ができます。

詳細は以下の記事をご確認ください。

参考②

VBAからではなく、手動でも上記と同等のことを実施できます。

詳細は以下のWebサイトの記事をご確認ください。

参考③

列名を取得しない方法もあります。

詳細は以下の記事をご確認ください。

参考④

テーブルのレコードをCSVファイルへ出力することもできます。

詳細は以下の記事をご確認ください。

参考⑤

上記で使用した以下の詳細は、公式サイトをご確認ください。

●「QueryTables.Add」

●「QueryTableオブジェクト(＝クエリと接続)」