Exporting from PDF to Excel

[Hashiru]

Hi everyone,

I want to export data from pdf to excel. Here is what I have searched from the internet but the code could not open the pdf file.

Sub ReadAdobeFields()
    row_number = 1
        Dim AcrobatApplication As Acrobat.CAcroApp
        Dim AcrobatDocument As Acrobat.CAcroAVDoc
        Dim fcount As Long
        Dim sFieldName As String
        
        On Error Resume Next
        Set AcrobatApplication = CreateObject("AcroExch.App")
        Set AcrobatDocument = CreateObject("AcroExch.AVDoc")
        
        If AcrobatDocument.Open("C:\Users\UserName\Documents\Audit\f1040.pdf", "") Then
            AcrobatApplication.Show
            Set AcroForm = CreateObject("AFormAut.App")
            Set Fields = AcroForm.Fields
            fcount = Fields.Count ' Number of fields
            
                For Each field In Fields
                row_number = row_number + 1
                    sFieldName = field.Name
                    'MsgBox sFieldName
                    
                    Sheet1.Range("B" & row_number) = field.Name
                    Sheet1.Range("C" & row_number) = field.Value
                    Sheet1.Range("D" & row_number) = field.Style

                  Next field
            Else
            MsgBox "Failure"
            End If
            
            AcrobatApplication.Exit
            Set AcrobatApplication = Nothing
            Set AcrobatDocument = Nothing
            Set field = Nothing
            Set Fields = Nothing
            Set AcroForm = Nothing
            
End Sub

The pdf file did not open with the code: AcrobatApplication.Show and nothing transfer to excel

 

[Macropod]

The code you posted requires you to have Adobe Acrobat Pro installed, a VBA reference to the Adobe Acrobat object model and for the PDF file to use Adobe's fields.

 

[Hashiru]

Hi Macropod,

Thanks, I have made the reference to adobe acrobat object model in MS Excel 2010 and I will try to install the adobe pro on my computer and try this code for workability. However, I have a windows 10 on my laptop with MS Excel 2016 but I cannot locate the adobe pro reference Library. Has there been a change in the name? Please help because I am using Excel 2016 at work and has where I can use the Adobe pro. Thanks

 

[Macropod]

The Excel version is of no consequence and, without Adobe Acrobat Pro installed, you wouldn't be able to set a reference to the Adobe Acrobat # Type Library (where # is the version). Under Tools|References, this may initially be listed as nothing more than Acrobat.

 

[Hashiru]

Hi Macropod,

Thanks for the insight, I have no knowledge of Acrobat but trying to learn it as I want to control some pdf files from Excel. I have installed Adobe Acrobat Pro DC and I have made reference to Adobe Acrobat 10.0 Type Library. In a trial I used IRS Form 1040 located in https://www.irs.gov/pub/irs-pdf/f1040.pdf. However, fcount is reading as zero and no field was transferred to the excel sheet1

Thanks

 

[Macropod]

When I ran your code, fcount returned 273.

FWIW, in the code you posted:
• Sheet1.Range("B" & row_number) = field.Name is generally empty, and
• Sheet1.Range("D" & row_number) = field.Style generates errors. You can overcome that with If field.Value <> "" Then Sheet1.Range("D" & row_number) = field.Style
• it would also help if you declared all your variables.

 

[Hashiru]

Thanks Macropod,

Your comments gave me more insight into what I am trying to achieve. However, I tried it on my work pdf and it did not work. Is it because the pdf files were generated by a software (SAP, Isolved, SAGE 50) and not developed in the lifeCycle or so within Adobe suite? I will be working with these types of files. Please help.

 

[Macropod]

As I said, I was able to read the PDF's fields (after making a few minor changes to your code to stop it crashing), so the software that generated it isn't (and shouldn't be) of any consequence. Are you sure you're even referencing the PDF correctly? I doubt you have a file that can be found via:
"C:\Users\UserName\Documents\Audit\f1040.pdf"
Doing so would require having a user named 'UserName'!

 

[humdingaling]

dont know what style does or is meant to be ...however if it is not really needed

i was able to replicate results straight out of Acrobat itself (I'm using Adobe acrobat X Standard)
Go to side panel>forms>more form options>Manage Form Data>Merge data files into spreadsheet
click add files..select the files

exported file is in csv format and runs across instead of downwards
you can simple transpose it if you want it running down

 

[Macropod]

dont know what style does or is meant to be ...however if it is not really needed

You might have an idea if you were trying to extract the data programmatically...

i was able to replicate results straight out of Acrobat itself (I'm using Adobe acrobat X Standard)
Go to side panel>forms>more form options>Manage Form Data>Merge data files into spreadsheet
click add files..select the files

exported file is in csv format and runs across instead of downwards
you can simple transpose it if you want it running down

That's not much use if the idea is to programmatically import the data into Excel. The code under discussion is already quite sufficient for that. All you're suggesting is that the user doing something manually that is intended to be done programmatically - not very helpful!