Accessing another application through Excel

[bzsurf03]

Using VBA, underneath an Excel workbook, how can I open another application, pull data off and continue processing in Excel.

I want to open a specific Adobe Acrobat file, do a copy and paste data into the Excel workbook I was already working in.

Thanks in advance. Let me know what other information I can provide.

 

[TomCologne]

The code below opens a *.pdf from a command button on the sheet.

Private Sub CommandButton1_Click()
Dim strFile As String, strShell As String
Dim openFile

strShell = "C:\Program Files\Adobe\Acrobat 7.0\Reader\AcroRd32.exe " 'Leave space at the end!
strFile = """C:\Documents and Settings\Administrator\Desktop\ThePDFfile.pdf"""

openFile = Shell(strShell & strFile, 1) '(1 = vbNormalFocus)

End Sub

TomCologne

 

[bzsurf03]

Great. Thanks Tom. This should be a good starting point.

 

[bzsurf03]

Anyone know of any good reference material on this. I have two *.pdf files open and have no clue on how to perform any logic on them with VBA. I basically need to copy all of the data in the two *.pdf files into separate worksheets, where the real processing begins. Thanks for any guidance.

 

[PHV]

Aren't PDF like scanned images instead of text files ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[bzsurf03]

Yes. But images that still can be copy and pasted into excel.

I want to do a "select all" and "copy" on the *.pdf and then "paste" to Excel.

 

[kspurr]

PDFs can be built from a number of sources (live text, scanned files which are raster images, or raster images). In the case that you can copy and paste, there is actual text in the file that you can use. My guess is that you'd be better off using a shell script like: pdftotext to convert the pdf to a more easily processed text file. Most of my experience doing this has been in linux, but you should be able to get a free pdftotext precompiled Windows binary from:

pdftotext

Good luck,

Kevin

 

[bzsurf03]

Copy and Paste is a common Windows function. I would think that I could do this without third-party software. Third party software is not an option in this case. What is the syntax for using pdftotext? I have no experience with using VBA with apps outside of Excel.

 

[PHV]

You may have a look at tsuji's answer ([2a]):

http://www.tek-tips.com/viewthread.cfm?qid=904320

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[TomCologne]

Kevin's tip is good!

bzsurf03, don't forget that you're dealing with pdf. Copy & paste may be a common Windows function but so is SaveAs and you still can't use it to save a *.pdf that is open in IE.

The choice of switches for AcroRd32.exe seems to be pretty limited, I could not find one to select & copy.

There is another converter program, PDF2Text Pilot 1.10 (

http://www.colorpilot.com/pdf2text.html)

and I tried the demo version without sucess on several documents which is probably due to the fact that I've got Reader 7.0.

I just downloaded a Win version of pdftotext (

http://www.foolabs.com/xpdf/download.html)

but haven't tried it yet.

I don't know if invoking an API call could solve your issue.

Another option you might want to try is saving the document as *.txt but I guess that would need to be done manually, too.


TomCologne

 

[bzsurf03]

What about the SendKeys Method?

 

[PHV]

What about the SendKeys Method
Highly unreliable even with the AppActivate method.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[bzsurf03]

How do you programatically close the shell - in this case an Acrobat application - once you are finished with it?