Results 1 to 3 of 3

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

  1. #1
    New Member
    Join Date
    Jan 2013
    Posts
    37
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #2
    New Member
    Join Date
    Jan 2013
    Posts
    37
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #3
    Board Regular
    Join Date
    Oct 2007
    Posts
    5,702
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    2 Thread(s)

    Default 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

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •