Macro that copies Excel data into pdf field doesn't always paste all the data

[RickOlson]

I have to geterate scads of pdf forms for distribution to other people. I complete about half the fields, then they complete the rest of them. I wrote a macro to automate the process that pulls data from Excel and pastes it to fileds in Acrobat. For the most part it works, but there are about 5 fields where the data gets truncated. Excel has a value like "202009" and it's pasted as "20200". or "This is a short description of something that happened" becomes "This is a short de". I'm trying to unravel why thngs are truncated and how to past all the data. The cells aren't all truncated at the same length, but each field is consistently truncated the same. E.g. Field 6 is always 5 chars long and Field 10 is always 19 chars long. The lengths of the field boxes aren't the same, but is is long enough to contain all the data it should have.

In the Acrobat form fields:
Font Size is Auto
I've tried Multi-line checked and unchecked
I've tried Scroll long text checked and unchecked
I've tried not specifying a character limit, and specifying 1000 chars
In the VBA code:
I've tried changing the duration of the pause

None of the changes had any impact. I put a break point in the code and verified that ThisCell.Offset(0, nfield - 1).Value in the code below contains the full string that should be pasted.

What should I try next?

--Thanks for the help

Sub Excel2Pdf()
Dim PDFTemplateFile, NewPDFName, SavePDFFolder, Name, stID As String
Dim LastRow As Long
Dim ThisCell As Range


With Sheets("MergePdf")                                ' Sheet containing the data that needs to be moved
  LastRow = Range("a9999").End(xlUp).Row    'Last Row that has data
  PDFTemplateFile = "DWtoPreUSDjul21.pdf" 'Template form file.  Data pasted here, then saved with new name

  Set ThisCell = Range("A1")
  
'Loop through rows, pasting data in fields as needed
  For nrow = 2 To LastRow                           
    Set ThisCell = ThisCell.Offset(1, 0)
        ThisWorkbook.FollowHyperlink PDFTemplateFile
'pause to give computer time to open the file
        Application.Wait Now + 0.00002                                      
        Name = ThisCell.Value
    
'Tab to the next field in Acrobat
        Application.SendKeys "{Tab}", True                                   
'Paste Excel data 
        Application.SendKeys ThisCell.Offset(0, 0).Value, True '    
'Pause to allow the paste to be completed
        Application.Wait Now + 0.00002                                       
          
        Application.SendKeys "{Tab}", True
        stID = "00" & ThisCell.Offset(0, 1).Value
        Application.SendKeys stID, True '
        Application.Wait Now + 0.00002
            
'Loop through a series of pasting the appropriate Excel Data.
        For nfield = 3 To 13                                                           
          Application.SendKeys "{Tab}", True
          Application.SendKeys ThisCell.Offset(0, nfield - 1).Value, True
          Application.Wait Now + 0.00002
        Next nfield

' Save the file with a new name
       NewPDFName = "PreUSD_" & Name & "_" & IDNO & "_" & Course & ".pdf"   
       'ThisCell.Offset(0, 3).Value = NewPDFName
       If Dir(NewPDFName) <> Empty Then
         Kill (NewPDFName)
       End If
       Application.SendKeys "^+(s)", True
       Application.Wait Now + 0.00002
       Application.SendKeys "~", True
       Application.Wait Now + 0.00002
       Application.SendKeys NewPDFName, True
       Application.Wait Now + 0.00002
       Application.SendKeys "{Enter}", True
       Application.Wait Now + 0.00004
  
  Next nrow



End With
End Sub

 

[John_w]

What should I try next?

Instead of sending whole strings with SendKeys, try a loop which sends one character at time, for example:

Dim i As Long
For i = 1 To Len(ThisCell.Offset(0, 0).Value)
    Application.SendKeys Mid(ThisCell.Offset(0, 0).Value, i, 1), True
    DoEvents
Next

The best method is to interact with the form fields directly with the Acrobat API. However this is only available if Acrobat Pro is installed, not the free Reader. See the example code at Create PDF from Excel and Email..

If you don't have Acrobat Pro then another method is using UIAutomation, which should be able to identify all the fields (probably Edit controls), set focus to them and set their values, without using SendKeys.

One change I would make to your code is to change the loop to create a copy of the PDF template file with each unique file name (FileCopy PDFTemplateFile, NewPDFName) and open the copy. At the end, Save the modified copy instead of Save As and populating the File Name.

 

[RickOlson]

Instead of sending whole strings with SendKeys, try a loop which sends one character at time, for example:

Dim i As Long
For i = 1 To Len(ThisCell.Offset(0, 0).Value)
    Application.SendKeys Mid(ThisCell.Offset(0, 0).Value, i, 1), True
    DoEvents
Next

The best method is to interact with the form fields directly with the Acrobat API. However this is only available if Acrobat Pro is installed, not the free Reader. See the example code at Create PDF from Excel and Email..

If you don't have Acrobat Pro then another method is using UIAutomation, which should be able to identify all the fields (probably Edit controls), set focus to them and set their values, without using SendKeys.

One change I would make to your code is to change the loop to create a copy of the PDF template file with each unique file name (FileCopy PDFTemplateFile, NewPDFName) and open the copy. At the end, Save the modified copy instead of Save As and populating the File Name.

Thanks for the suggestions. It didn't quite work. With your code I got similar results to the original code but with different truncations.

If I replaced DoEvents with Application.Wait Now + 0.00002 it worked perfectly, but takes forever because it's waiting after each character.

For fun, I tried printing in reverse order. That confirmed that the VBA knew about the full string, but I was only getting the end of the strings. e.g. 'mlkjih' instead of 'mlkijhgfedcba'

I did have another odd result. At one point, I have both my Sendkeys statement and the loop. With that configuration it would print a truncated string from my line, then the full text. So, 'This is a test string' came out as 'This iThis is a test string.

So, there does appear to be something funky in the handshake between Excel and Acrobat. Fortunately, I do have Acrobat Pro, so I'll try the API.

Copying the file rather than SaveAs is a good idea that I'll save until the rest is working. As it is, I'm debugging with a break before the SaveAs, so I can just close the file in Acrobat and don't need to delete anything to get back to a clean slate.

 

[RickOlson]

The Acrobat API approach worked. I didn't know about that approach and, not surprisingly, it's much faster. Thanks for the solution.