Using VBA Power Query to import PDF tables

[jharding]

I have a PDF that has three pages - each page has a header and detail and the last page has an additional footer.

I'd like to use Power Query to import the PDF tables into Excel

I ran the File ->Get data ->From File -> From PDF and it came back with 6 tables and three pages

I'd like to import the First table as my header, then tables 2, 4 and 6 as the detail and 6 will also have the footer.

I recorded a macro when I did this and it came back with the following code (PDF_Path substituted for hard path) for Table001

Sub PDF_PQ(PDF_Path As String)

    ActiveWorkbook.Queries.Add Name:="Table001 (Page 1)", Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Source = Pdf.Tables(File.Contents(""" & PDF_Path & """), [Implementation=""1.3""])," & Chr(13) & "" & Chr(10) & "    Table001 = Source{[Id=""Table001""]}[Data]," & Chr(13) & "" & Chr(10) & "    #""Changed Type"" = Table.TransformColumnTypes(Table001,{{""Column1"", type text}, {""Column2"", type text}})" & Chr(13) & "" & Chr(10) & "in" & _
        "" & Chr(13) & "" & Chr(10) & "    #""Changed Type"""
   
    ActiveWorkbook.Worksheets.Add
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""Table001 (Page 1)"";Extended Properties=""""" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [Table001 (Page 1)]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "Table001__Page_1"
        .Refresh BackgroundQuery:=False
    End With
    
End Sub

When I run this code from a button push event, it give me an error - "System Error & H80070057 (-2147024809). The Parameter is incorrect". I'm not sure what the issue is, it was generated from the system.

 

[jharding]

I think I figured out the error. I already had a query with the name I was trying to use.

I guess my question now is - how do I create queries for PDFs of different page counts?

Process is - user selects a PDF file. That file name gets passed to power query. PQ figures out how many pages and tables there are in the PDF. Then I would want the tables imported via queries.

The way I am doing it now, there is a lot of manual entry for Table name, etc.

 

[RoryA]

A gentle reminder:

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at:

Importing PDF into Excel using VBA

I have a PDF that has multiple tables (page header, detail, summary footer) and multiple pages. I am looking to import this PDF into Excel and keep the format with minor tweaking to the detail section. If I Export the PDF from Acrobat into Excel, the format does not come across. If I copy...

www.excelforum.com

There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.