Changing Source in Power Query using vba

ashsweety

New Member
Joined
May 23, 2024
Messages
1
Office Version
  1. 2021
Platform
  1. Windows
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
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,226,453
Messages
6,191,134
Members
453,642
Latest member
jefals

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top