Manually choose file to query

Occasional Visitor

I have an Excel workbook that queries another workbook for its content, and then do a bunch of other things. Using the 'New Query' in Excel, makes me point to a specific file to query, but in my case I want the 'Master' Excel Workbook to ask what file it should open to query, possibly with a file open dialog box.

 

The reason for this is that the data file Excel, arrives several times a month, with different names, but with identical data structure.

 

It will work as it is today, but only if the file is renamed and moved to the same folder where the original file was, but it would be so much easier to just click on the data file and the Master Excel workbook will query it....

 

Any help would be deeply appreciated.

2 Replies
What I would suggest is to have a cell (or 2) in the worksheet to define the path & name of the file you want to open. Then just right click the file you want and copy path and then paste into the sheet and hit Refresh All data. Here is a sample link for how you can do that:
https://excelquick.com/excel-power-query/power-query-dynamic-file-path/

@spehe In case you always want to query the most recent file, store all files in a dedicated folder as they come in. Get data From File, From Folder, and navigate to the designated folder, Open and choose Transform Data. Now you will see the content of the folder. In the "Date created" column filter for Date "Is Latest". Remove all columns except the first one called "Content" and press the two-arrow icon beside the column name. Do your transformations. Close and load. That's it.

When a new file comes in, just refresh the query. No need to change file name as the query will always use the most recent file in the folder.