CODEWITHSUNDEEP
excelvbapowerquery
JoshK
JoshK

Reputation: 425

How to add Dynamic Variable in VBA PowerQuery

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.

Upvotes: 0

Views: 1901

Answers (1)

JoshK
JoshK

Reputation: 425

Solution was to replace:

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

with

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

Thanks to @BigBen for the help!

Upvotes: 3

Related Questions