I have a shared Excel file with a Power Query that extracts data from an external local file. However, several users work with the shared file and they need the Power Query to access the local file from their PC’s local directory.

Say the Power Query extracts data from this file:

C:Usersuser1Documentssource.xml

This would work for user user1, but when user2 uses the shared file, then the Power Query wouldn’t work anymore.

Is there a way for VBA to automate changing the Power Query M-Code, so that each user can automate changing the Power Query without having to manually change the source directory?

I was thinking of having the user input his username in a cell, and I could use that value to edit the directory to incorporate the username, and then have VBA edit the Power Query to source from that new directory.

I found the following online but I’m not sure how to edit:

Dim pqTable As WorkbookQuery 'replace pqTable with any name
Dim oldSource As String
Dim newSource As String

Set pqTable = ThisWorkbook.Queries("Your Query Here")
oldSource = Split(pqTable.Formula, """")(1)
newSource = "Your file path here"

pqTable.Formula = Replace(pqTable.Formula, oldSource, newSource)

My PQ M-Code goes something like this:

"let
    Source = Xml.Tables(File.Contents("C:Usersuser1Documentssource.xml")),
    Table0 = Source{0}[Table],
    #"Changed Type" = Table.TransformColumnTypes(Table0,xxxxxxxx), _
    #"Reordered Columns" = Table.ReorderColumns(#"Changed Type",xxxxxxxx)
in
    #"Reordered Columns""

Basically, I just need VBA to automate changing this part of the M-Code

C:Usersuser1Documentssource.xml

into something like C:Usersuser2Documentssource.xml so user2 can use it.