Button to Open "Get Data from PDF" Function

[trose6382]

I am trying to find a way to create a button that will upload data from a pdf file. I am familiar with the "Data>Get Data>From file>From pdf" way of importing the data and am simply trying to create a button that allows the user to open the display that allows them to select a pdf file to upload. I'm trying to save the user from having to click 4 times. We upload a lot of pdf files and eliminating these 4 clicks would be helpful.

Ideally, the code would also be able to finish the upload process once a file is selected. All of the pdf files are formatted the same so that once a pdf file is selected, Table003 (Page 1) would always be the table we want to upload. Also, it would be great to be able to have it "Load to" a specific cell rather than to a new worksheet.

Any suggestions on where to start with the VBA macro? Is there an easy way to see basic VBA functions so I can essentially create shortcuts to different excel functions using buttons?

I have a basic understanding of VBA but I'm still a beginner.

Any help is welcome. Thank you!

 

[John_w]

Welcome to MrExcel forums.

Any suggestions on where to start with the VBA macro?

Start by recording a macro whilst getting data from one of the PDF files. The generated code can then be modified to browse for a file and import the table data from it. If you post the macro I can help you with this.

 

[trose6382]

This is what I was able to pull for the recording. Should it just be a matter of adding this macro to a button along with the FileDialog?

Sub Upload_PDF_Data()
'
' Upload_PDF_Data Macro
''
    ActiveWorkbook.Queries.Add Name:="Table003 (Page 1)", Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Source = Pdf.Tables(File.Contents(""[FILE_PATH_HERE].pdf""), [Implementation=""1.3""])," & Chr(13) & "" & Chr(10) & "    Table003 = Source{[Id=""Table003""]}[Data]," & Chr(13) & "" & Chr(10) & "    #""Promoted Headers"" = Table.PromoteHeaders(Table003, [PromoteAllScalars=true])," & Chr(13) & "" & Chr(10) & "    #""Changed Type"" = Table.TransformColumnTypes(#""" & _
        "Promoted Headers"",{{""Peak#(lf)#"", type text}, {""Component#(lf)Name"", type text}, {""Time#(lf)[min]"", type number}, {""Area#(lf)[uV*sec]"", type number}, {""Raw#(lf)Amount"", type number}, {""Mass %"", type number}, {""Corrected#(lf)Mass %"", type number}, {""Amount#(lf)(mg/g)"", type number}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Changed Type"""
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""Table003 (Page 1)"";Extended Properties=""""" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [Table003 (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 = "Table003__Page_1"
        .Refresh BackgroundQuery:=False
    End With
End Sub

 

[John_w]

Thanks. Here is your macro modified to allow the user to browse and select the PDF and then import it into Excel using Power Query. We might need to tweak the queryName value to make it unique.

Option Explicit

Sub Upload_PDF_Data()
    
    ' Upload_PDF_Data Macro
    
    Dim PDFtableName As String, tableId As String
    Dim PDFfile As Variant
    Dim loadToCell As Range
    Dim queryName As String
    
    'The name of the table in the selected PDF which will be imported into Excel
    
    PDFtableName = "Table003 (Page 1)"
    
    'The cell where the import will begin - the destination cell of the query table created by this macro
    
    Set loadToCell = ActiveSheet.Range("A1")
        
    PDFfile = Application.GetOpenFilename(FileFilter:="PDF Files (*.pdf), *.pdf", MultiSelect:=False, Title:="Select PDF to upload")
    If PDFfile = False Then Exit Sub 'Cancel clicked
    
    queryName = GetQueryName(CStr(PDFfile))
    tableId = Left(PDFtableName, InStr(PDFtableName, " ") - 1)
    
    ActiveWorkbook.Queries.Add Name:=queryName, Formula:= _
        "let" & vbCrLf & _
        "    Source = Pdf.Tables(File.Contents(""" & PDFfile & """), [Implementation=""1.3""])," & vbCrLf & _
        "    Table003 = Source{[Id=""" & tableId & """]}[Data]," & vbCrLf & _
        "    #""Promoted Headers"" = Table.PromoteHeaders(Table003, [PromoteAllScalars=true])," & vbCrLf & _
        "    #""Changed Type"" = Table.TransformColumnTypes(#""" & "Promoted Headers"",{{""Peak#(lf)#"", type text}, {""Component#(lf)Name"", type text}, {""Time#(lf)[min]"", type number}, {""Area#(lf)[uV*sec]"", type number}, {""Raw#(lf)Amount"", type number}, {""Mass %"", type number}, {""Corrected#(lf)Mass %"", type number}, {""Amount#(lf)(mg/g)"", type number}})" & vbCrLf & _
        "in" & vbCrLf & _
        "    #""Changed Type"""
    
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:="OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""" & queryName & """;Extended Properties=""""", _
            Destination:=loadToCell).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [" & queryName & "]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = queryName
        .Refresh BackgroundQuery:=False
    End With
    
End Sub


Private Function GetQueryName(PDFfullName As String) As String
    'Convert PDF file name to a query name
    GetQueryName = Mid(PDFfullName, InStrRev(PDFfullName, "\") + 1)
    GetQueryName = Left(GetQueryName, InStrRev(GetQueryName, ".") - 1)
    GetQueryName = Replace(GetQueryName, " ", "_")
    GetQueryName = Replace(GetQueryName, "-", "_")
End Function

 

[trose6382]

I just tried it and it works great! Thank you so much! Incredible that you can do that so quickly. Can't wait until I'm as adept. Thanks again!

 

[vba programming]

Hi

Thanks. Here is your macro modified to allow the user to browse and select the PDF and then import it into Excel using Power Query. We might need to tweak the queryName value to make it unique.

Option Explicit

Sub Upload_PDF_Data()
   
    ' Upload_PDF_Data Macro
   
    Dim PDFtableName As String, tableId As String
    Dim PDFfile As Variant
    Dim loadToCell As Range
    Dim queryName As String
   
    'The name of the table in the selected PDF which will be imported into Excel
   
    PDFtableName = "Table003 (Page 1)"
   
    'The cell where the import will begin - the destination cell of the query table created by this macro
   
    Set loadToCell = ActiveSheet.Range("A1")
       
    PDFfile = Application.GetOpenFilename(FileFilter:="PDF Files (*.pdf), *.pdf", MultiSelect:=False, Title:="Select PDF to upload")
    If PDFfile = False Then Exit Sub 'Cancel clicked
   
    queryName = GetQueryName(CStr(PDFfile))
    tableId = Left(PDFtableName, InStr(PDFtableName, " ") - 1)
   
    ActiveWorkbook.Queries.Add Name:=queryName, Formula:= _
        "let" & vbCrLf & _
        "    Source = Pdf.Tables(File.Contents(""" & PDFfile & """), [Implementation=""1.3""])," & vbCrLf & _
        "    Table003 = Source{[Id=""" & tableId & """]}[Data]," & vbCrLf & _
        "    #""Promoted Headers"" = Table.PromoteHeaders(Table003, [PromoteAllScalars=true])," & vbCrLf & _
        "    #""Changed Type"" = Table.TransformColumnTypes(#""" & "Promoted Headers"",{{""Peak#(lf)#"", type text}, {""Component#(lf)Name"", type text}, {""Time#(lf)[min]"", type number}, {""Area#(lf)[uV*sec]"", type number}, {""Raw#(lf)Amount"", type number}, {""Mass %"", type number}, {""Corrected#(lf)Mass %"", type number}, {""Amount#(lf)(mg/g)"", type number}})" & vbCrLf & _
        "in" & vbCrLf & _
        "    #""Changed Type"""
   
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:="OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""" & queryName & """;Extended Properties=""""", _
            Destination:=loadToCell).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [" & queryName & "]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = queryName
        .Refresh BackgroundQuery:=False
    End With
   
End Sub


Private Function GetQueryName(PDFfullName As String) As String
    'Convert PDF file name to a query name
    GetQueryName = Mid(PDFfullName, InStrRev(PDFfullName, "\") + 1)
    GetQueryName = Left(GetQueryName, InStrRev(GetQueryName, ".") - 1)
    GetQueryName = Replace(GetQueryName, " ", "_")
    GetQueryName = Replace(GetQueryName, "-", "_")
End Function

Hi! Please I need your help, can you modify this code for me since I want to do same here but I don’t understand the codes because I am a beginner

The name of my table is “unload” and I want the pdf to be imported cell A3 so that the next one can be at A4 when i trigger import button. My sheet is called “import pdf”
@John_w

 

[John_w]

The name of my table is “unload” and I want the pdf to be imported cell A3 so that the next one can be at A4 when i trigger import button. My sheet is called “import pdf”

You should start a new thread, but maybe these changes, replacing the original lines, will work:

    PDFtableName = "unload"


    With Worksheets("import pdf")
        Set loadToCell = .Cells(.Rows.Count, "A").End(xlUp)
        Debug.Print loadToCell.Address
        If loadToCell.Row <= 2 Then
            Set loadToCell = .Range("A3")
        Else
            Set loadToCell = loadToCell.Offset(1)
        End If
    End With


    tableId = PDFtableName

Please start a new thread if those changes don't work.