How to add Dynamic Variable in VBA PowerQuery

Question

I am connecting an excel workbook to a VBA macro through a power query. The macro should select the file within the user's directory that also matches the correct date on the file name.

The path should be first generated by the variable:

f = ThisWorkbook.Path & "\Data\details_" & Month([Date].Value) & "." & Day([Date].Value) & ".xlsx"

Then, the variable should be placed into the PowerQuery:

ActiveWorkbook.Queries.Add Name:="PortalData", Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Source = Excel.Workbook(File.Contents(f), null, true)," & Chr(13) & "" & Chr(10) & "    Table1_Table = Source{[Item=""Table1"",Kind=""Table""]}[Data]," & Chr(13) & "" & Chr(10) & "    #""Changed Type"" = Table.TransformColumnTypes(Table1_Table,{...})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Changed Type"""

The File.Contents(f) searches for a file named 'f' instead of the path. The File.Contents() originally contained a path in double-quotes but adding double quotes to f does not work.

@BigBen I added it as File.Contents(""&f&"") but am getting an error for valid absolute path details &f& — JoshK
– JoshK, Commented Feb 3, 2021 at 17:02
First step. Debug.Print your concatenated string and examine in the Immediate Window. — BigBen
– BigBen, Commented Feb 3, 2021 at 17:03
@BigBen Ok so running Debug.Print f = path shows as True. Debug.Print path shows C:\Users\XXXXX\Downloads\V3.3 Automated Review File 1.28\Folder1\Portal Data\details_2.3.xlsx\ – — JoshK
– JoshK, Commented Feb 3, 2021 at 17:11
I guess I meant Debug.Print everything after Formula:= _, i.e. the actual M query, not just the path portion. — BigBen
– BigBen, Commented Feb 3, 2021 at 17:12

JoshK · Accepted Answer · 2021-02-03 17:26:08Z

3

Solution was to replace:

File.Contents(""&f&"")

with

File.Contents(""" & f & """)

Thanks to @BigBen for the help!

answered Feb 3, 2021 at 17:26

JoshK

4251 gold badge4 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to add Dynamic Variable in VBA PowerQuery

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related