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

 

[humdingaling]

while you interpret the question to be
"programmatically import the data into Excel"

Going by the first line the OP said

I want to export data from pdf to excel.

I'm merely offering an means to get what they wanted as the outcome

yes there is some clicking involved but its a whole lot easier to do and understand if they OP is in affluent in VBA....even if the OP is, anyone else finding this thread thru the internet may not be

Here is what I have searched from the internet but the code could not open the pdf file

as the OP did not write the code themselves
i ran the code and styles did not provide me with anything i deemed useful which is why i put forward the question about styles and whether did they did indeed require it...

 

[Macropod]

Since the OP posted some code with his first post and explicitly sought help with it, I think it is self-evident a programmatic solution is envisaged. Your posts do not contribute to solving the issue under discussion.

styles did not provide me with anything i deemed useful

Whether you regarded it useful is irrelevant.

 

[Hashiru]

Thanks Macropod and humdingaling. Macropod I have the USerName per the name on my computer and the file path and name. I have been able to use the code and got the same answer as yours. However, this was for test case but I was unable to replicate the same with my office work files. Since I now have Adobe Pro, I tried to edit the file manually to see the difference in the file but I cannot make sense of it. Any help on how to proceed

 

[Macropod]

Instead of using:
"C:\Users\UserName\Documents\Audit\f1040.pdf"
use:
"C:\Users\" & Environ("UserName") & "\Documents\Audit\f1040.pdf"
That way, you won't have to change the path for every user.

 

[Hashiru]

I am unable to replicate this code with my work files. I tried to use edit with pdf to see how it would look. I also go to the excel IDE Editor and then Object browser and filter for Acrobat. I cannot find the AcroForm Object. Can anyone help. Also I click on the AcroApp Object on the classes and CloseAllDocs on the member of "AcroApp and click on the help button but I could not get any help

Thanks.

 

[Macropod]

I am unable to replicate this code with my work files. I tried to use edit with pdf to see how it would look. I also go to the excel IDE Editor and then Object browser and filter for Acrobat. I cannot find the AcroForm Object. Can anyone help. Also I click on the AcroApp Object on the classes and CloseAllDocs on the member of "AcroApp and click on the help button but I could not get any help

As I said in posts 2 & 4, you need to set a reference to the Adobe Acrobat object model. There is no need to find or reference the AcroForm Object. What you should have is something like:

Sub ReadAdobeFields()
Dim AcrobatApplication As Acrobat.CAcroApp
Dim AcrobatDocument As Acrobat.CAcroAVDoc
Dim AcroForm As Object
Dim Fields As Object
Dim Field As Object
Dim row_number As Long, fcount As Long
Dim sFieldName As String
row_number = 1

On Error Resume Next
...