1 Reply Latest reply on Apr 8, 2013 11:10 AM by sbaprogrammer

    Prepopulating Interactive Form with XML Data using VBA

    Jack Boers

      I have an excel spreadsheet with data on it.  I need to click on a button that will cause an Interactive PDF form to open and be populated with data from the spreadsheet.  To do this I want to use an XML data file.  I know how to create the file, but how do I cause the PDF to open in Adobe Reader with the XML data file using VBA?  The link needs to be a soft link, in that the name of the XML file could change.

      Many thanks for answers.

        • 1. Re: Prepopulating Interactive Form with XML Data using VBA
          sbaprogrammer

                      strXFDFFile = "c:\temp\" & strFileName & "tmp.xfdf"

                      'Open the PDF file
                       appPDFInit = True
                       Status "Processing " & strFileName
                       DoEvents
                      
                       ' Create PDF File, if any
                       If strXFDFFile <> "" Then
                           Dim fldValue As String
                      
                           Open strXFDFFile For Output As #1
                           Print #1, "<?xml version=""1.0"" encoding=""UTF-8""?>"
                           Print #1, "<xfdf xmlns=""http://ns.adobe.com/xfdf/"" xml:space=""preserve"">"
                           Print #1, "<f href=""" & strFilePath & strFileName & """/>"
                           Print #1, "<fields>"
                      
                           For Each fdField In rsData.Fields
                           If IsNull(fdField.Value) Then
                               fldValue = ""
                           Else
                               fldValue = fdField.Value
                               fldValue = XMLit(fldValue)
                           End If
                           If InStr(1, fdField.Name, "ID") > 0 Then
                               fldValue = Format(fldValue, "##########")
                           End If
                           If InStr(1, fdField.Name, "ZIP") > 0 Then
                               fldValue = Format(fldValue, "#####")
                           End If
                           If InStr(1, fdField.Name, "Rate") > 0 Then
                               fldValue = Format(fldValue, "#0.00#")
                           End If
                      '        Write #1, fdField.Name,
                           Print #1, "<field name=""" & fdField.Name & """>"
                           Print #1, "<value>" & fldValue & "</value>"
                           Print #1, "</field>"
                           Next
                           Print #1, "</fields>"
                           Print #1, "</xfdf>"
                      
                       Close #1
                             
                      End If
                      Dim x As Long
                      For x = 1 To 1000000: Next x
                     
                      LaunchPDF.LaunchFile strXFDFFile, 0, 2

           

           

          Make sure you reference the Adobe Acrobat Browser Control Library under Tools | References

           

          It will merge to Adobe Reader.  Note that I have made the strXFDFFile variable unique by including the name of the PDF file.  This assures that you can merge to more than one PDF at a time (this is taken from a loop for multiple PDF files).  Note that the XFDF file must include the path and the PDF file name in order to open the correct PDF for merging.

           

          I don't have time to explain the code, but if you have any questions let me know.

           

          Daniel H. Smith

          Smith Enterpises LLC