How to add Dynamic Variable in VBA PowerQuery

0
votes

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.

1
excelvbapowerquery
You need to concatenate with &. – BigBen
@BigBen I added it as File.Contents(""&f&"") but am getting an error for valid absolute path details &f& – JoshK
First step. Debug.Print your concatenated string and examine in the Immediate Window. – BigBen
@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
I guess I meant Debug.Print everything after Formula:= _, i.e. the actual M query, not just the path portion. – BigBen

1 Answers

2
votes

Solution was to replace:

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

with

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

Thanks to @BigBen for the help!