VBA - open PDF file into Excel -

[sara121]

Hi Everyone,

I recorded a macro for opening PDF file into Excel using the "Data --> get data --> from PDF file.

The macro works great with the file path hardcoded.

 ActiveWorkbook.Queries.Add Name:="Table001", Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Source = Pdf.Tables(File.Contents(""[COLOR=rgb(226, 80, 65)]C:\PDF Folder\Invoices.pdf[/COLOR]""), [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"",{{""Expected" & _
        " arrival"", type date}, {""Advice date"", type text}, {""Customer name"", type text}, {""Column4"", type text}, {""Column5"", type text}, {""Account"", Int64.Type}, {""Customer"", type text}, {""Amount"", Currency.Type}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Changed Type"""
    ActiveWorkbook.Worksheets.Add

I tried to add script before this to either prompt user to browse for file location or reference to a cell for the PDF file. It stopped working.

For example: I placed the same file path in the Control sheet in cell A1 and have the macro call the "file_path". It returned an error saying: "the supplied file path must be a valid absolute path"

Dim Control_sh As Worksheet
Set Control_sh = ThisWorkbook.Sheets("CONTROL")

Dim pdf_path As String
pdf_path = Control_sh.Range("A1").Value

    ActiveWorkbook.Queries.Add Name:="Table001", Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Source = Pdf.Tables(File.Contents(""[COLOR=rgb(209, 72, 65)]pdf_path[/COLOR]""), 
[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"",{{""Expected" & _
        " arrival"", type date}, {""Advice date"", type text}, {""Customer name"", type text}, {""Column4"", type text}, {""Column5"", type text}, {""Account"", Int64.Type}, {""Customer"", type text}, {""Amount"", Currency.Type}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Changed Type"""
    ActiveWorkbook.Worksheets.Add

I tried to use the GetOpenFileName function to prompt user to browse for the file and pass the file location to the File.Content(" ") . It did not work either.

Dim FileLocation As String    
FileLocation = Application.GetOpenFilename
If FileLocation = "False" Then
Beep
Exit Sub
End If

Can someone shed some lights as to how to pass the file path?

Thanks.

 

[Alex Blakenburg]

Does this help:

    ActiveWorkbook.Queries.Add Name:="Page001", Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Source = Pdf.Tables(File.Contents(""" & pdf_path & """), [Implementation=""1.3""])," & Chr(13) & "" & Chr(10) & "    Page1 = Source{[Id=""Page001""]}[Data]," & Chr(13) & "" & Chr(10) & "    #""Changed Type"" = Table.TransformColumnTypes(Page1,{{""Column1"", type text}, {""Column2"", type text}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Changed Type"""

 

[sara121]

wow.. it works! Thanks so much! @Alex Blakenburg !! You saved me ton of hours. appreciated.

 

[Alex Blakenburg]

You're welcome. Glad I could help.