作成済みのExcelの「クエリと接続」を、VBAから実行(更新)する

　

サンプルプログラム作成

ここでは以下の「接続とクエリ」をVBAから実行(更新)します。

項目	内容
シート名	サンプル
エクセル上のテーブル名	GetEmployeeTable　※「数式タブ」-「名前の管理」で確認
接続とクエリ名	GetEmployee

'変数の宣言を必須
Option Explicit

Sub execConnectQuery()

    '===============================
    '接続文字列
    '===============================
    'プロバイダ
    Const PROVIDER As String = "MSOLEDBSQL"
    'サーバー名(サーバーのPC名\インスタンス名)
    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"
    
    
    '===============================
    '接続とクエリの情報
    '===============================
    'シート名
    Const SHEET_NAME As String = "サンプル"
    'エクセル上のテーブル名
    Const TABLE_NAME As String = "GetEmployeeTable"
    '接続とクエリ名
    Const QUERY_NAME As String = "GetEmployee"
    
    Dim conStr As String
    
    '===============================
    '接続文字列の組み立て(Windows認証)
    '===============================
    conStr = "OLEDB;" & _
             "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;"
              
    '===============================
    '接続とクエリを実行(更新)
    '===============================
    With ThisWorkbook
        '接続文字列を指定
        .Connections(QUERY_NAME).OLEDBConnection.Connection = conStr
        '更新
        .Worksheets(SHEET_NAME).ListObjects(TABLE_NAME).QueryTable.Refresh BackgroundQuery:=False
    End With

End Sub

結果

実行前の状態

更新されることを確認するために、サンプルプログラムを実行する前にシート上のテーブルの値を任意の値へ変更します。
※下記はセル「B3」値を「aiueo」へ変更しました。

実行後の状態

サンプルプログラムの実行により、シート上のテーブルがSQL Server上のテーブルと同じデータになりました。
※最新の状態に更新されました。