Import data from PDF in to Excel

[earwig999]

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?

 

[earwig999]

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]

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:

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