Convert Excel to PDF with empty signature box

[JayB0730]

Hello,

We have an excel macro (vba) that converts our worksheet to pdf. However, we are trying to figure out a way to add signature boxes to the converted pdf file in an automated fashion. Can this be done via VBA? If so, how?

TIA,
Jay

 

[john316swan]

Do you have an example of your worksheet? Can't you just add a signature line to the worksheet so that it is already included when the conversion to pdf happens?

 

[JayB0730]

John,

I do not have one I can share but how would the PDF know it is a signature field when I convert? Just to clarify, this needs to be signed digitally.

 

[john316swan]

The digital signature feature I believe you are asking for is only available in Adobe. You can add a digital signature line in excel that will allow a user to sign digitally...it just won't be in pdf format. You can find out more how to do this: https://support.office.com/en-us/ar...ce-files-70d26dc9-be10-46f1-8efa-719c8b3f1a2d

Basically it's under the Insert Tab, under the text section labeled "Signature Line" - once you add a signature line, you can save the document and then send for a user to sign. Once a user signs, it locks the document and if they change anything, it breaks their signature.

 

[JayB0730]

The digital signature feature I believe you are asking for is only available in Adobe. You can add a digital signature line in excel that will allow a user to sign digitally...it just won't be in pdf format. You can find out more how to do this: https://support.office.com/en-us/ar...ce-files-70d26dc9-be10-46f1-8efa-719c8b3f1a2d

Basically it's under the Insert Tab, under the text section labeled "Signature Line" - once you add a signature line, you can save the document and then send for a user to sign. Once a user signs, it locks the document and if they change anything, it breaks their signature.

Thanks. However, we want to lock the rest of the document in a PDF format. I wonder if there is code within Adobe if not available in Excel?

 

[john316swan]

Possibly a code within Adobe, but a question for another forum. You could lock the document in Excel, then create a macro to export it to PDF after it's been digitally signed. Just trying to help you brainstorm ideas

 

[John_w]

This can be done with the Acrobat API, however that's only available to VBA when you install Acrobat Pro, not the free Adobe Reader.

This saves the active sheet as a PDF and adds an signature field on the first page, ready for you to sign the '.... WITH SIGNATURE FIELD.pdf' file.

Public Sub Save_Sheet_As_PDF_Add_Signature_Field()

    Dim PDDoc As Object
    Dim AVDoc As Object
    Dim JSO As Object
    Dim formField As Object
    Dim inputPDFfile As String, outputPDFfile As String
    Dim coords() As Variant
    
    Const BOTTOM_LEFT_X = 300
    Const BOTTOM_LEFT_Y = 650
    Const WIDTH = 200
    Const HEIGHT = 100
    
    'Coordinates specifying position and size of the signature field's bounding rectangle, with origin (0,0) at bottom left of page,
    'in the following order: top-left x, top-left y, bottom-right x and bottom-right y.
    
    coords = Array(BOTTOM_LEFT_X, BOTTOM_LEFT_Y + HEIGHT, BOTTOM_LEFT_X + WIDTH, BOTTOM_LEFT_Y)

    With ActiveSheet
        .Range("F1").Value = "Created " & Now
        inputPDFfile = ThisWorkbook.Path & "\" & .Name & ".pdf"
        outputPDFfile = ThisWorkbook.Path & "\" & .Name & " WITH SIGNATURE FIELD.pdf"
        .ExportAsFixedFormat Type:=xlTypePDF, Filename:=inputPDFfile, _
            Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
    End With

    Set PDDoc = CreateObject("AcroExch.PDDoc")
    Set AVDoc = CreateObject("AcroExch.AVDoc")
    
    If PDDoc.Open(inputPDFfile) Then
        Set JSO = PDDoc.GetJSObject
        Set formField = JSO.AddField("SignatureField", "signature", 0, coords)   '0 = 1st page
        formField.StrokeColor = JSO.Color.black  'StrokeColor sets the border and text colours of the field
        If PDDoc.Save(1, outputPDFfile) Then
            PDDoc.Close
            AVDoc.Open outputPDFfile, vbNullString
            AVDoc.BringToFront
            MsgBox "Created " & outputPDFfile
            AVDoc.Close False
        Else
            MsgBox "Unable to save " & outputPDFfile
        End If
    End If
  
End Sub

 

[chipsworld]

John,
I have a question to your above... Is there any way to use this to create more than one signature block on a single page? Would it require running the signature block portion as many times as the number of signatures required or ?

By the way...it works perfectly! It was amazing that this was just posted the other day, and here I was looking for a solution today!

 

[chipsworld]

This can be done with the Acrobat API, however that's only available to VBA when you install Acrobat Pro, not the free Adobe Reader.

This saves the active sheet as a PDF and adds an signature field on the first page, ready for you to sign the '.... WITH SIGNATURE FIELD.pdf' file.

Public Sub Save_Sheet_As_PDF_Add_Signature_Field()

    Dim PDDoc As Object
    Dim AVDoc As Object
    Dim JSO As Object
    Dim formField As Object
    Dim inputPDFfile As String, outputPDFfile As String
    Dim coords() As Variant
   
    Const BOTTOM_LEFT_X = 300
    Const BOTTOM_LEFT_Y = 650
    Const WIDTH = 200
    Const HEIGHT = 100
   
    'Coordinates specifying position and size of the signature field's bounding rectangle, with origin (0,0) at bottom left of page,
    'in the following order: top-left x, top-left y, bottom-right x and bottom-right y.
   
    coords = Array(BOTTOM_LEFT_X, BOTTOM_LEFT_Y + HEIGHT, BOTTOM_LEFT_X + WIDTH, BOTTOM_LEFT_Y)

    With ActiveSheet
        .Range("F1").Value = "Created " & Now
        inputPDFfile = ThisWorkbook.Path & "\" & .Name & ".pdf"
        outputPDFfile = ThisWorkbook.Path & "\" & .Name & " WITH SIGNATURE FIELD.pdf"
        .ExportAsFixedFormat Type:=xlTypePDF, Filename:=inputPDFfile, _
            Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
    End With

    Set PDDoc = CreateObject("AcroExch.PDDoc")
    Set AVDoc = CreateObject("AcroExch.AVDoc")
   
    If PDDoc.Open(inputPDFfile) Then
        Set JSO = PDDoc.GetJSObject
        Set formField = JSO.AddField("SignatureField", "signature", 0, coords)   '0 = 1st page
        formField.StrokeColor = JSO.Color.black  'StrokeColor sets the border and text colours of the field
        If PDDoc.Save(1, outputPDFfile) Then
            PDDoc.Close
            AVDoc.Open outputPDFfile, vbNullString
            AVDoc.BringToFront
            MsgBox "Created " & outputPDFfile
            AVDoc.Close False
        Else
            MsgBox "Unable to save " & outputPDFfile
        End If
    End If
 
End Sub

John...I apologize for asking about a year old post, but had a question...

How could I change this to insert two ( or more) signature fields into a single document?

I have been playing with it, but to no avail so far...

 

[John_w]

@chipsworld, what happened to your question yesterday asking how to add 2 or more signature fields?

In case you haven't found the answer, you simply repeat the JSO.addField line with a different field name (the 1st argument) and coordinates (the 4th argument).

Maybe my first code was a little confusing because I decided to position the signature field's bounding rectangle by specifying its bottom-left coordinates, whereas the Adobe documentation uses the top-left coordinates. This code uses the Adobe convention and adds 2 signature fields near the bottom of the page 80 pixels apart.

Public Sub Save_Sheet_As_PDF_Add_2_Signature_Fields()

    Dim PDDoc As Object
    Dim AVDoc As Object
    Dim JSO As Object
    Dim formField As Object
    Dim inputPDFfile As String, outputPDFfile As String
    Dim coords() As Variant
   
    Const TOP_LEFT_X = 50
    Const TOP_LEFT_Y = 60
    Const WIDTH = 200
    Const HEIGHT = 30
   
    With ActiveSheet
        .Range("F1").Value = "Created " & Now
        inputPDFfile = ThisWorkbook.Path & "\" & .Name & ".pdf"
        outputPDFfile = ThisWorkbook.Path & "\" & .Name & " WITH 2 SIGNATURE FIELDS.pdf"
        .ExportAsFixedFormat Type:=xlTypePDF, Filename:=inputPDFfile, _
            Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
    End With

    Set PDDoc = CreateObject("AcroExch.PDDoc")
    Set AVDoc = CreateObject("AcroExch.AVDoc")
   
    If PDDoc.Open(inputPDFfile) Then
   
        Set JSO = PDDoc.GetJSObject
       
        'The 4th argument to JSO.addField is an array of coordinates specifying the position and size of the signature field's bounding rectangle,
        'with origin (0,0) at bottom left of page, in the following order: top-left x, top-left y, bottom-right x and bottom-right y.

        '1st signature field
       
        coords = Array(TOP_LEFT_X, TOP_LEFT_Y, TOP_LEFT_X + WIDTH, TOP_LEFT_Y - HEIGHT)
        Set formField = JSO.addField("SignatureField1", "signature", 0, coords)   '0 = 1st page
        formField.StrokeColor = JSO.Color.black  'StrokeColor sets the border and text colours of the field
       
        '2nd signature field 80 pixels to the right of the 1st signature field
       
        coords = Array(TOP_LEFT_X + WIDTH + 80, TOP_LEFT_Y, TOP_LEFT_X + WIDTH + 80 + WIDTH, TOP_LEFT_Y - HEIGHT)
        Set formField = JSO.addField("SignatureField2", "signature", 0, coords)
        formField.StrokeColor = JSO.Color.black
       
        If PDDoc.Save(1, outputPDFfile) Then
            PDDoc.Close
            AVDoc.Open outputPDFfile, vbNullString
            AVDoc.BringToFront
            MsgBox "Created " & outputPDFfile
            AVDoc.Close False
        Else
            MsgBox "Unable to save " & outputPDFfile
        End If
       
    End If
 
End Sub