ExcelのVBAからPower QueryのCSV取り込みを動かす方法｜ファイルパスの差し替え・更新・完了を待ってから次の処理へ進める

Power QueryでCSVの取り込み手順を作っておき、毎月の作業ではVBAから「取り込むファイルを差し替えて、更新して、終わったら保存や集計へ進む」ようにすると、整形はPower Query、流れの制御はVBA、と役割を分けられます。

このとき注意したいのが、更新が終わる前にVBAが次の行へ進んでしまうことです。この記事では、ファイルパスの差し替え、更新の完了を待つ設定、失敗したときに元に戻す処理までを順に説明します。

全体の流れ

1. Power Queryで、CSVを1つ取り込むクエリを手作業で作り、正しく取り込めることを確認しておく
2. VBAで、クエリのM式（Formulaプロパティ）に書かれたファイルパスを差し替える
3. 接続の「バックグラウンドで更新する」をオフにしてから更新する
4. 取り込んだ件数を確認してから、保存や集計に進む

VBAで扱うのは「どのファイルを読むか」と「いつ更新するか」だけにします。列の型や不要な列の削除などの整形は、Power Query側に任せます。

クエリ名・接続名・M式を確認する

VBAからは、クエリ名と接続名の2つを使います。日本語版のExcelでは、接続名が「クエリ - クエリ名」のような形になっていることが多いですが、環境によって違う可能性があるため、まず一覧を出して確認します。

Sub ListQueriesAndConnections()
    Dim q As WorkbookQuery
    For Each q In ThisWorkbook.Queries
        Debug.Print "クエリ: " & q.Name
        Debug.Print q.Formula
    Next q

    Dim cn As WorkbookConnection
    For Each cn In ThisWorkbook.Connections
        Debug.Print "接続: " & cn.Name
    Next cn
End Sub

VBEの「表示」→「イミディエイトウィンドウ」に結果が出ます。M式の中に、次のような File.Contents("...") の行があるはずです（ステップ名や引数は、作り方や環境によって異なります）。

let
    ソース = Csv.Document(File.Contents("C:\work\csv\sales_202609.csv"), [Delimiter=",", Encoding=65001, QuoteStyle=QuoteStyle.Csv]),
    昇格されたヘッダー数 = Table.PromoteHeaders(ソース, [PromoteAllScalars=true])
in
    昇格されたヘッダー数

この File.Contents(" の直後から次の " までが、差し替える対象のパスです。

ファイルパスを差し替える関数

M式の文字列から File.Contents(" を探し、その後ろのパスだけを新しいパスに置き換えます。古いパスを完全一致で探して置換する方法もありますが、前回のパスが分からないと置換できないため、位置で差し替えるほうが毎月の運用に向いています。

Function ReplaceCsvPath(ByVal m As String, ByVal newPath As String) As String
    Const PREFIX As String = "File.Contents("""
    Dim p1 As Long, p2 As Long

    p1 = InStr(1, m, PREFIX)
    If p1 = 0 Then
        Err.Raise vbObjectError + 1, , "M式に File.Contents が見つかりません"
    End If
    p1 = p1 + Len(PREFIX)

    p2 = InStr(p1, m, """")
    If p2 = 0 Then
        Err.Raise vbObjectError + 2, , "パスの終わりの引用符が見つかりません"
    End If

    ReplaceCsvPath = Left(m, p1 - 1) & newPath & Mid(m, p2)
End Function

この関数は、最初に見つかった File.Contents だけを差し替えます。1つのクエリで複数のファイルを読んでいる場合は、クエリを分けるか、差し替える箇所を判別する処理を追加してください。

更新して、完了を待ってから次へ進む

Power Queryの接続は、初期設定で「バックグラウンドで更新する」がオンになっていることがあります。この状態で更新を実行すると、VBAは更新の完了を待たずに次の行へ進み、古いデータのまま保存や集計をしてしまうことがあります。

更新の前に BackgroundQuery を False にすると、更新が終わるまでVBAが待つようになります。また、更新に失敗したときにエラーとして受け取れるようになります。

Sub RefreshSalesCsv()
    Const QUERY_NAME As String = "売上CSV"
    Const CONN_NAME As String = "クエリ - 売上CSV"
    Const SHEET_NAME As String = "売上"

    Dim csvPath As String
    csvPath = "C:\work\csv\sales_202610.csv"

    If Dir(csvPath) = "" Then
        MsgBox "CSVが見つかりません：" & csvPath, vbExclamation
        Exit Sub
    End If

    Dim q As WorkbookQuery
    Dim oldFormula As String
    Set q = ThisWorkbook.Queries(QUERY_NAME)
    oldFormula = q.Formula

    On Error GoTo Failed
    q.Formula = ReplaceCsvPath(oldFormula, csvPath)

    With ThisWorkbook.Connections(CONN_NAME).OLEDBConnection
        .BackgroundQuery = False
        .Refresh
    End With
    On Error GoTo 0

    Dim rowCount As Long
    rowCount = ThisWorkbook.Worksheets(SHEET_NAME).ListObjects(1).ListRows.Count
    If rowCount = 0 Then
        MsgBox "取り込み件数が0件です。CSVの中身を確認してください。", vbExclamation
        Exit Sub
    End If

    ' ここから保存や集計の処理
    ThisWorkbook.Save
    MsgBox "取り込みが完了しました：" & rowCount & "件", vbInformation
    Exit Sub

Failed:
    q.Formula = oldFormula
    MsgBox "更新に失敗したため、クエリを元のパスに戻しました。" & vbCrLf & Err.Description, vbCritical
End Sub

ポイントは次のとおりです。

• 差し替える前に Dir でファイルの存在を確かめる。存在しないパスに差し替えると、更新時にエラーになる
• 差し替え前のM式を oldFormula に残しておき、失敗したら元に戻す
• 更新が終わったら、取り込んだ件数を確認してから保存や集計に進む

ListObjects(1) は、取り込み先のシートにテーブルが1つだけある前提です。複数ある場合は、ListObjects("売上CSV") のようにテーブル名で指定します。テーブル名は、テーブル内のセルを選んだときの「テーブルデザイン」タブで確認できます。

シートに読み込んだテーブルから更新する方法

シートにテーブルとして読み込んでいるクエリは、テーブル側から更新する書き方もあります。

ThisWorkbook.Worksheets("売上").ListObjects("売上CSV").QueryTable.Refresh BackgroundQuery:=False

接続名を調べなくてよい点が便利です。「接続専用」で読み込んでいるクエリにはシート上のテーブルがないため、接続から更新する書き方を使います。

すべて更新を使う場合

ThisWorkbook.RefreshAll は、バックグラウンド更新がオンの接続が含まれていると、完了を待たずに次へ進むことがあります。すべて更新を使う場合は、先にすべての接続の BackgroundQuery をオフにしておきます。

Dim cn As WorkbookConnection
For Each cn In ThisWorkbook.Connections
    If cn.Type = xlConnectionTypeOLEDB Then
        cn.OLEDBConnection.BackgroundQuery = False
    End If
Next cn
ThisWorkbook.RefreshAll

同じ設定は、「データ」→「クエリと接続」でクエリを右クリックし、「プロパティ」の「バックグラウンドで更新する」のチェックを外しても変えられます。

取り込むファイルを毎回選ぶ・名前から作る

パスをコードに直接書かず、実行時にファイルを選んでもらうこともできます。

Dim picked As Variant
picked = Application.GetOpenFilename("CSVファイル (*.csv),*.csv", , "取り込むCSVを選択")
If VarType(picked) = vbBoolean Then Exit Sub   ' キャンセルされた
csvPath = CStr(picked)

ファイル名に年月が入るルールなら、日付から組み立てる方法もあります。

csvPath = "C:\work\csv\sales_" & Format(Date, "yyyymm") & ".csv"

VBAでM式を書き換えず、ブック内のセルにパスを入れてPower Query側で読む方法もあります。どちらを選ぶかは、パスを変える人がVBAを動かす人と同じかどうかで決めるとよいでしょう。

うまく動かないときの確認ポイント

クエリの差し替えは保存するとブックに残ります。成功したときは新しいパスのまま保存され、次回はそのパスから差し替えが始まります。

運用前の確認項目

• 手作業で作ったクエリが、元のCSVで正しく取り込めている
• クエリ名・接続名・取り込み先のテーブル名を一覧で確認した
• 存在しないパスを指定したとき、メッセージを出して止まる
• 列の構成が違うCSVを指定したとき、エラーになりクエリが元のパスに戻る
• 更新が終わってから件数確認・保存に進んでいる（件数がCSVの行数と合う）
• ブックをマクロ有効ブック（.xlsm）で保存している