Sign a PDF from Excel VBA

[jcarlosd]

I have been searching for an answer in different forums without success, so I decided to open a post.
Currently, I generate a PDF file from a range or a worksheet in Excel. What I need is to include a signature in the PDF file.

This is the code that I gather from different sources to open the PDF and sign it:

Public Sub SignPDF()
On Error GoTo Err_Handler
Dim pdfPDDoc As New AcroPDDoc, oJS As Object, oSign As Object, oPpklite As Object, oFields As Object
Dim strFName As String, strSignFName As String
Dim oSignInfo As Object, strSecInfo As String
Dim oParam As Parameter
    
   strSignFName = "C:\mySignature.pfx"
   strFName = "C:\myPdf.pdf"

   Set pdfPDDoc = CreateObject("AcroExch.PDDoc")
    
   If pdfPDDoc.Open(strFName) Then
      Set oJS = pdfPDDoc.GetJSObject
      
      Set oFields = oJS.AddField("SignatureField", "signature", 0, Array(350, 800, 500, 750))
      Set oSign = oJS.GetField("SignatureField")
      
      Set oPpklite = oJS.security.getHandler("Adobe.PPKLite", True)
      oPpklite.login "{'xxx', '" & strSignFName & "'}"
      oSign.signatureSign oPpklite, "{password:xxx, mdp: allowNone}"  , "C:\myPdfSigned.pdf", True
      
      pdfPDDoc.Save 1, strFName
      oPpklite.logout
   End If
   
Exit_Proc:
    Exit Sub
        
Err_Handler:
    MsgBox "In test" & vbCrLf & Err.Number & "--" & Err.Description
    Resume Exit_Proc
End Sub

I do have Adobe Acrobat installed in my computer (not the free Acrobat Reader) and I added a reference to "Adobe Acrobat 10.0 Type Library" with the "acrobat.tlb" file.
The code aborts at the line with oSign.signatureSign. I can not make it run, because of this error: "TypeError: Invalid argument type". I tried different alternatives in that line, but none worked.

If I comment that line, the code created a rectangle with my signature on it, but it was not signed: if I open the PDF, and click on the signature, I can succesfully sign it. But I want to see if it can be automated from VBA.

 

[mole999]

have you read this > https://community.adobe.com/t5/acro...-a-pdf-in-using-excel-vba/td-p/9629881?page=1

 

[jcarlosd]

Yes mole999, I read that forum thread. I also opened the JS console and the error was the same shown at VBA: "TypeError: Invalid argument type".
In that thread, as I understand, there is no solution, adn the comment was just to work with the javascript code and console.
I checked the "Adobe Javascript Scripting Reference" to see how to sign a PDF, but I cannot make it run from VBA.

Anybody has done it before?

 

[mr_vodoo]

Hi jcarlosd,

Can you please try passing an array of strings to "oPpklite.login"?

Dim arrWsNames() As String
arrWsNames = Split("password:xxx,mdp: allowNone", ",")

oSign.signatureSign oPpklite, arrWsNames, "C:\myPdfSigned.pdf", True

I have learnt that you need to pass parameters as array to certain functions (like setLock function) when using acrobat objects. When I did this following your example, I didn't get an error, instead I got the prompt window to select the signature and press sign button.

You probably need to pass an array to oPpklite.login as well.

Let me know if it works.

 

[mr_vodoo]

Just to clarify when I wrote:

Can you please try passing an array of strings to "oPpklite.login"?

I meant Can you please try passing an array of strings to "oSign.signatureSign"?

 

[mr_vodoo]

Hello,

I have tested the following code successfully, even though there are somethings I don't understand yet and other things I don't know how to do:

Public Function SignPDF_NotFinished()

    On Error GoTo Err_Handler
    
    Const inputpdf = "C:\Users\...\a.pdf"
    Const outputpdf = "C:\Users\...\a_Signed.pdf"
    Const strSignFName = "C:\Users\...\myCert.pfx"
    Const pass = "YourCertificatePasswordHere"

    Dim pdfPDDoc As New AcroPDDoc, oJS As Object, oSign As Object, oPpklite As Object
    Dim oSignInfo As Object
    Dim ResultLogin As Boolean
    Dim ResultSign As Boolean
    Dim arr() As String
    
    Set pdfPDDoc = CreateObject("AcroExch.PDDoc")
     
    If pdfPDDoc.Open(inputpdf) Then
    
        Set oJS = pdfPDDoc.GetJSObject
        Set oSign = oJS.GetField("SignatureField1")
        Set oPpklite = oJS.security.getHandler("Adobe.PPKLite", True)
        Set oSignInfo = oSign.SignatureInfo()
        
        'Login into certificate
        ResultLogin = oPpklite.login(pass, strSignFName)
        
        'Set timeout for password expiration. Without this it doesn't work
        oPpklite.setPasswordTimeout pass, 60
        
        arr = Split("", ",")
        
        ResultSign = oSign.signatureSign(oPpklite, arr, outputpdf)

        oPpklite.logout
        
        'No need to save. It is already saved when signed
        'pdfPDDoc.Save 1, outputpdf
        pdfPDDoc.Close
        
        Set oJS = Nothing
        Set oSign = Nothing
        Set oPpklite = Nothing
        Set oSignInfo = Nothing
        
    End If
    
    Set pdfPDDoc = Nothing
    
Exit_Proc:
        Exit Function
            
Err_Handler:
        MsgBox "In SignPDF" & vbCrLf & Err.Number & "--" & Err.Description
        Resume Exit_Proc
        
End Function

You know it's working because ResultLogin and ResultSign are True after running the code.

Note 1: Without entering "oPpklite.setPasswordTimeout pass, 60" it wouldn't work.
Note 2: Without passing array "arr" (it doesn't matter if it's empty) to "oSign.signatureSign", it doesn't work. I guess there should be another way, I just don't know it yet. I have also tried passing parameters like this but it doesn't work either:

ResultSign = oSign.signatureSign(oSig:=oPpklite, cDIPath:=outputpdf)

 

[therahulsah]

I'm getting this error on running,

In SignPDF
424--Object required error


I tried debugging step by step, and found that the control stops at

Set oSign = oJS.GetField("SignatureField1")

and jumps to error handler, please help.



BTW I m trying to sign my pdf from a folder, and want to save to another location, using a excel vba button.