Can Power Query prompt for a file name when loading a text file?

Question

Power Query can be used to pull text files into Excel.

But does it have an equivalent to Excel's External Data's "Prompt for file name on refresh"?

That is, to assume the pulled text file has a dynamic name and therefore ask to browse for it each time a refresh is triggered.

I know Power Query has a feature of Parameters, but can they trigger a "choose file" prompt?

score 0 · Accepted Answer · answered Mar 13 '25 at 12:48

Well, the lack of answer until now suggests Power Query really lacks such an option, but as suggested in the comments VBA can simulate it:

First use the following table via this Power Query Parameter:

The first two parameters are just used for the prompt's default, while the last one is for actual storage of the result. So put the last one in your Power Query's Source:

= Csv.Document(File.Contents(fnGetParameter("path_Original file")),[Delimiter=",", Columns=21, Encoding=65001, QuoteStyle=QuoteStyle.None])

Then call the following whenever you like to trigger a refresh:

Sub RefreshAll()
    Dim wb As Workbook, filePath As Variant, thefolder As String, thefile As String, thetable As Range, cn As WorkbookConnection
    Set wb = ThisWorkbook
    ' Assumes your table is in the very last sheet
    Set thetable = wb.Sheets(wb.Sheets.Count).ListObjects(1).DataBodyRange
    ' Grab default values
    thefolder = thetable.Find("Original folder").Offset(0, 1).Value
    thefile = thetable.Find("Original file").Offset(0, 1).Value
    ' Open default folder and prompt for the suggested default file
    filePath = fileopen(thefolder & thefile, "CSV files", "*.csv")
    ' Blank the result is the prompt was cancelled
    If filePath = False Then
        If thetable.Find("path_Original file").Offset(0, 1).Value <> "" Then
            thetable.Find("path_Original file").Offset(0, 1).Value = ""
        End If
    Else
        ' Otherwise, load the chosen file into Power Query
        thetable.Find("path_Original file").Offset(0, 1).Value = filePath
        ' Then refresh all connections
        For Each cn In ThisWorkbook.Connections
            cn.Refresh
        Next
        ' Then refresh all pivot tables (through their cache, since many of them probably share caches), since those pivot tables are probably based on the connections
        For Each pc In wb.PivotCaches
            pc.Refresh
        Next
    End If
End Sub

Can Power Query prompt for a file name when loading a text file?

1 Answers1