Pdf tomacro

QuestionEdit

Is there a way to pull data from a PDF file and paste into Excel as part of VB script?

I have a macro that pulls data from multiple Excel workbooks and creates a summary in a separate workbook. Some of the data is in PDF form and I want to put that in as well.

AnswerEdit

Yes there are ways to pull data from PDF intousing VBA.

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) inVBA. (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.)