Searching a .pdf using Excel 2010 VBA

[AverageAmy]

I am trying to open and search a .pdf using VBA in Excel. Ultimately, I want to search the pdf and put the first five characters following the found text from the .pdf in a cell in Excel. However, I am having issues with doing anything with the application. More details on the error I encounter at the end.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
I am using Excel 2010, Acrobat Standard 9, and am on Windows 7. The References I have active are Visual Basic for Applications; Microsoft Excel 14.0 Object Library; OLE Automation; Adobe Acrobat 9.0 Type Library; and Microsoft Office 14.0 Object Library. The code shown below does not work with this setup, BUT it does work with Excel 2000, Acrobat Standard 8, and Windows XP.<o></o>
<o></o>

Sub AcrobatFindText2() <o:p></o:p>
<o:p></o:p>
'variables<o:p></o:p>
Dim Resp 'For message box responses<o:p></o:p>
Dim gPDFPath As String<o:p></o:p>
Dim sText As String 'String to search for<o:p></o:p>
Dim sStr As String 'Message string<o:p></o:p>
Dim foundText As Integer 'Holds return value from "FindText" method<o:p></o:p>
<o:p></o:p>
'hard coding for a PDF to open, it can be changed when needed.<o:p></o:p>
gPDFPath = "C:\Users\Me\Documents\test.pdf"<o:p></o:p>
<o:p></o:p>
'Initialize Acrobat by creating App object<o:p></o:p>
Set gApp = CreateObject("AcroExch.App", "")<o:p></o:p>
gApp.Hide<o:p></o:p>
<o:p></o:p>
'Set AVDoc object<o:p></o:p>
Set gAvDoc = CreateObject("AcroExch.AVDoc")<o:p></o:p>
<o:p></o:p>
' open the PDF<o:p></o:p>
If gAvDoc.Open(gPDFPath, "") Then<o:p></o:p>
sText = "Designation"<o:p></o:p>
'FindText params: StringToSearchFor, caseSensitive (1 or 0), WholeWords (1 or 0), 'ResetSearchToBeginOfDocument (1 or 0)<o:p></o:p>
foundText = gAvDoc.FindText(sText, 1, 0, 1) 'Returns -1 if found, 0 otherwise<o:p></o:p>
<o:p></o:p>
Else ' if failed, show error message<o:p></o:p>
Resp = MsgBox("Cannot open" & gPDFPath, vbOKOnly)<o:p></o:p>
<o:p></o:p>
End If<o:p></o:p>
<o:p></o:p>
If foundText = -1 Then<o:p></o:p>
<o:p></o:p>
'compose a message<o:p></o:p>
sStr = "Found " & sText<o:p></o:p>
Resp = MsgBox(sStr, vbOKOnly)<o:p></o:p>
Else ' if failed, 'show error message<o:p></o:p>
Resp = MsgBox("Cannot find" & sText, vbOKOnly)<o:p></o:p>
End If<o:p></o:p>
<o:p></o:p>
gApp.Show<o:p></o:p>
gAvDoc.BringToFront<o:p></o:p>
<o:p></o:p>
End Sub<o:p></o:p>

<o></o>
When I try to run this on my main system (Excel 2010, Acrobat 9, Win7), I get a run-time error on

gApp.Hide

It will say: <o></o>
Run-time error ‘-2147319799 (8002801d)’:<o></o>
Automation error<o></o>
Library not registered.<o></o>
I went into Tools and selected all the References that I thought might remotely be related to Acrobat, and the same thing happened.<o></o>

If I comment that line out then run, Excel “stops working” and crashes at:

If gAvDoc.Open(gPDFPath, "") Then<o:p></o:p>

<o></o>
If I remove the If statement and go directly to:

foundText = gAvDoc.FindText(sText, 1, 0, 1)

I get the same run-time error shown above. <o></o>
<o></o>
Given this and the fact that the code works on my older system (Excel 2000, Acrobat 8, WinXP), I think this has something to do with the Adobe operations allowed in Excel 2010, however I have searched online and can’t find a solution.<o></o>
<o></o>
Can anyone out there help?

 

[AverageAmy]

Update: I copied my .xlsm file containing this code to another system using Excel 2010 and it works there.

 

[AverageAmy]

Another update: I have tried repairing my Excel 2010 installation and that didn't work. I can't figure out why the one version of Excel 2010 won't run this macro.

Any thoughts of where to look next?

 

[AverageAmy]

Got It!!
The problem was with Adobe Acrobat Standard 9. There are known issues between Acrobat 9.3 (and later) and Office 2010 where Acrobat does not show up in the ribbon.
See: (http://kb2.adobe.com/cps/843/cpsid_84399.html).
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o>My main computer where I was having problems running the macro had Acrobat Standard 9.5, whereas the other computer had Acrobat Standard 9.0. I deactivated and uninstalled 9.5, and then installed 9.0 and all works fine.</o>

 

[drdavidge]

Hey, thanks for the code above. Do you know how to use this to extract the next X number of characters after finding the string you are searching for?

 

[AverageAmy]

Hey, thanks for the code above. Do you know how to use this to extract the next X number of characters after finding the string you are searching for?

I don't really, but there is a document that tries to explain the commands for working with pdf documents. See Adobe Acrobat and VBA – An Introduction | Karl Heinz Kremer's Ramblings and linked documents: http://www.adobe.com/content/dam/Adobe/en/devnet/acrobat/pdfs/iac_developer_guide.pdf and http://www.adobe.com/content/dam/Adobe/en/devnet/acrobat/pdfs/iac_api_reference.pdf.

It's been a while since I worked with this, but I seem to remember searching online, finding snippets of code, and trial and error to finally get something that worked. Page 30 on the developer guide pdf and Page 44 on the API reference pdf seem to point you in the right direction, though. Please post back if you find something that works - I would love to hear it.