Add table from pdf to excel macro

Hi,

I have a query about a macro using the get from file function in excel. Specifically I want to extract information from a file, however this file path is depended on a cell information in the excel file. I create the path name based on the strings at the start of the macro. I then point the source of the PDF (highlighted in red in the VBA code see below, this is only an extract of the final code), however I doesn't seem to like that source and I get the error attached. I can't find an example VBA to see what I doing wrong. I think its because the code is actually inserting ''path'' into the excel query rather than the file extension. You can see screenshot from query attached. Any help would be greatly appreciated.

Attachment 29728Attachment 29729

ActiveWorkbook.Queries.Add Name:="Table004 (Page 1) (2)", Formula:= _
"let" & Chr(13) & "" & Chr(10) & " Source = Pdf.Tables(File.Contents(path), [Implementation=""1.3""])," & Chr(13) & "" & Chr(10) & " Table004 = Source{[Id=""Table004""]}[Data]," & Chr(13) & "" & Chr(10) & " #""Changed Type"" = Table.TransformColumnTypes(Table004,{{""Column1"", type text}, {""Column2"", type number}, {""Column3"", type text}, {""Colu" & _
"mn4"", type text}, {""Column5"", type number}, {""Column6"", type text}, {""Column7"", type number}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & " #""Changed Type"""

path isn't being recognised as a variable in your code because it's inside quotes. You can either do something like this:

Code:

---

Path = "C:\Users\blah\Downloads\blah.pdf"
ActiveWorkbook.Queries.Add Name:="Table004 (Page 1) (2)", Formula:= _
"let" & Chr(13) & "" & Chr(10) & " Source = Pdf.Tables(File.Contents(""" & Path & """), [Implementation=""1.3""])," & Chr(13) & "" & Chr(10) & " Table004 = Source{[Id=""Table004""]}[Data]," & Chr(13) & "" & Chr(10) & " #""Changed Type"" = Table.TransformColumnTypes(Table004,{{""Column1"", type text}, {""Column2"", type number}, {""Column3"", type text}, {""Colu" & _
"mn4"", type text}, {""Column5"", type number}, {""Column6"", type text}, {""Column7"", type number}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & " #""Changed Type"""

---

or have the code include a path step so it looks like:
Attachment 29730
Where the code for that could be:

Code:

---

    ActiveWorkbook.Queries.Add Name:="Table004 (Page 1) (2)", Formula:= _
        "let" & Chr(13) & "" & Chr(10) & " path = ""C:\Users\blah\Downloads\blah.pdf""," & Chr(13) & "" & Chr(10) & " Source = Pdf.Tables(File.Contents(path), [Implementation=""1.3""])," & Chr(13) & "" & Chr(10) & " Table004 = Source{[Id=""Table004""]}[Data]," & Chr(13) & "" & Chr(10) & " #""Changed Type"" = Table.TransformColumnTypes(Table004,{{""Column1"", type text}, {""Column2"", type number}, {""Column3"", type text}, {""Column4"", type text}, {""Column5"", type number}, {""Colum" & _
        "n6"", type text}, {""Column7"", type number}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & " #""Changed Type"""

---

or some combination of the above.

BUT, you might be able to do without vba altogether since you can easily grab data from a sheet into Power Query; if you have a single cell named range, say fullPathAndName in the sheet, PQ can grab that data with the likes of:
Attachment 29731

Thanks that worked great

:thumb