'check if file exists, if it does - randomly assign a numeric sequence at the end.
strCheckPoint = 0
If Dir(Environ("USERPROFILE") & "\Downloads\" & descValueTitle & ".pdf") = "" Then
strCheckPoint = 0
Else
answer = MsgBox("A PDF with this name already exists, overwrite?", vbOKCancel)
If answer = vbOK Then
strCheckPoint = 0
Else
strCheckPoint = 1
End If
End If
If strCheckPoint = 0 Then
'activate sheets and begin printing
Sheets("Home").Activate
ActiveSheet.ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:=Environ("USERPROFILE") & "\Downloads\" & descValueTitle, _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=False
'add form fields to adobe sheet.
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 thlft = 169 'left
Const thtop = 512 'top
Const thrt = 300 'right
Const thebot = 473 'bottom
inputPDFfile = Environ("USERPROFILE") & "\Downloads\" & descValueTitle & ".pdf"
outputPDFfile = Environ("USERPROFILE") & "\Downloads\" & descValueTitle & "_temp.pdf"
Set PDDoc = CreateObject("AcroExch.PDDoc")
Set AVDoc = CreateObject("AcroExch.AVDoc")
If PDDoc.Open(inputPDFfile) Then
Set JSO = PDDoc.GetJSObject
'1st signature field
coords = Array(thlft, thtop, thrt, thebot)
Set formField = JSO.addField("authorize_signature", "signature", 0, coords) '0 = 1st page
formField.StrokeColor = JSO.Color.transparent 'StrokeColor sets the border and text colours of the field
'2nd signature field
coords = Array(thlft + 131, thtop, thrt + 127, thebot)
Set formField = JSO.addField("sca_signature", "signature", 0, coords) '0 = 1st page
formField.StrokeColor = JSO.Color.transparent 'StrokeColor sets the border and text colours of the field
'1st date field
coords = Array(thlft + 258, thtop - 26, thrt + 180, thebot)
Set formField = JSO.addField("approved_date", "text", 0, coords) '0 = 1st page
formField.textSize = 6
formField.textFont = "Arial"
formField.StrokeColor = JSO.Color.transparent 'StrokeColor sets the border and text colours of the field
'2nd date field
coords = Array(thlft + 311, thtop - 26, thrt + 242, thebot)
Set formField = JSO.addField("sca_date", "text", 0, coords) '0 = 1st page
formField.textSize = 6
formField.textFont = "Arial"
formField.StrokeColor = JSO.Color.transparent '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 if open input file
Dim Shex As Object
Set Shex = CreateObject("Shell.Application")
Shex.Open (outputPDFfile)