Failing to Populate Secured PDF with Excel VBA

[shairado]

Hi,

I have been using the same Excel VBA code below to populate a few PDFs in the past, .

However, when I try now to use the same code for a PDF that has [SECURED] in its file name, when I open it with Adobe Acrobat Pro, it won't work.

I am able to modify this PDF manualy and save the changes I make, but failing to do so with Excel VBA.

Any ideas ?

My Code

Option Explicit

' --- Adobe Acrobat Objects ---
Dim AcroApp                 As Acrobat.AcroApp
Dim AcroDoc                 As Acrobat.AcroAVDoc
Dim AcroPdf                 As New Acrobat.AcroPDDoc
Dim PDFJSO                  As Object

' ===== List of Fields in PDF =====
Const AddressFldName        As String = "p_Street_Address" ' address
Const CityFldName           As String = "p_City"  ' city
Const ZipCodeFldName        As String = "p_Zip" ' Zip Code


Sub FMLSSaleAgreementPDFPopulate()
              
         
Dim PDFFullPathAndName      As String
Dim PSAFullFileName         As String

' set the Full path and PDf file name (including extension)
PDFFullPathAndName = "C:\My Path\PDF Template Name.pdf"

On Error Resume Next

' initialize Acrobat by creating the App Object
Set AcroApp = CreateObject("AcroExch.App")
' Check if the Acrobat App was created
If Err.Number <> 0 Then
    MsgBox "Could not create the App object!", vbCritical, "Object error"
    ' release the object and exit
    Set AcroApp = Nothing
    Exit Sub
End If

' Create the AVDoc object
Set AcroDoc = CreateObject("AcroExch.AVDoc")
' Check if the AVDoc object was created
If Err.Number <> 0 Then
    MsgBox "Could not create the AVDoc object!", vbCritical, "Object error"
    ' release the objects and exit.
    Set AcroDoc = Nothing
    Set AcroApp = Nothing
    Exit Sub
End If
On Error GoTo 0 ' reset Adobe object Resume Next Error handling
       
'--- Check if PDF Template exists in Folder ---
If Len(Dir(PDFFullPathAndName)) = 0 Then
    MsgBox "Unable to find PDF template in folder", vbCritical, "PDF Template Error"
        
    Exit Sub
End If
       
' open the PDF file.
If AcroDoc.Open(PDFFullPathAndName, "") = True Then
    Application.ScreenUpdating = False
    
    ' set the PDDoc object
    Set AcroPdf = AcroDoc.GetPDDoc
    ' set the JS Object - Java Script Object
    Set PDFJSO = AcroPdf.GetJSObject
        
    ' === Paragraph 1 - General Info ===
    With ThisWorkbook.Sheets("PDF")
        PDFJSO.getField(AddressFldName).Value = "address test"
        PDFJSO.getField(CityFldName).Value = "city name"
        PDFJSO.getField(ZipCodeFldName).Value = "99999"
    End With
End If
Application.ScreenUpdating = True

PSAFullFileName = FMLSPDFFolderPathRng.Value2 & "\" & PSAFullFileName & Format(Date, "yyyy_mm_dd") & ".pdf"

AcroPdf.Save 1, PSAFullFileName
AcroPdf.Close
AcroDoc.Close 1

' Release all Acrobat Objects
AcroApp.Exit
Set PDFJSO = Nothing
Set AcroPdf = Nothing
Set AcroDoc = Nothing
Set AcroApp = Nothing

End Sub

[Thom Parker]

Where is the failure point in the code? Is the secure PDF actually being opened by the VBA?  

Have you tried manuall opening the PDF in Acrobat and then aquiring the current PDF with the VBA? 

[shairado]

There is no fail, the code opens the PDF, the code runs all the way to the end, just the PDF fields aren't populated when running through the lines.

Any idea ?

[Thom Parker]

What kind of security is on the PDF? Password or Certificate? Are there any rights or restrictions on the PDF?

I would suggest a slight change to your code, which will simplify the VBA interaction with Acrobat and provide more information about what's going on. 

Instead of calling "getField" in the VBA, write a folder level function to set the field values. Then call this function from the VBA. Place "console.println()" statements into this function so you can see directly that it's being called.  And return a value from a document field or property that can be reported in the VBA script. 

I don't know what the specifics are for IAC interaction with a secure PDF. I'm sure they depend on the type of security. But I suspect part of the issue may be the indirect nature of IAC. Using a folder level script puts the interaction with the field inside Acrobat. It also gives you a direct way to guarentee the interaction, instead of just not getting any indication of what's going on. 

[shairado]

I am not familiar with folder level function.

Do you have a sample of VBA code that does that ?

[Thom Parker · Accepted solution]

All top level objects in the Acrobat/PDF JavaScript context are directly accessible through the IAC JSO. So you call the function exactly like you call "getField()".  Just pass in the 3 strings that set the fields. 

To define a folder level function do this.

1) Create a plain txt file and give it a ".js" extension.

2) Edit the file to define the function, something like this

function SetMyFields(a,b,c)
{
    console.println("Start Set Fields: " + a);
    this.getField("p_Street_Address").value = a;
    this.getField("p_City").value = b;
    this.getField("p_Zip").value = c;
    console.println("End Set Fields: " + b);
    return this.title;
}

Call this function in your VBA like this:

PDFJSO.SetMyField("Address","City","Zip");

Here are instructions on installing a folder level script

https://www.pdfscripting.com/public/Installing_Automation_Tools.cfm

And you'll find a tutorial on using the JavaScript Console Window here:

https://www.pdfscripting.com/public/Free_Videos.cfm#JSIntro

The console window should report an errors, and of course you'll see the messages from the function that verify the code is being run. In fact. If Acrobat is having a problem with the current code, then there should be a message here.

On another note, why are you using the JSO instead of the IAC AcroForm methods for setting form  fields? Doing this would get around the context change the JSO causes. 

[shairado]

Thanks so much for all your help, I was trying to use the AcroForm, not sure which references VBA needs, and what type of object it is.

I couldn't find any complete piece of code online, on this forum or stackoverflow.

Can you assist ? 

[Thom Parker]

Download the Acrobat SDK, it has lots of examples. 

[shairado]

I have been using the same Excel VBA code below to populate a few PDFs in the past, successfully.

However, when I try now to use the same code for a PDF that has [SECURED] when I open it with Adobe Acrobat Pro, it won't work.

I am able to modify this PDF manualy and save the changes I make, but not failing to do so with Excel VBA.

Any ideas ?

My Code (relevant part)

Option Explicit

' --- Adobe Acrobat Objects ---
Dim AcroApp                 As Acrobat.AcroApp
Dim AcroDoc                 As Acrobat.AcroAVDoc
Dim AcroPdf                 As New Acrobat.AcroPDDoc
Dim PDFJSO                  As Object

' ===== List of Fields in PDF =====
Const AddressFldName        As String = "p_Street_Address" ' address
Const CityFldName           As String = "p_City"  ' city
Const ZipCodeFldName        As String = "p_Zip" ' Zip Code


Sub FMLSSaleAgreementPDFPopulate()
              
         
Dim PDFFullPathAndName      As String
Dim PSAFullFileName         As String

' set the Full path and PDf file name (including extension)
PDFFullPathAndName = "C:\My Path\PDF Template Name.pdf"

On Error Resume Next

' initialize Acrobat by creating the App Object
Set AcroApp = CreateObject("AcroExch.App")
' Check if the Acrobat App was created
If Err.Number <> 0 Then
    MsgBox "Could not create the App object!", vbCritical, "Object error"
    ' release the object and exit
    Set AcroApp = Nothing
    Exit Sub
End If

' Create the AVDoc object
Set AcroDoc = CreateObject("AcroExch.AVDoc")
' Check if the AVDoc object was created
If Err.Number <> 0 Then
    MsgBox "Could not create the AVDoc object!", vbCritical, "Object error"
    ' release the objects and exit.
    Set AcroDoc = Nothing
    Set AcroApp = Nothing
    Exit Sub
End If
On Error GoTo 0 ' reset Adobe object Resume Next Error handling
       
'--- Check if PDF Template exists in Folder ---
If Len(Dir(PDFFullPathAndName)) = 0 Then
    MsgBox "Unable to find PDF template in folder", vbCritical, "PDF Template Error"
        
    Exit Sub
End If
       
' open the PDF file.
If AcroDoc.Open(PDFFullPathAndName, "") = True Then
    Application.ScreenUpdating = False
    
    ' set the PDDoc object
    Set AcroPdf = AcroDoc.GetPDDoc
    ' set the JS Object - Java Script Object
    Set PDFJSO = AcroPdf.GetJSObject
        
    ' === Paragraph 1 - General Info ===
    With ThisWorkbook.Sheets("PDF")
        PDFJSO.getField(AddressFldName).Value = "address test"
        PDFJSO.getField(CityFldName).Value = "city name"
        PDFJSO.getField(ZipCodeFldName).Value = "99999"
    End With
End If
Application.ScreenUpdating = True

PSAFullFileName = FMLSPDFFolderPathRng.Value2 & "\" & PSAFullFileName & Format(Date, "yyyy_mm_dd") & ".pdf"

AcroPdf.Save 1, PSAFullFileName
AcroPdf.Close
AcroDoc.Close 1

' Release all Acrobat Objects
AcroApp.Exit
Set PDFJSO = Nothing
Set AcroPdf = Nothing
Set AcroDoc = Nothing
Set AcroApp = Nothing

End Sub