Microsoft Excel - Copy all data of .pdf file to excel (macro)

Asked By farrukh on 11-Feb-11 02:25 AM
Hi ALL


Is it possible that  we get the data of .pdf file to excel file,like in code below which copy data of  word to excel
excel....

help would be appreciated......


Sub GET_DATA_WORD()
  Dim MyWd As Object
  Set MyWd = GetObject("D:\abc.doc")

  MyWd.ActiveWindow.Selection.WholeStory
  MyWd.ActiveWindow.Selection.Copy
 
  Sheet1.Range("A4").PasteSpecial xlPasteValues
End Sub

Thanks
Farrukh

Anoop S replied to farrukh on 11-Feb-11 04:09 AM
The simplest solution is to use VBA "Shell" method to open Acrobat together with "SendKeys" method to select all, copy, and paste into Excel. After getting the data into Excel, you will be able to manipulate them to your needs.

Refer to the code below.

Sub Shell_Copy_Paste()
    
   Dim o As Variant
   Dim wkSheet As Worksheet
    
   Set wkSheet = ActiveSheet
    
   o = Shell("C:\Program Files\Adobe\Acrobat 8.0\Acrobat\Acrobat.exe C:\Users\Victor Lan\Documents\sample.pdf", vbNormalFocus)
    
   Application.Wait (Now + TimeSerial(0, 0, 2)) 'Wait for Acrobat to load
    
   SendKeys "^a"   'Select All
   SendKeys "^c"   'Copy
   SendKeys "%{F4}"    'Close shell application
    
   wkSheet.Range("B5").Select
   SendKeys "^v"   'Paste
    
End Sub


A more complex method is to reference "Acrobat Acrobat 8.0 Type Library" (acrobat.tlb) in Excel VBA. (This assumes you have Acrobat installed.) More information can be found at Adobe Acrobat Developer Center, http://www.adobe.com/devnet/acrobat/.

Adobe provided an example (with minimal explanation) on how to extract selected area of text using this method.

http://support.adobe.com/devsup/devsup.nsf/docs/51533.htm (How To: Extracting Text using Visual Basic.)
farrukh replied to Anoop S on 11-Feb-11 04:50 AM
Dear Anoop,


Thank you so much dear for your help the code works prefectly.....



Thanks
Farrukh
farrukh replied to Anoop S on 11-Feb-11 05:19 AM
Dear Annop

Just a litle amendent required i have create a same format of pdf in the excel sheet. i want to copy paste the whole pdf file to excel specifed area in(excel) as pdf has

This macro is pasting the value in one column it is quite difficult to look the values help Pleasez


Thanks
Farrukh


Anoop S replied to farrukh on 11-Feb-11 05:38 AM
You can change the range here
wkSheet.Range("B5").Select -> wkSheet.Range("A1:C12").Select
farrukh replied to Anoop S on 11-Feb-11 05:43 AM
Anoop

Thank alot all have done .....


Thanks and Regards
Farrukh
Anoop S replied to farrukh on 11-Feb-11 05:43 AM
Sorry it will not work,
farrukh replied to Anoop S on 11-Feb-11 05:46 AM
Sorry

Then what can i do?


Thanks
Farrukh
Anoop S replied to farrukh on 11-Feb-11 06:05 AM
Actually it will paste data to single column only, if you want to be in column base then you can try with some 3rd party tools like
http://www.pdftoexcelonline.com/
http://www.mrexcel.com/tip107.shtm ->Using Able2Extract not free version
farrukh replied to Anoop S on 11-Feb-11 08:06 AM
Thanks Dear alot

i was prefering macro because we are automating fileds data tranfer to live db.

Actaully if pdf file fixed in excel format in multiple column e.g if pdf file template fix to excel A1 to AE27 then we can go for other macro like excel to csv then application....

if there is away to fix in data in excel template ther would be the huge achivement...


Thank your work is appreciated .

farrukh