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

例えば
 ・SQL Server上の
 ・テーブル「m_product」のデータを取得
して、

SQL Server上のテーブル「m_product」のデータ
SQL Server上のテーブル「m_product」のデータ


シート上へ設定できます!
※列名も含む。

実行結果
実行結果


ここではVBAから
 ・「QueryTableオブジェクト(=クエリと接続)」
を使用して取得する方法を記載します!

クエリと接続
クエリと接続
PR

VBAコード

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

SQL Serverの環境情報
SQL Serverの環境情報
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

以下を設定します(6~14行目)。
※必要に応じて修正してください。

・プロバイダ
・サーバー名
・DB名
・ユーザー名
・パスワード
※上記では「ユーザー名」と「パスワード」は仮で「XXXXX」としています。適切な値を設定してください。

既にシート「data」が存在する場合は削除します(29~38行目)。

「Worksheets.Add(After:=Worksheets(Worksheets.Count))」によって、シートの最後(一番右)にシート「data」を新規作成します(41、42行目)。

「QueryTables.Add」で「QueryTableオブジェクト(=クエリと接続)」を作成します(61~70行目)。
※データの取得/設定を実行した後に削除(Delete)します。

「Destination」に「Range(“B2”)」と指定しているため、
 ・読み込んだ内容はセル「B2」から設定
されます(62行目)。


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

PR

実行結果

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

実行結果
実行結果

列幅は自動で調整されます。

PR

参考①

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

シート上の表(一連のセル範囲)をテーブル化
シート上の表(一連のセル範囲)をテーブル化


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

PR

参考②

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

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

PR

参考③

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

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

PR

参考④

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

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

PR

参考⑤

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

●「QueryTables.Add」


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

タイトルとURLをコピーしました