Add a VBA File Picker to an Excel Power Query Import
Question details
The user wants an Excel macro to let users choose a text file each time a Power Query import runs.

- Product
- Microsoft Excel Power Query and VBA
- Device & OS
- not provided
- Scenario
- A Power Query import macro currently uses a fixed text-file path, but the source file needs to change between runs.
- Observed behavior
- The desired workflow opens a file-selection dialog, uses the selected path for the query, and exits without refreshing if the dialog is canceled.
Use an Excel version that supports both VBA and Power Query, and save the workbook as a macro-enabled .xlsm file. Identify the existing query and the File.Contents expression that points to the text file.
Pass the selected file path to Power Query through a query
Use a small path query to change the source file without rebuilding the import query each time.
The path query holds a text value. The existing import query references that value in File.Contents, while its other transformation and load steps can remain unchanged.
In Excel, select Data > Get Data > From Other Sources > Blank Query. In Power Query Editor, open Home > Advanced Editor and enter a quoted path to an existing text file, such as "C:\Data\sample.txt". Name the query SelectedFilePath, then use Close & Load To to create a connection without loading it to a worksheet.
Open Data > Queries & Connections, edit your existing import query, and select Home > Advanced Editor. Replace the fixed path expression, such as File.Contents("C:\Data\sample.txt"), with File.Contents(SelectedFilePath). Keep the remaining import and transformation steps, then close the editor.
Press Alt+F11, select Insert > Module, and paste this code: Sub PickTextFileAndRefresh() Dim sFile As String With Application.FileDialog(3) .Title = "Select a text file" .AllowMultiSelect = False .Filters.Clear .Filters.Add "Text files", "*.txt" If .Show <> -1 Then Exit Sub sFile = .SelectedItems(1) End With ThisWorkbook.Queries("SelectedFilePath").Formula = _ """" & Replace(sFile, """", """""") & """" ThisWorkbook.RefreshAll End Sub
In Excel, open the Macros dialog, select PickTextFileAndRefresh, and choose Run. Select a compatible text file and confirm that the loaded query updates. Choose Cancel to exit without changing the path or refreshing.

Work with text-file data in WPS Spreadsheets
WPS Office is a free, lightweight alternative with a familiar spreadsheet interface and compatibility with Excel files, making it easy to move everyday spreadsheet work. The Excel Power Query and VBA workflow described above is specific to Excel and may not transfer directly.

Frequently Asked Questions
What happens if I cancel the file picker?
The macro exits at the dialog check before updating SelectedFilePath or calling RefreshAll, so the current source path remains unchanged.
How do I select CSV files instead of TXT files?
In the VBA module, change the filter to .Filters.Add "CSV files", "*.csv". Make sure the import query's parsing steps are suitable for the selected CSV files.
Why does the query fail after I select a file?
Check Data > Queries & Connections for the query error. The selected file may be inaccessible or may have different columns, delimiters, or encoding from the file expected by the existing transformation steps.
Will this macro refresh other queries too?
Yes. ThisWorkbook.RefreshAll requests a refresh of the workbook's external data connections, not just the text-file query. Use a targeted connection refresh instead if other connections should remain unchanged.




