VBA - Changing Power Query Source

Question

Dear All,

I have a Power Query internal to an excel file.

I need your help on VBA programmatically change the below source value from C:\SERVICE\Service\List\January 2025.xlsm to C:\SERVICE\Service\List\November 2025.xlsm

Any idea?
Thanks for your help

Answer 1

I suspect you did not connect PQ to the named range and then 'drilled down' to the file name and create a parameter. Therefore. PQ does not recognise "Filename".

Have a look in the article in the link below. It describes the process in great detail.

https://exceloffthegrid.com/power-query-source-cell-value/

Answer 2

Why VBA? The correct way would be to use a parameter for the file name. Enter the name somewhere in your Excel workbook. Name it, for example "filename". Connect PQ to that named range.

Drill down to get the filename as a text parameter. Use that in the Source step. So, rather than

"C:\SERVICE\Service\List\January 2025.xlsm"

it would then look like:

"C:\SERVICE\Service\List" & filename

Answer 3

Thanks for your reply.

Following your suggestion I have added into "Sheet1" cell E22 a named range: 'Filename'

Then, I have modified as below "C:\SERVICE BU\00 - Service BU\17 - Price List\Normal\2025\11" & Filename:

but I get the error: