【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"
    'サーバー名(サーバーのPC名\インスタンス名)
    Const SERVER_NAME As String = "localhost\SQLEXPRESS"
    'DB名
    Const DB_NAME As String = "sampleDB"
    '実行するSELECT文
    Const SQL As String = "SELECT * FROM m_product"
    'SELECT文の結果を設定するシート名
    Const DATA_SHEET_NAME As String = "data"
    
    Dim sheet As Worksheet
    Dim dataSheet As Worksheet
    Dim conStr As String
    Dim n As Name
    
    '既にシート「date」が存在する場合は削除
    For Each sheet In ThisWorkbook.Worksheets
        If sheet.Name = DATA_SHEET_NAME Then
            '確認メッセージを非表示
            Application.DisplayAlerts = False
            'シート削除
            Worksheets(DATA_SHEET_NAME).Delete
            '確認メッセージを表示
            Application.DisplayAlerts = True
        End If
    Next

    'シート「date」を新規作成
    Set dataSheet = Worksheets.Add(After:=Worksheets(Worksheets.Count))
    dataSheet.Name = DATA_SHEET_NAME
    
    '接続文字列の組み立て
    conStr = "Provider=" & PROVIDER & ";" & _
              "Data Source='" & SERVER_NAME & "';" & _
              "Initial Catalog='" & DB_NAME & "';" & _
              "Integrated Security=SSPI;" & _
              "DataTypeCompatibility=80;"
    
    '「QueryTableオブジェクト(=クエリと接続)」を作成
    With dataSheet.QueryTables.Add(Connection:="OLEDB;" & conStr, _
                                              Destination:=Range("B2"), _
                                              SQL:=SQL)
        'SELECT文を実行
        .Refresh BackgroundQuery:=False
        '作成された「QueryTableオブジェクト(=クエリと接続)」を削除
        .Delete
    End With
    
End Sub

既にシート「data」が存在する場合は削除します。

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

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

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

必要に応じて以下の環境情報を修正してください。
・7行目「サーバー名」
・9行目「DB名」


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

PR

実行結果

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

実行結果
実行結果

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

PR

参考①

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

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

PR

参考②

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

●「QueryTables.Add」


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

PR

参考③

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

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

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