• Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
    Dedicated community for Japanese speakers
  • 한국 커뮤니티
    Dedicated community for Korean speakers
Exit
0

Excel VBA - Same sheet produces different Signature box locations on PDF for different people

New Here ,
Jan 22, 2021 Jan 22, 2021

Copy link to clipboard

Copied

Roughly half a year ago I had some VBA that prints an excel sheet to PDF then adds two signature and two form fields to the newly created PDF.  It's worked without issue until two days ago.  It hadn't been used for awhile so i'm not entirely sure what happened during this time.  

From the same exact excel file, if I print to PDF - it positions the signature and form fields up perfectly.  if anyone else prints to PDF it moves the signature and form fields way off the page entirely.  Between myself and 3 other people testing it, we all have the following:

- Same Adobe DC Pro verison (including build number)

- Same page layout (landscape), same margins, etc (derived from excel sheet).

- Same preferences for  documents, general, page display, units & guides, Measuring (2D, 3D, Geo)

- When right clicking on a signature box and selecting properties (position tab), we all match on position units and position.

 

I'm really lost on how the same exact excel file can put these items in different locations.  Here is the code I use.  An example of how far off it is is.  From bottom (468) for the other people (1260) for me.

      '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 = 449 'left
      Const thtop = 1360 'top 507
      Const thrt = 793 'right
      Const thebot = 1260 'bottom 468

      inputPDFfile = Environ("USERPROFILE") & "\Downloads\" & descValueTitle & "_do_not_use.pdf"
      outputPDFfile = Environ("USERPROFILE") & "\Downloads\" & descValueTitle & ".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 + 347, thtop, thrt + 335, 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 + 683, thtop - 68, thrt + 475, 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 + 822, thtop - 68, thrt + 640, 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

 

TOPICS
Create PDFs

Views

397

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Mar 04, 2021 Mar 04, 2021

Copy link to clipboard

Copied

LATEST

I have an update on this - after some testing, verifying exact settings for the excel sheet and adobe in general I discovered that it has something to do with scaling of the excel sheet.  We tried monitor resolution, etc - and only one repeatable action was discovered.

 

With the sheet at 100% scaling - the signature boxes lined up just fine.  If the scaling is set to 38% (normal) the boxes scale down for me but not a co-worker.  His boxes appear as if the sheet was at 100% scaling.  

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines