# Thread: Import data from PDF in to Excel Thanks: 0 Likes: 0

1. ## Import data from PDF in to Excel

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?

2. ## Re: Import data from PDF in to Excel

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?

3. ## Re: Import data from PDF in to Excel

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