Excelのすべて更新をVBAで自動化する

きっかけ

ExcelのPowerQueryを利用してデータを処理していると、結果を最新に更新するため「データ」タブの「すべて更新」を実行する必要があります。しかし、その作業を複数のファイルに対して行うのは非常に手間がかかるため、VBAを使用して一括実行をしたいと考えました。

VBAにはRefreshAll関数があり、これを使用することでクエリの更新を一括して実行できると思われました。ただし、この関数は非同期で実行されるため、更新が完了する前に後続処理が開始されてしまう問題がありました。

Wait関数を使って更新が完了するまで待機させる方法も検討しましたが、更新自体が停止するため、この方法は使えませんでした。

実現方法とサンプルコード

この問題の解決策として、クエリの BackgroundQuery プロパティを False に設定する方法が有効でした。この設定により、更新が完了するまで待つようにすることが可能になります。サンプルコードを掲載します。

Sub Macro1()
    Dim objCon As WorkbookConnection, bBQ As Boolean
    Dim objConType As Object

    ' コネクションごとに処理
    For Each objCon In ThisWorkbook.Connections
        ' タイプによって分岐
        Select Case objCon.Type
            Case xlConnectionTypeODBC
                ' ODBC接続の設定
                Set objConType = objCon.ODBCConnection
            Case xlConnectionTypeOLEDB
                ' OLEDB接続の設定
                Set objConType = objCon.OLEDBConnection
        End Select

        ' BackgroundQueryを更新前にFalseに設定
        With objConType
            bBQ = .BackgroundQuery
            .BackgroundQuery = False
            objCon.Refresh
            ' 更新後に元の設定を復元
            .BackgroundQuery = bBQ
        End With
    Next
End Sub

おわりに

このコードを使用することで、Excelのクエリの更新を自動化することができました。

参考文献

VBA RefleshAll作業を待ってから次の作業に移る
https://detail.chiebukuro.yahoo.co.jp/qa/question_detail/q14205174064