VBA: Editing Power Query M-Code using VBA

Question

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:\Users\user1\Documents\source.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:\Users\user1\Documents\source.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:\Users\user1\Documents\source.xml

into something like C:\Users\user2\Documents\source.xml so user2 can use it.

The vba function environ$("username") will return the user's username. You could use this within a UDF to pull the username onto the sheet. — CLR
– CLR, Commented Jul 25, 2024 at 8:19
This will return the users document folder - as CLR said, put it in a function and use on a worksheet to pull the required path. CreateObject("wscript.shell").specialfolders("MyDocuments") — Darren Bartrup-Cook
– Darren Bartrup-Cook, Commented Jul 25, 2024 at 12:15

CHill60 · Accepted Answer · 2024-07-25 08:44:46Z

I frequently have the output from a file picker directed to a Named Range on a worksheet then use this function in Power Query to get the name of the file I require:

let
    GetNamedRange = (NamedRange as any) =>
    let
        name = try Excel.CurrentWorkbook(){[Name=NamedRange]}[Content] otherwise Table.FromColumns({{""}},{"Column1"}),
        value = name{0}[Column1]
    in
        value
in GetNamedRange

You could use it in your code like this

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

It's a little more flexible that assuming documents will always be stored in the Documents library

Storax · Accepted Answer · 2024-07-25 11:16:10Z

Based on Creating Dynamic Parameters in Power Query (the link mentioned in the code is gone) you could use the following PowerQuery function

// fnGetParameter function written by Ken Puls, FCPA, FCMA, MS MVP (Excel)
// Original source can be found at the following URL:
// http://www.excelguru.ca/blog/2014/11/26/building-a-parameter-table-for-power-query/

// To use the function you must have a table called "Parameters" in your workbook
// The header row must contain a "Parameter" and a "Value" column (with those names)

// Data rows should list the paramter name in the first column and the value of that
// parameter in the second column

// Provided you save this query under the name "fnGetParameter", you can then reference
// it from other queries as follows:
//
//  =fnGetParameter("your_parameter_name")

// One useful sample to return the current folder:
// Parameter Name:  File Path
// Parameter Value: =LEFT(CELL("filename",A1),FIND("[",CELL("filename",A1),1)-1)
// Call as  "fnGetParamater("File Path")

let
    Quelle = (ParameterName as text) =>
 let
     ParamSource = Excel.CurrentWorkbook(){[Name="tblParam"]}[Content],
     ParamRow = Table.SelectRows(ParamSource, each ([Parameter]=ParameterName)),
     Value=
    if Table.IsEmpty(ParamRow)=true
    then null
    else Record.Field(ParamRow{0},"Value")
in
    Value
in
    Quelle

PS Building a Parameter Table for Power Query

Look · Accepted Answer · 2025-10-29 15:59:52Z

Just in case someone study this old problem

The simplest solution is:

define query with just one line of code" path in quotes. No "let" and "in"
use this query (that is now marked as text not table) in your real data query as variable
in VBA you can put a line like:

activeWorkbook.Queries("yourQuery").Formula=char(34) & filePath & char(34)

where filePath is your proper calculated path based on workbook path or selected by user with GetOpenFileName

There is no need to put path as a value to a sheet and read it in separate query in PowerQuery

Collectives™ on Stack Overflow

VBA: Editing Power Query M-Code using VBA

3 Answers 3

Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Related