将数据从Excel导出为pdf格式，而无需先将其另存为.csv或其他文件 [英] Export data from Excel to pdf form without saving it as .csv or other file first

问题描述

我有一本非常复杂的Excel工作簿，在一个工作表中，我的老板希望将一些值放在Acrobat表单中.

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.

有人要求我找到一种方法，以便从Excel工作表中的匹配标头中自动填充Acrobat模板中的表单字段(前提是填写了一个字段作为起点)，但是没有 >将Excel工作表保存为.csv，以制表符描述，或将其保存为间歇步骤.

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.

或者相反，在Excel工作表中创建一个宏，该宏将从当前行中获取值并打开，然后填写Acrobat表单.

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.

这是可以在Excel还是Acrobat Pro中进行的?

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

我不太了解Java或Visual Basic，所以请耐心等待.

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

我们将不胜感激.

谢谢.

推荐答案

您可以创建UTF-8 XML文本文件并将其轻松导入PDF. 这是我编写的用于创建XML文件的函数. 该函数创建一个XFDX，在文件标题内指定了PDF文件. 您只需双击XFDX文件，然后Adobe Reader就会导入数据.

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.

私有函数CreatePDFFile(PDFileID为整数)为布尔值 昏暗的sFileHeader作为字符串 昏暗的sFileFooter作为字符串 昏暗的sFileFields作为字符串 Dim sTmp作为字符串 昏暗的sFileName作为字符串 尽可能长的Dim lngFileNum 昏暗的FieldName，FieldValue作为字符串 昏暗的列，PDFRowStart，PDFRowEnd，PDFNumberOfRows，RetVal作为整数 昏暗的文档作为新的MSXML2.DOMDocument60 出现错误时转到To ErrorHandler_CreatePDFFile

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 colomn contains the name of the field in the PDF codument, the second colum 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:

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

结束功能

这篇关于将数据从Excel导出为pdf格式，而无需先将其另存为.csv或其他文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！