Changing Source in Power Query using vba

[ashsweety]

I am newbie to vba. I need to change the source in power query. I have a folder which contains all the data files monthly. I want to select each file one after another use it as source for power query , run the query and save it to worksheet.
e,g Data is for each month. Need to run the query for each month, select source as each month from data folder , run the query and save the data to year table

 

[JEC]

Use a parameter table in power query to which you refer in the source step of your query. You can change the parameter table to a specific path(where the month is variable), then refresh All

 

[barren]

I am newbie to vba. I need to change the source in power query. I have a folder which contains all the data files monthly. I want to select each file one after another use it as source for power query , run the query and save it to worksheet.
e,g Data is for each month. Need to run the query for each month, select source as each month from data folder , run the query and save the data to year table

This VBA coding might help you find a solution.
Unlike other solutions this one works on MAC as well as on Win. Referencing a cell in a worksheet is fine for Win but does not work in MAC. To be universal this is the only method I have found that works and it is so easy. Thanks Michael it took me a long time to find your post and I will try to share it as much as I can.
How to change source of a power query using VBA?

Sub change_source()

Dim pqTable As WorkbookQuery
Dim oldSource As String
Dim newSource As String
Set pqTable = ThisWorkbook.Queries("put query name here")

oldSource = Split(pqTable.Formula, """")(1) ‘gets Source from old query
newSource = "put new source path here"
‘ this msg just confirms change, not required for task
MsgBox ("Changing From: " & oldSource & vbCr & vbLf & vbCr & vbLf & "To: " & newSource)
‘this statement modifies Source= formula in query to have new source as path
pqTable.Formula = Replace(pqTable.Formula, oldSource, newSource)

End Sub