Can you create dynamic power query formulas in VBA?-CodePudding

I used the macro recorder in Excel to import a table from a PDF into the Power Query editor, and then to export the transformed table to excel.

I want to reuse this code for other similar PDF tables that are identical in structure.

The issue is that the "Value Balance" column will have a different date in each PDF table. I would like to use a dynamic variable to handle the updated date. The concatenation I used with the variable "RefDate" in the below code does not work:

Sub Macro1()

RefDate = "05-05-22"

ActiveWorkbook.Queries.Add Name:="Table001 (Page 1)", Formula:= _
    "let" & Chr(13) & "" & Chr(10) & "    Source = Pdf.Tables(File.Contents(""C:\Users\MyPDF.pdf""), [Implementation=""1.3""])," & Chr(13) & "" & Chr(10) & "    Table001 = Source{[Id=""Table001""]}[Data]," & Chr(13) & "" & Chr(10) & "    #""Promoted Headers"" = Table.PromoteHeaders(Table001, [PromoteAllScalars=true])," & Chr(13) & "" & Chr(10) & "    #""Changed Type"" = Table.TransformColumnTypes(#""Promoted Headers"",{{""Ac" & _
    "count Code"", type text}, {""Ccy"", type text}, {""Book Balance"", type number}, {""Value Balance#(lf)"" & RefDate, type number}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Changed Type"""

End Sub

How can I create a dynamic string in this type of nested code?

Thanks!

CodePudding user response：

You can see in your post how the variable name is green. That is a visual indicator that it is inside the string. You have to break the string and concatenate the variable. Compare the following examples:

MyString = "The value is MyVar."

MyString = "The value is " & MyVar & "."