Opening and populating a PDF form with VBA code in Access 2007

[christian_bahnsen]

I had posted this in Acrobat Windows.  It was suggested this (and the SDK forum) may be a better forum ...

I'm trying to load and then populate a PDF form programmatically using Access/VBA.  I'm patterning this after code that worked fine in Acrobat 5.0 but is throwing errors in Acrobat 9.  We're using Access 2007 on Windows XP and Vista computers.

The Access project has a reference to Adobe Acrobat 9.0 Type Library.  Attached is a jpg showing all the references in the Access project.

The error "Object variable not set (Error 91)" is happening with this statement:
Set PDDoc = AVDoc.GetPDDoc

Here is the code I'm trying to use.  I've always been a little fuzzy exactly which objects need to be created and the sequence they need to be created in.  Once I got it working in Acrobat 5 I left it alone.

    Dim WshShell As Object
    Dim myApp As Acrobat.AcroApp
    Dim AVDoc As Acrobat.AcroAVDoc
    Dim PDDoc As Acrobat.AcroPDDoc
    Dim PauseTime, Start

    Set WshShell = CreateObject("Wscript.Shell")
    ' run the Acrobat application within that shell and pass it a document name
    WshShell.Run "Acrobat.exe C:\Users\Christian\Documents\data\dist5\dist05_face_only_nh.pdf"
   
   
    '// Set/Get Acrobat Objects
    ' create an automation object that references the active copy of Acrobat
    Set myApp = CreateObject("AcroExch.App")
    ' reference the acrobat document we loaded above
    'Set AVDoc = CreateObject("AcroExch.AVDoc")
    Set AVDoc = myApp.GetActiveDoc()
   
    ' this apparently runs some method available to AVDOC
    Set PDDoc = AVDoc.GetPDDoc      '<------------------ THIS IS THE LINE THROWING THE ERROR
    ' this is how you reference the JSObject
    Set jso = PDDoc.GetJSObject
   
    ' let's clear the form
    temp = jso.resetForm()

   
    Set x = jso.getField("txt1_name")
    x.Value = "Bing Crosby"
   
    Set x = jso.getField("txt2_sex")
    x.Value = "Male"
   
    Set WshShell = Nothing
    Set myApp = Nothing
    Set AVDoc = Nothing
    Set PDDoc = Nothing

Thanks in advance for any help.

Christian Bahnsen

[lrosenth]

How about downloading the Acrobat SDK and reading the documentation to understand what you've written?!?!

[christian_bahnsen]

I'll give that another shot.  Hopefully the SDK has matured since last I perused it (back in version 5).  It was pretty sketchy then.  I recently got Acrobat 9 (mainly because I bought a laptop running Vista and my venerable version 5 wouldn't load thereon).  Does Adobe have the equivalent of MSDN?  I recall being very frustrated at the time I built the first application using the code above because I couldn't find code samples to test.

Thanks for your reply.

[christian_bahnsen]

OK, downloaded the SDK, extracted from the zip, and poked around.  I wouldn't spurn any suggestions/assistance zeroing in on the content germane to my post. 

Thanks.

[lrosenth]

The category is IAC (Interappliction Communication) and you should be looking at the documentation on the various methods there as well as the sample code...

[christian_bahnsen]

10-4

Thanks

[christian_bahnsen]

I searched the SDK (several times, using different keywords and combinations thereof) looking for an updated version of the Acrobat Forms API Reference.  My most recent copy is Version 6.0 (Adobe Technical Note #5181).  Is the info in Acrobat Forms API Reference Version 6.0 still germane, or has it been superseded by subsequent versions?

Specifically, have the AVDoc and PDDoc objects changed fundamentally since the hoary days of Acrobat 5 and 6?

Thanks.

[lrosenth]

Try the IAC (interapplication communication) reference.

[christian_bahnsen]

Using the "Developing Applications Using Interapplication Communication" reference, I've been filling in knowledge gaps and baby-stepping until I hit this major wall.  FYI:  from page 22 in the reference

Example 2.6 Displaying “Hello, Acrobat!” in the JavaScript console will not work as shown in Access VBA.  I've tried the example in both Access 2003 and 2007.  The sample code as is throws error 91 (see attachment error91.jpg), "Object variable ... not set".

I add Set to the following 3 lines:

    Set gApp = CreateObject("AcroExch.App")
    Set gPDDoc = CreateObject("AcroExch.PDDoc")
    Set jso = gPDDoc.GetJSObject

Running the code after adding "Set" doesn't throw an error but it does crash Access every time.  (see attachment error_reporting.jpg)

Any suggestions?

Here's the entire code snippet:

Private Sub cmdHelloAcrobat_Click()

    Dim gApp As Acrobat.CAcroApp
    Dim gPDDoc As Acrobat.CAcroPDDoc
    Dim jso As Object

    Set gApp = CreateObject("AcroExch.App")
    Set gPDDoc = CreateObject("AcroExch.PDDoc")
    If gPDDoc.Open("C:\chris\acrobat_test.pdf") Then
        Set jso = gPDDoc.GetJSObject
        jso.console.Show
        jso.console.Clear
        jso.console.println ("Hello, Acrobat!")
        gApp.Show
    End If

End Sub

[lrosenth]

What about error catching code? I don't see where you are catching any errors that might be thrown...

[christian_bahnsen]

I added error handling code but Access crashes before displaying the results.

I've also attached a screenshot of some debug information from the error report.  If you want the full error report (that is sent to Microsoft) please give me an email address.  I don't want to attach the document here for security purposes.

Here's my revised snippet:

Private Sub cmdHelloAcrobat_Click()
On Error GoTo Err_cmdHelloAcrobat_Click
    Dim gApp As Acrobat.CAcroApp
    Dim gPDDoc As Acrobat.CAcroPDDoc
    Dim jso As Object

    Set gApp = CreateObject("AcroExch.App")
    Set gPDDoc = CreateObject("AcroExch.PDDoc")
    If gPDDoc.Open("C:\chris\acrobat_test.pdf") Then
        Set jso = gPDDoc.GetJSObject
        jso.console.Show
        jso.console.Clear
        jso.console.println ("Hello, Acrobat!")
        gApp.Show
    End If

    Set gApp = Nothing
    Set gPDDoc = Nothing
    Set jso = Nothing
   
Exit_cmdHelloAcrobat_Click:
    Exit Sub

Err_cmdHelloAcrobat_Click:
    MsgBox Err.Description
    Resume Exit_cmdHelloAcrobat_Click

End Sub

[christian_bahnsen]

BTW: The line that crashes Access is Set jso = gPDDoc.GetJSObject

[christian_bahnsen]

Rather, it's that section.  If I remove this block no errors are thrown and Access doesn't crash.

    If gPDDoc.Open("C:\chris\acrobat_test.pdf") Then
        Set jso = gPDDoc.GetJSObject
        jso.console.Show
        jso.console.Clear
        jso.console.println ("Hello, Acrobat!")
        gApp.Show
    End If

[christian_bahnsen]

I've narrowed it down even more.  Everything works fine until this line is added back in:

      gApp.Show

I started with the If block itself

    If gPDDoc.Open("C:\chris\acrobat_test.pdf") Then

    End If

and ran it then added back in one line at a time.  This doesn't crash Access.

    If gPDDoc.Open("C:\chris\acrobat_test.pdf") Then

        Set jso = gPDDoc.GetJSObject
        jso.console.Show
        jso.console.Clear
        jso.console.println ("Hello, Acrobat!")
    End If

But this does.

    If gPDDoc.Open("C:\chris\acrobat_test.pdf") Then

        Set jso = gPDDoc.GetJSObject
        jso.console.Show
        jso.console.Clear
        jso.console.println ("Hello, Acrobat!")
        gApp.Show

    End If

[ReinhardF]

Working with jso and docs you have to use AVDoc.

Use something like attached to work with jso.

HTH, Reinhard

AcroUseJso.vbs

--------------------------------------

  '//-> Settings
FileNm = "c:\Test.pdf"

  '//-> Start Acrobat and show
Set gApp = CreateObject("Acroexch.app")
gApp.Show

  '//-> Open the file via avdoc to get jso
Set AVDoc = CreateObject("AcroExch.AVDoc")
OK = AVDoc.Open(FileNM, "")
if  not OK then if MsgBox("Error open Basic File") then Quit()
Set PDDoc = AVDoc.GetPDDoc()
Set jso = PDDoc.GetJSObject

  '//-> Do something with jso
jso.app.alert("Hello world")

Quit()
sub Quit()
   Set JSO = Nothing    
    Set PDDoc = Nothing
    Set AVDOC = Nothing
    set gApp = Nothing
end sub

[christian_bahnsen]

Reinhard,

1) Thanks for you suggestion.  It works better, but with one curious twist.  The first time I run the code it only opens the Acrobat application in the background and doesn't load the document.  The second time I run the code it opens Acrobat with the file loaded and makes Acrobat the active window.  (It doesn't matter whether I left the Acrobat application window that loaded on the first attempt open or had closed it).

When I run the code a third time it only opens the Acrobat application in the background, without loading the file.  Running the code a fourth time opens Acrobat with the file loaded and makes Acrobat the active window again.

If I leave the Acrobat window open (the one that loaded without the file being opened), running the code thereafter loads another instance of Acrobat with the file open, but does not make it the active window.

2) Including the Quit() code without declaring objects with a DIM statement will throw the "Object variable not set (Error 91)" described in my previous posts 1 and 8 (8 has a screenshot).  The code works if I leave off the SET name = Nothing (where name = gapp, jso, etc.).  The question that comes to mind is whether there's a penalty for not explicitly declaring the objects then deconstructing them (using the Nothing assignments) at the end of the code block?

It seems the best practice would be to explicitly declare the objects and explictly release them when finished.

Adding this statement, "Dim gApp As Acrobat.AcroApp" threw an error and crashed Access again.  I'm still trying to figure out the difference between the CAcroApp and AcroApp references, and whether I need to use Acrobat.AcroApp or if AcroApp can just stand on its own.

More readings in the IAC reference.  😉

Thanks again for your suggestion.  I'm getting close.

[ReinhardF]

About point 1) I really wonder. For me it works without problems.

However I attached another example which starts the file from script

and then get the AVDoc from already opened file in Acrobat.

For the release of the object I don't take really care.

If VBS closed and Acrobat closed all objects released by standard.

Enjoy, Reinhard

PS: The attached script gather all fields and report them or report that no field found.

AcroGetFields.vbs

------------------------------------------------------------------------

set WshShell = CreateObject ("Wscript.Shell")
WshShell.run "Acrobat.exe c:\Test.pdf"
While not WshShell.AppActivate("Adobe Acrobat") : Wscript.Sleep 1000 : Wend
Wscript.Sleep 1000

  '// Set/Get Acrobat Objects
Set App = CreateObject("AcroExch.App")
Set AVDoc = App.GetActiveDoc
Set PDDoc = AVDoc.GetPDDoc
Set JSO = PDDoc.GetJSObject

  '// search for fields and get Name+value
txt = ""
if jso.numFields then
  for i = 0 to jso.numFields -1
     fn = jso.getNthFieldName(i)
     set f = jso.getField(fn)
     fv = f.value
     txt = txt  &fn &"=" &fv &";"
  next
  msgbox(txt)
else
  msgbox("No fields found")
end if

Set AVDOC = Nothing
Set PDDoc = Nothing
Set JSO = Nothing

[christian_bahnsen]

Reinhard,

Thanks again for your help.

I tweaked your sample, mainly changing the way we delay while the Acrobat form loads.  The end product is very close to my original post.  Here what works for me:

Private Sub cmdHybrid_Click()

    Dim WshShell As Object
   
    Set WshShell = CreateObject("Wscript.Shell")
   
    WshShell.Run "Acrobat.exe C:\Users\Christian\Documents\data\dist5\dist05_face_only_nh.pdf"

    ' delay to let the form load completely
    PauseTime = 1
    Start = Timer
    Do While Timer < Start + PauseTime
        DoEvents
    Loop
   
    Set App = CreateObject("Acroexch.app")
    Set avDoc = CreateObject("AcroExch.AVDoc")
    Set avDoc = App.GetActiveDoc
    Set pdDoc = avDoc.GetPDDoc
    Set jso = pdDoc.GetJSObject
   
   
    ' let's clear the form
    temp = jso.resetForm()
   
    ' populate a couple fields
    Set x = jso.getField("txt1_name")
    x.Value = "Jerry Lewis"
   
    Set x = jso.getField("txt2_sex")
    x.Value = "Male"
   
    'housekeeping on the way out
    Set App = Nothing
    Set avDoc = Nothing
    Set pdDoc = Nothing
    Set jso = Nothing
   
   
End Sub

Adobe pros: any changes you'd suggest, or does this look kosher to you, too?