Excel to PDF

[bryVA]

Hello,

I have an excel sheet that has information that needs to be placed in a fillable PDF. Is there a way using VBA to take the information in Excel and automatically fill that information into a fillable PDF? Almost like a mail merge or using bookmarks to place the information into the PDF. Is this doable?

Thanks for all your help,

-B

[Dr.K]

Yup, I do a similar thing, with FDF files. FDF files contain data to be imported/exported from a PDF form.

There is a developers Object Library to help automate the creation/parsing of FDF files,
but its a simple text file format, so I build them manually using the FileSystemObject.


1. The PDF form MUST have named Data Fields everywhere that you need to import data.

2. Build FDF data file from an Excel spreadsheet, with Data Field name that corrrspond to your PDF form.

3. Import the FDF data into the PDF file and save it.

Step 3 can be done manually from the Acrobat GUI, or it can be done with the Object Model from VBA.

To get you started, here is a sample 3 line FDF file:
[vba]%FDF-1.2
1 0 obj <<
/FDF <<
/Fields
[
<</T(DataFieldName)/Kids[<</T(0)/V(DATA)>><</T(1)/V(DATA)>><</T(2)/V(DATA)>>)>>]>>
<</T(DataFieldName)/Kids[<</T(0)/V(DATA)>><</T(1)/V(DATA)>><</T(2)/V(DATA)>>)>>]>>
<</T(DataFieldName)/Kids[<</T(0)/V(DATA)>><</T(1)/V(DATA)>><</T(2)/V(DATA)>>)>>]>>
]
>>
>>
endobj
trailer
<</Root 1 0 R>>
%%EOF[/vba]


And here is some code to do a mass import of FDF data:
[vba]Private Sub PDF_MassImport()

Dim PathIn As Variant 'Path to Input folder
Dim PathOut As Variant 'Path to output folder
Dim InputFName As Variant 'Fullname of FDF input file
Dim strFName As String 'temp str used to iterate through PDF files
Dim x As Integer
Dim NumPDFs As Integer
Dim Prog As Integer
Dim AcroApp As Object
Dim AVDoc As Object
Dim PDDoc As Object
Dim formApp As Object
Dim formFields As Object

'confirm execution
If Not MsgBox("This macro opens PDF files, imports data from an FDF data file,
and saves the PDF file to an output folder." _
& vbLf & "It updates every PDF file in the target folder." _
& vbLf & vbLf & "Click ""Yes"" to continue, ""No"" to quit and exit.", _
vbYesNo + vbQuestion + vbDefaultButton2, "Confirm Macro execution") = vbYes Then Exit Sub

'Check to see if Acrobat is installed and ready to go
If AcrobatReady(AcroApp) = False Then Exit Sub


'Get input folder
PathIn = GetOpenFileNameFrom(BuckSlipFolder, "pdf", "Select a PDF file from the input folder")
If PathIn = False Then Exit Sub
PathIn = FNameStrip(CStr(PathIn))

'Get output folder
Do
PathOut = BrowseForFolder("Select Output Folder:", Environ("USERPROFILE") _
& "\Desktop\")
If PathOut = False Then Exit Sub
If PathOut = PathIn Then
MsgBox "Input and Output folders cannot be the same." _
& vbLf & "Please select a different folder.", _
vbExclamation, "Output folder error!"
Let PathOut = False
End If
If Dir(PathIn & "\*.pdf", vbNormal) = Empty Then
MsgBox "That folder doesn't contain any PDF files!", _
vbExclamation, "No PDF Files found"
Let PathOut = False
End If
Loop Until Not PathOut = False

'Get input FDF file
Do
InputFName = GetOpenFileNameFrom(BuckSlipFolder, "FDF", _
"Select Performance Data file to import from")
If InputFName = False Then
If Not MsgBox("Macro cannot continue without an FDF data file." _
& vbLf & vbLf & "Click ""OK"" to browse for a data file, or" _
& vbLf & "Click ""Cancel"" to quit this macro and exit." _
, vbOKCancel + vbExclamation, "FDF Data file not found") = vbOK Then Exit Sub
End If
Loop Until Not InputFName = False

Application.StatusBar = "Importing FDF Data into PDF files..."

'count PDF files in directory
NumPDFs = 0
strFName = Dir(PathIn & "\*.pdf", vbNormal)
Do Until strFName = Empty
NumPDFs = NumPDFs + 1
strFName = Dir
Loop

'begin looping through PDF files
Let x = 0
Let Prog = 0
strFName = Dir(PathIn & "\*.pdf", vbNormal)

Set AVDoc = CreateObject("AcroExch.AVDoc")
Set formApp = CreateObject("AFormAut.App")

Do Until strFName = Empty
AVDoc.Open PathIn & "\" & strFName, ""
Set PDDoc = AVDoc.GetPDDoc
Set formFields = formApp.Fields

formFields.ImportAnFDF (InputFName)
PDDoc.Save 33, PathOut & "\" & strFName

Set formFields = Nothing
PDDoc.Close
Set PDDoc = Nothing
AVDoc.Close (-1)
strFName = Dir
Prog = Prog + 1
Application.StatusBar = "Importing FDF Data into PDF files..." _
& Int((Prog / NumPDFs) * 100) & "% complete "
Loop

'close out all Acrobat objects
Set formApp = Nothing
Set AVDoc = Nothing

AcrobatClose AcroApp

Application.StatusBar = False

MsgBox "Number of PDF files processed: " & NumPDFs & " ", _
vbOKOnly + vbInformation, "Mass PDF data import complete!"
End Sub
[/vba]

Note that this codes relies on some Functions that I've built, so you will have to modify it before it works.


READ THE ACROBAT DEVELOPERS KIT FOR MORE INFO!

Hope that helps!

[bryVA]

Thank you. So I need to have an FDF file or does the code place the information into FDF and the fill it into the PDF?

Thanks for the information. I will read up on the Acrobat Developers Kit.

-B

[Dr.K]

Nope, that sub just imports a single FDF file into a folder full of PDF files, and then saves the output in a new folder. The compiler that builds the FDF files is a separate program, and its very text intensive.

If you don't want to build the FDF from scratch, you can use the Object model FDF tools that Adobe gives you. OR... you can just build the PDF form as you want it (making sure you properly name the fields), and then you can create an FDF file by directly exporting the PDF data. Now you have an FDF to work from. Good luck.