Convert Excel to PDF with empty signature box

J

JayB0730

Board Regular
Joined
Oct 22, 2014
Messages
74
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
Sort by date Sort by votes
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?
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
john316swan said:
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.
Click to expand...

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?
 
Upvote 0
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 :)
 
Upvote 0
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.

Code: 
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
 
Upvote 0
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!
 
Upvote 0
John_w said:
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.

Code: 
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
Click to expand...

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...
 
Upvote 0
@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.

VBA Code: 
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question Question
Top of text gets cut off in newer Excel versions when printing / converting to PDF
Replies
7
Views
248
kjhung
K
J
  • Question Question
VBA Email as PDF
Replies
6
Views
115
j4ymf
J
B
  • Question Question
Using VBA and Acrobat Add-in to export sheet as PDF
Replies
2
Views
435
Basher515
B
N
  • Question Question
VBA to add title from formula to a save as name
Replies
0
Views
149
NKUNZI
N
O
  • Question Question
VBA Convert to PDF
Replies
1
Views
231
JEC
JEC

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

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
Back
Top