Convert PDF data to Excel

[imrandelhi]

Hi Team,

Please confirm how we can convert pdf data to excel through vba.Thanks


Regards
Imran

[imrandelhi]

Hey Team,

I m tring to using below mentioned coding for convert data pdf to excel but getting error so please check & confirm where is the problem in this -

Do one more thing in below coding i have to mentioned the path which folder pdf file is save & available so i want its take active pdf file.

Thanks

Sub CommandButton1_Click()
 'Declare Variable(s)
Dim appAA As Acrobat.CAcroApp, docPDF As Acrobat.CAcroPDDoc
Dim strFileName As String, intNOP As Integer, arrI As Variant
Dim intC As Integer, intR As Integer, intBeg As Integer, intEnd As Integer
'Initialize Variables
Set appAA = CreateObject("AcroExch.App"): Set docPDF = CreateObject("AcroExch.PDDoc")
'Set PDF FileName
'strFileName = "C:\Documents and Settings\Michael Palkovitz\My Documents\Test\EC Operations Budget February FY13.pdf"
strFileName = "C:\Program Files (x86)\Adobe\Reader 11.0\Reader\AcroRd32.exe C:\Users\santoshk\Downloads\BNG.pdf"
'Read PDF File
docPDF.Open (strFileName)
'Extract Number of Pages From PDF File
 intNOP = docPDF.GetNumPages
'Select First Data Cell
Range("A1").Select
'Open PDF File
ActiveWorkbook.FollowHyperlink strFileName, , True
'Loop Through All PDF File Pages
For intC = 1 To intNOP
'Go To Page Number
SendKeys ("+^n" & intC & "{ENTER}")
'Select All Data In The PDF File's Active Page
SendKeys ("^a"), True
'Right-Click Mouse
SendKeys ("+{F10}"), True
'Copy Data As Table
SendKeys ("c"), True
'Minimize Adobe Window
SendKeys ("%n"), True
'Paste Data In This Workbook's Worksheet
ActiveSheet.Paste
'Select Next Paste Cell
Range("A" & Range("A1").SpecialCells(xlLastCell).Row + 2).Select
'Maximize Adobe Window
SendKeys ("%x")
Next intC
'Close Adobe File and Window
SendKeys ("^w"), True
'Empty Object Variables
Set appAA = Nothing: Set docPDF = Nothing
'Select First Cell
Range("A1").Select
End Sub

[imrandelhi]

please revert

[ashleyuk1984]

I've successfully converted PDF's into workable formats for my job, and then used conditions to extract the correct data that I want. Literally converting a manual 45 minute job into a automatic 30 second job

To get the PDF into Excel, I firstly open the PDF, select all, and then copy and paste that into Excel. I then use Text to Columns (by spaces) to split all the data up into separate columns.
That should probably be a good start.

If this isn't what your looking for... Then I suggest you seek a paid program to do the job for you. Some are good and keep the same format once converted to xlsx.. but some are not. It depends on the PDF itself and the program that your using.

[arronlee]

Hi, imrandelhi.

Thanks for your nice sharing. As for myself, I have seldom tried to convert pdf data to excel through vba. I wonder have you ever worked it out? How to deal with those code? Do I need another 3rd party manual toolkit to help with PDF conversion process? Do you have experience about it? Any suggestion will be appreciated. Thanks in advance.





Best regards,
Lee

[SamT]

I know nothing about Adobe, but this has corrected VBA and Excel logic and some suggestions to try:

Option Explicit

Sub CommandButton1_Click()
     'Declare Variable(s)
    Dim appAA As Acrobat.CAcroApp, docPDF As Acrobat.CAcroPDDoc
    Dim strFileName As String
    Dim intC As Integer
    Dim ASht As Worksheet, PasteCell As Range
    
     'Initialize Variables
    Set ASht = ActiveWorksheet
    Set PasteCell = ASht.Range("A1")
    Set appAA = CreateObject("AcroExch.App")
    Set docPDF = CreateObject("AcroExch.PDDoc")
     'Set PDF FileName
     'strFileName = "C:\Documents and Settings\Michael Palkovitz\My Documents\Test\EC Operations Budget February FY13.pdf"
    strFileName = "C:\Users\santoshk\Downloads\BNG.pdf"
    
 With appAA
    .Activate '??????????????
    'Open file with Adobe Acrobat '<<<<<<<<<<<<<<<<
    docPDF.Open (strFileName) '????????????????
    '.docPDF.Open (strFileName) '? Note dot prefix
    'Set docPDF = .Open(strFileName) '?????????????
     'Loop Through All PDF File Pages
    docPDF.Open (strFileName) '????????????????
    '.docPDF.Open (strFileName) '? Note dot prefix
    For intC = 1 To docPDF.GetNumPages '? Try Dot prefix
         'Go To Page Number
        SendKeys ("+^n" & intC & "{ENTER}")
         'Select All Data In The PDF File's Active Page
        SendKeys ("^a"), True
         'Right-Click Mouse
        SendKeys ("+{F10}"), True
         'Copy Data As Table
        SendKeys ("c"), True
         'Minimize Adobe Window
        'SendKeys ("%n"), True
         'Paste Data In This Workbook's Worksheet
        ASht.PasteCell.Paste
         'Select Next Paste Cell
        Set PasteCell = ASht.Range("A" & Rows.Count).End(xlUp).Offset(2) '<<<<<<<<<<
         'Maximize Adobe Window
        'SendKeys ("%x")
    Next intC
     'Close Adobe File and Window
    SendKeys ("^w"), True
 End With
    
     'Empty Object Variables
    Set appAA = Nothing: Set docPDF = Nothing
     'Select First Cell
    ASht.Range("A1").Select
End Sub

When you solve this, Please post the solution here for us, that we may learn from you as a reward for our help.