Import data from PDF in to Excel

earwig999

New Member
Joined
Jan 11, 2013
Messages
37
I am trying to create a Macro in VBA to Import data from either single or multiple PDF forms in to an Excel workbook. Adding each form as a new row in the sheet.

I am thinking of a Button with the macro attached on the Sheet, which then identifies a folder containing the forms. It then pulls the data from the PDFs and adds each form as a new row to the sheet.

I would like to be able to also spot/prevent duplicate imports if possible.

What's the best way of achieving this?
 

Some videos you may like

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

earwig999

New Member
Joined
Jan 11, 2013
Messages
37
I guess my original request was a little vague, so will try to add some further detail -

I want a USER to complete a .PDF form (I can't use the Excel Forms function as it has too many fields)
Then drop the completed form in to a folder for IMPORT
Trigger an IMPORT of all forms within this folder, which extracts the data and adds it to the Excel sheet
The IMPORTED files can then be moved to another folder.

Is this even possible?
 

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,222
This is possible, but only if Adobe Acrobat Professional is installed, not the free Reader.

This macro shows the basic code to import the fields from one PDF form file:

Code:
Public Sub PDF_Form_Fields()

    Dim AcroApp As Object
    Dim PDdoc As Object
    Dim JSO As Object
    Dim field As Object
    Dim i As Long, pages As Variant, p As Long
    Dim fields As Variant, fieldName As Variant
    Dim PDFfile As String
           
    Set AcroApp = Nothing
    On Error Resume Next
    Set AcroApp = CreateObject("AcroExch.App")
    If AcroApp Is Nothing Then
        MsgBox "Acrobat Professional must be installed to use the Acrobat API in this macro", vbCritical
        Exit Sub
    End If
    On Error GoTo 0
    
    PDFfile = "C:\path\to\Your PDF Form.pdf"   'Change this

    If Dir(PDFfile) = vbNullString Then
        MsgBox "The PDF file '" & PDFfile & "' doesn't exist"
        Exit Sub
    End If
    
    Set PDdoc = CreateObject("AcroExch.PDDoc")
    
    'Open the PDF form document
    
    PDdoc.Open PDFfile
    
    'Get the document's JavaScript interface
    
    Set JSO = PDdoc.GetJSObject
   
    'Output fields to Immediate window
    
    For i = 0 To JSO.numFields - 1
        Set field = JSO.getField(JSO.getNthFieldName(i))
        pages = field.Page
        If (VarType(pages) And vbArray) = vbArray Then
            For p = 0 To UBound(pages)
                Debug.Print i; field.Name, field.Value, field.Type, pages(p)
            Next
        Else
            Debug.Print i; field.Name, field.Value, field.Type, field.Page
        End If
    Next
    
    PDdoc.Close
    
    Set JSO = Nothing
    Set PDdoc = Nothing
    Set AcroApp = Nothing
    
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,096,287
Messages
5,449,459
Members
405,566
Latest member
JeIIyfish

This Week's Hot Topics

Top