Sign a PDF from Excel VBA

jcarlosd

New Member
Joined
Oct 20, 2002
Messages
40
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:

VBA Code:
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.
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

jcarlosd

New Member
Joined
Oct 20, 2002
Messages
40
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

New Member
Joined
Oct 29, 2011
Messages
4
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

New Member
Joined
Oct 29, 2011
Messages
4
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

New Member
Joined
Oct 29, 2011
Messages
4
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:

VBA Code:
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:

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

Forum statistics

Threads
1,141,203
Messages
5,704,934
Members
421,372
Latest member
Jamie11

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top