VBA & Macro Problems

Add a VBA File Picker to an Excel Power Query Import

John WilsonJohn Wilson Nov 18, 2026 869 views

Question details

The user wants an Excel macro to let users choose a text file each time a Power Query import runs.

Add a VBA File Picker to an Excel Power Query Import
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.
Before you start

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.

Solution 1Recommended

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.

1
Create a path query

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.

2
Update the import query

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.

3
Add the file-picker macro

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

4
Run and check the import

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.

Pass the selected file path to Power Query through a query
Keep the query reference intact: If an existing macro rebuilds the import query, update its generated formula to use File.Contents(SelectedFilePath) as well. The selected file must also match the structure expected by the existing transformation steps.
Free Microsoft Office alternative

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.

Open and work with data from text files in WPS SpreadsheetsCompatibility with Microsoft Excel formats such as XLSX and XLSFamiliar spreadsheet interface for everyday editingFree, lightweight office suite for common document tasks
QA img-9

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.