PDA

View Full Version : Convert PDF data to Excel



imrandelhi
04-15-2014, 01:32 AM
Hi Team,

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


Regards
Imran

imrandelhi
04-15-2014, 04:26 AM
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
04-15-2014, 08:50 PM
please revert

ashleyuk1984
04-15-2014, 11:31 PM
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
10-03-2015, 01:44 AM
Hi, imrandelhi.

Thanks for your nice sharing. As for myself, I have seldom tried to convert pdf (http://www.pqscan.com/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 (http://www.pqscan.com/pdf-to-image/) process? Do you have experience about it? Any suggestion will be appreciated. Thanks in advance.





Best regards,
Lee

SamT
10-03-2015, 07:06 AM
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.