Failing to Populate Secured PDF with Excel VBA

New Here ,
Dec 14, 2019 Dec 14, 2019

Copy link to clipboard

Copied

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

 

TOPICS
Acrobat SDK and JavaScript, Edit and convert PDFs, PDF forms, Standards and accessibility

Views

611

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines

correct answers 1 Correct Answer

Adobe Community Professional , Dec 14, 2019 Dec 14, 2019
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").val...

Likes

Translate

Translate
Adobe Community Professional ,
Dec 14, 2019 Dec 14, 2019

Copy link to clipboard

Copied

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? 

Thom Parker - Software Developer at PDFScripting
Use the Acrobat JavaScript Reference early and often

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Dec 14, 2019 Dec 14, 2019

Copy link to clipboard

Copied

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 ?

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Adobe Community Professional ,
Dec 14, 2019 Dec 14, 2019

Copy link to clipboard

Copied

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. 

Thom Parker - Software Developer at PDFScripting
Use the Acrobat JavaScript Reference early and often

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Dec 14, 2019 Dec 14, 2019

Copy link to clipboard

Copied

I am not familiar with folder level function.

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Adobe Community Professional ,
Dec 14, 2019 Dec 14, 2019

Copy link to clipboard

Copied

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. 

 

 

Thom Parker - Software Developer at PDFScripting
Use the Acrobat JavaScript Reference early and often

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Dec 14, 2019 Dec 14, 2019

Copy link to clipboard

Copied

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 ? 

 

 

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Adobe Community Professional ,
Dec 14, 2019 Dec 14, 2019

Copy link to clipboard

Copied

LATEST

Download the Acrobat SDK, it has lots of examples. 

Thom Parker - Software Developer at PDFScripting
Use the Acrobat JavaScript Reference early and often

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Dec 14, 2019 Dec 14, 2019

Copy link to clipboard

Copied

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

 

 

 

 

 

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines