VBA: クエリーで読み込むCSVファイルを簡単に切り替える

Excelの「データ」メニューの「テキストまたはCSVから」を使ってCSVを容易に読み込み、データ型の指定などをすることができます。

VBAで処理する場合、任意の読み込みファイルを指定したい場合があると思います。

クエリーのパラメーターを使うと容易に読み込むファイルを変更することができるようになります。

パラメーターを作成する

「データ」メニューから「クエリと接続をクリックします。

CSVファイルを読み込むクエリーをダブルクリックします。

「パラメーターの管理」をクリックし「新しパラメーター」をクリックします。

「名前」を適宜変更し、「現在の値」にCSVファイルのパスを一時的に指定しOKします。

パラメーターが追加されました。

クエリーを選択します。

「ソース」をダブルクリックします。

「ファイルパス」のプルダウンをクリックし、「パラメーター」をクリックします。

先ほど追加したパラメーターを選択しOKします。

「ソース」のパス部分がパラメーターに変わっています。

「閉じて読み込む」でクエリーを保存します。

これでパラメーターを変更することによりCSVファイルの切り替えを行えるようになりました。

コード

CSVファイルを変更して、クエリーを再実行するサンプルです。

コピペ用のコードです。

Public Sub uImportCSV()

    Dim uWS As Worksheet

    Dim uQuery As WorkbookQuery

    

    Const uCsvPath As String = "C:\NKTemp\Sample2.csv"

    

    For Each uQuery In ThisWorkbook.Queries

        If uQuery.Name = "uCSVPath" Then

            uQuery.Formula = _

                """" & uCsvPath & """" & _

                " meta [IsParameterQuery=true, Type=""Any"", IsParameterQueryRequired=true]"

            Exit For

        End If

    Next

    

    ThisWorkbook.Queries("Sample").Refresh

End Sub

ワークブックのクエリーをすべて検索し、CSVパスを保存したパラメーター名に一致する場合、クエリー（パラメーター）のFormulaを書き換えます。

最後に、CSVファイルを読み込むクエリーをリフレッシュ（再読み込み）します。

クエリーの更新に注意

なお、当然ですが、クエリーはCSVファイルにリンクされたままなので、そのままでは更新される可能性があるので注意が必要です。

テーブルをクリックし、「データ」の「プロパティ」をクリックします。

「クエリープロパティ」をクリックします。

更新タイミングの設定を確認します。

「バックグラウンドで更新する」は更新後にプログラム処理する場合は外しておいた方が良い気がします。

「すべての更新」ボタンの対象からも外しておいた方が無難かもしれません。もちろん、わかって使うのなら良いですが。

おわり

定型業務でファイルのフォーマットは決まっているけれど、毎月、毎週、別のファイルを指定しないといけないような場合に便利です。例えば、 請求データ11月.csv みたいな。

ファイルフォーマットが変わってしまうようなら、一からクエリーを作るしかないと思います。

VBA Power Queryを使ってCSVファイルを読み込む

VBAからパラメーターを作成する方法はこちらです。

VBA PowerQueryでCSVファイルを読み込み パラメーター版

追記　WorkbookQuery.Refleshは非同期

実務で使ったところ、一つ問題があることがわかりました。それは WorkbookQuery オブジェクトの Reflesh メソッドは非同期だという事です。

クエリーをリフレッシュした後に、テーブルの値を参照しようとしたところ、うまくいきませんでした。テーブルがすべて読み込まれる前に次の命令が実行されてしまうためです。

それなら  BackgroundQuery プロパティを false にすれば済むだろうと思って試そうとしたところ、なんと、WorkbookQueryには BackgroundQuery がありませんでした。

WorkbookQuery object (Excel)

やむを得ず、リンク先のテーブルに対して Refesh を実行することにより回避しました。

Public Sub uImportCSV()
    Dim uWS As Worksheet
    Dim uQuery As WorkbookQuery
    Dim uList As ListObject
    
    Const uCsvPath As String = "C:\NKTemp\Sample2.csv"
    
    For Each uQuery In ThisWorkbook.Queries
        If uQuery.Name = "uCSVPath" Then
            uQuery.Formula = _
                """" & uCsvPath & """" & _
                " meta [IsParameterQuery=true, Type=""Any"", IsParameterQueryRequired=true]"
            Exit For
        End If
    Next
    
    Set uList = ThisWorkbook.Worksheets("Sample").ListObjects("Sample")
    With uList.QueryTable
        .BackgroundQuery = False
        .Refresh
    End With
End Sub

最後の部分は次のようにも書けます。

Set uList = ThisWorkbook.Worksheets("Sample").ListObjects("Sample")
uList.QueryTable.Refresh BackgroundQuery:=False

この場合は、QueryTableのBackgroundQueryプロパティを変更することなく実行可能です。