Export data from Excel to pdf form without saving it as .csv or other file first

Question

I have a very complicated Excel workbook, and on one of the worksheets my boss would like some values placed in an Acrobat form.

I was able to find and edit a macro (I found here) that would open a blank version of the form.

I've been asked to find a way to then have form fields in the Acrobat template auto fill from matching headers in the Excel worksheet (provided one field was filled out as a starting point) but without saving the Excel sheet to .csv to tab-delineated or whatever as an intermittent step.

Or, conversely, make a macro in the Excel sheet that will take the values from, say, the current row and open then fill the Acrobat form.

Is this do-able from within either Excel or Acrobat Pro?

I do not know java or Visual Basic very well, so please be patient.

Any help you can offer will be greatly appreciated.

Thank you.

There is some documentation available here: http://www.adobe.com/content/dam/acom/en/devnet/acrobat/pdfs/VBJavaScript.pdf I tried it in vba but got a 429 error which this: https://stackoverflow.com/questions/28677288/vba-acrobat-run-time-error-429-activex-component-cant-create-object describes as being because I've only got the free version of Acrobat installed. You may have more luck with Pro. — majjam, Nov 15 '17 at 20:35
Here is a link to someone that seems to have solved your problem. It requires Pro. http://www.myengineeringworld.net/2013/10/read-and-write-pdf-forms-from-excel-vba.html — mooseman, Nov 15 '17 at 20:39
The page mooseman linked to was a big help; it'll take me some work to finesse the script, however. I'm too new at this. :^) — fogharty, Nov 20 '17 at 20:53

score 0 · Answer 1 · edited Dec 01 '20 at 17:53

You can create an UTF-8 XML text file and import it easily into the PDF. Here is a function I wrote to create the XML file.

The function creates an XFDX, inside the header of the file, the PDF file is specified.

You can just double click the XFDX file, and adobe reader will import the data.

Private Function CreatePDFFile(PDFileID As Integer) As Boolean
Dim sFileHeader As String
Dim sFileFooter As String
Dim sFileFields As String
Dim sTmp As String
Dim sFileName As String
Dim lngFileNum As Long
Dim FieldName, FieldValue As String
Dim row, PDFRowStart, PDFRowEnd, PDFNumberOfRows, RetVal As Integer
Dim doc As New MSXML2.DOMDocument60
On Error GoTo ErrorHandler_CreatePDFFile
    
    'If errors then Use late binding to avoid error user-defined type not defined (References)
    'Dim doc As Variant
    'Set doc = CreateObject("MSXML2.DOMDocument")
    
    If Len(PDFFilePath(PDFileID)) < 1 Then
       ' MsgBox "Wählen Sie zuerst eine Datei aus. Für diese  PDF-Datei-ID: " & PDFileID
       'If no file selected for this PDFFile ID then do nothing...
       CreatePDFFile = False
        Exit Function
    Else
        'Continue to create list of selected PDF docs further here in this sub.
        CreatePDFFile = True
    End If
    
    'Locaion of PDF File
    'PDFFile1Path
    'sFileName = "D:\OneDrive\PDF\Try6\" & "NameOfPDFFile.pdf"
    'Adobe, use this path to open the correct PDF to import the data into.
    Dim temp As String
    temp = PDFFilePath(PDFileID) '& "\" & PDFFileName(PDFileID)
    
    sFileHeader = "<?xml version=""1.0"" encoding=""UTF-8""?>" & vbCrLf & _
                  "<xfdf xmlns=""http://ns.adobe.com/xfdf/"" xml:space=""preserve"">" & vbCrLf & _
                  "     <f href=""" & temp & """/>" & vbCrLf & _
                  "     <fields>" & vbCrLf

    sFileFooter = "    </fields>" & vbCrLf & _
                  "    <ids original=""31686985C2863CD11CFF58ED2604C831"" modified=""6592384A5ED5A44484C99A887E1D71CE""/>" & vbCrLf & _
                  "</xfdf>"
    
    'Example XML field value pair
    ' sFileFields = "     <field name=""ClientName"">" & vbCrLf & _
    '               "         <value>Firstname Lastname</value>" & vbCrLf & _
    '               "     </field>" & vbCrLf
    

    'Determine the start for of data in Excel for this file
    'Start row is marked with a nammed called cell PDFFile1StartRow
    If Not NamedRangeExists(ActiveWorkbook.Names, "PDFFile" & PDFileID & "StartRow") Then
        MsgBox "Remember to define the start row named cell! " & "PDFFile" & PDFileID & "StartRow"
        Exit Function
    Else
        'Set the PDFRowStart to the row number of the named field found
        PDFRowStart = Range("PDFFile" & PDFileID & "StartRow").row
    End If
    
    'Determine the end row for of data in Excel for this file
    'Strat end is marked with a nammed cell called PDFFile1EndRow
    If Not NamedRangeExists(ActiveWorkbook.Names, "PDFFile" & PDFileID & "EndRow") Then
        MsgBox "Remember to define the end row named cell! " & "PDFFile" & PDFileID & "EndRow"
        Exit Function
    Else
        'Set the PDFRowEnd to the row number of the named field found
        PDFRowEnd = Range("PDFFile" & PDFileID & "EndRow").row
    End If
    
    'How many rows of data are there for this file to loop through?
    PDFNumberOfRows = PDFRowEnd - PDFRowStart
        
    'Loop through all the rows of data for this specific PDF file, and create a new xml field for each row.
    For row = PDFRowStart To PDFRowStart + PDFNumberOfRows
        
        On Error GoTo ErrorHandler_CreatePDFFile
        'The first column contains the name of the field in the PDF document, the second column the value
        FieldName = Worksheets("PDF").Cells(row, 1).value
        FieldValue = Worksheets("PDF").Cells(row, 2).value

        'Encode text to xml, encode special characters like ampersands.
        FieldName = doc.createTextNode(FieldName).XML
        FieldValue = doc.createTextNode(FieldValue).XML

        'Add a new field node in the xml document.
        sFileFields = sFileFields & "     <field name=""" & FieldName & """>" & vbCrLf & _
                        "         <value>" & FieldValue & "</value>" & vbCrLf & _
                        "     </field>" & vbCrLf
    Next row
    
    'Combine the header content and footer to create the complete XML File
    sTmp = sFileHeader & sFileFields & sFileFooter
    'Debug.Print sTmp
    
    ' Set the path of the XML file to be written disk
    'sFileName = ActiveWorkbook.Path & "\" & PDFFileName(PDFileID) & ".xfdf"
    'save it to the same path as the PDF file,just change the extension to . xfdf
    sFileName = PDFFilePath(PDFileID) & ".xfdf"
    
    'Load the xml text into the MSXML2.DOMDocument60, and parse it to see if valid
    doc.LoadXML (sTmp)
    
    If doc.parseError <> 0 Then
        MsgBox "0x" & Hex(doc.parseError) & ": " & doc.parseError.reason
        Exit Function
    End If

    'Save the XML file to disk in utf-8 file format
    doc.Save (sFileName)
    
    'Destroy the object, release the file to be deleted.
    Set doc = Nothing
    
    DoEvents
        
    'Wait one second to give it time to save the file completely
    Application.Wait Now + #12:00:03 AM#
       
    ' Open XFDX file as PDF
    OpenXMLFile (sFileName)
    
    DoEvents
    
    Exit Function

ErrorHandler_CreatePDFFile:
    
    If Err.Number = 13 Then
            MsgBox "Error number: " & Err.Number & " Description: " & Err.Description & " Error in data in row: " & row
        Exit Function
    End If
    MsgBox "Make xfdf Error: " + Str(Err.Number) + " " + Err.Description + " " + Err.Source
End Function

Export data from Excel to pdf form without saving it as .csv or other file first

1 Answers1

Linked