VBAでPowerQueryとピボットテーブルの更新を制御する

Sub Macro1()
'[データ] > [クエリと接続] > [すべて更新]
    ActiveWorkbook.RefreshAll
End Sub

Sub Macro2()
'ピボットテーブルのあるシートで [データ] > [クエリと接続] > [すべて更新] > [更新] 
    Sheets("Pivot").Select
    Selection.ListObject.TableObject.Refresh
    Selection.ListObject.TableObject.Refresh
    Selection.ListObject.TableObject.Refresh
    ActiveSheet.PivotTables("ピボットテーブル1").PivotCache.Refresh
End Sub

Sub Macro3()
'[クエリと接続] の各クエリリスト上で表示される [最新の情報に更新] をクリック 
    ActiveWorkbook.Connections("クエリ - 販売クエリ").Refresh
    ActiveWorkbook.Connections("クエリ - 店舗マスタ").Refresh
    ActiveWorkbook.Connections("クエリ - 単価マスタ").Refresh
End Sub

'全てのクエリ更新
Sub AllQueryRefresh()
    Dim wb As Workbook
    Dim sh As Worksheet
    Dim wbc As WorkbookConnection
    Dim bolOLEDB As Boolean 'OLEDBのBackgroundQuery設定保存用
    Dim bolODBC As Boolean  'ODBCのBackgroundQuery設定保存用
    
    Set wb = ActiveWorkbook
    'OLEDBとODBCでない場合エラーになるので回避のためのおなじない
    On Error Resume Next
    For Each wbc In wb.Connections  'コネクションの全てをループ
        'BackgroundQueryの状態を変数に保存
        bolOLEDB = wbc.OLEDBConnection.BackgroundQuery
        bolODBC = wbc.ODBCConnection.BackgroundQuery
        wbc.OLEDBConnection.BackgroundQuery = False
        wbc.ODBCConnection.BackgroundQuery = False
        Debug.Print wbc.Name        'クエリ名確認用に
        '更新実行
        wbc.Refresh
        'BackgroundQueryの状態を元に戻す
        wbc.OLEDBConnection.BackgroundQuery = bolOLEDB
        wbc.ODBCConnection.BackgroundQuery = bolODBC
    Next
    On Error GoTo 0
    'ピボットテーブル更新へ
    Call piv_CacheRefresh
End Sub

'ブック内全てのピボットテーブル更新
Sub piv_CacheRefresh()
    Dim sh As Worksheet
    Dim pivTable As PivotTable
    
    'ブックの全ワークシートをループ
    For Each sh In Worksheets
        'ワークシート内のピボットテーブルを更新
        For Each pivTable In sh.PivotTables
            Debug.Print pivTable.Name   'Pivotテーブル名
            pivTable.PivotCache.Refresh 'Pivot更新
        Next pivTable
    Next sh
End Sub

'ブック内の全クエリ接続名取得
Sub GetConnectionName()
    Dim wb As Workbook
    Dim sh As Worksheet
    Dim wbc As WorkbookConnection
    Dim i As Long
    
    Set wb = ActiveWorkbook
    Set sh = wb.ActiveSheet
    i = 2
    'シートのセルデータを一旦クリアする
    sh.Range(Cells(i, 7), Cells(50, 7)).ClearContents
    'ブック内のクエリ接続名を全て書き込む
    For Each wbc In wb.Connections
        sh.Cells(i, 7) = wbc.Name   'クエリ接続名を書き込む
        i = i + 1
    Next
End Sub

'指定順にクエリ更新
Sub QueryRefresh()
    Dim wb As Workbook
    Dim sh As Worksheet
    Dim wbc As WorkbookConnection
    Dim bolOLEDB As Boolean
    Dim bolODBC As Boolean
    Dim i As Long, n As Long, tg As Long
    Dim cname As String
    
    Set wb = ActiveWorkbook
    Set sh = wb.ActiveSheet
    n = WorksheetFunction.Max(sh.Columns(6))
    'OLEDBとODBCでない場合エラーになるので回避のため
    On Error Resume Next
    For i = 1 To n
        tg = WorksheetFunction.Match(i, sh.Columns(6), 0)
        cname = sh.Cells(tg, 7).Value
        Set wbc = wb.Connections(cname)
        With wbc
            'BackgroundQueryの状態保存
            bolOLEDB = .OLEDBConnection.BackgroundQuery
            bolODBC = .ODBCConnection.BackgroundQuery
            'BackgroundQueryをFalseに設定
            .OLEDBConnection.BackgroundQuery = False
            .ODBCConnection.BackgroundQuery = False
            '更新実行
            .Refresh
            'BackgroundQueryの状態を戻す
            .OLEDBConnection.BackgroundQuery = bolOLEDB
            .ODBCConnection.BackgroundQuery = bolODBC
        End With
    Next
    On Error GoTo 0
    
    Call piv_CacheRefresh   'ピボット更新へ
 
End Sub

Private Sub CommandButton1_Click()
    Dim wb As Workbook
    Dim sh As Worksheet
    Dim n As Long
    
    Set wb = ActiveWorkbook
    Set sh = wb.ActiveSheet
    n = WorksheetFunction.Max(sh.Columns(6))
    'F列に数値設定があるかどうかで処理を分岐
    If n <> 0 Then
        Call QueryRefresh       '指定順で更新処理へ
    Else
        Call AllQueryRefresh    'すべて更新する処理へ
    End If
End Sub

Private Sub CommandButton2_Click()
    Call GetConnectionName
End Sub