Merging PDFs and Allowing for Filling of Adobe Fields

[nreynolds]

I'm utilizing the MergePDF macro kindly supplied by Kenneth Hobs in response to a previous question (see link below).

http://www.vbaexpress.com/forum/showthread.php?51366-Merging-PDFs-Based-on-Checklist-Selection-in-Excel

My issue is now allowing for a VBA macro from excel to populate fields that are on the original pdfs. I tried using the code presented in this thread:

http://www.vbaexpress.com/forum/showthread.php?25499-How-do-I-auto-populate-an-Acrobat-form-from-Excel

This coding works on the individual pdfs. However, after merging the pdfs (some of which are not set up as forms) the fields are not recognized by Adobe and therefore are unable to be filled. Any combination of these two subs would be incredibly useful. Ideally, I'd prefer to have the merged file populated from cells in Excel since the files being merged are standard forms and drawings that would be used on multiple occasions. However, if the individual files have to be populated prior to the merge, then I'd need to include some actions to clear out those fields on the original files.

Hopefully I described this adequately. If sample files are needed, I'm sure I can add some pdfs of what I'm using. I'm an amateur in VBA at best and my attempts at combining these subs have not worked thus far. Any assistance is appreciated...thanks for your time.

[Kenneth Hobs]

See if this helps with filling a pdf file with fields. http://www.vbaexpress.com/forum/showthread.php?53888-Excel-Export-Cells-to-PDF-Forms-(fillable)

[nreynolds]

Where should I even begin inserting the coding for the FDF file? Should I write it into the MergePDFs macro or should it be a stan-dalone macro applied after the merging process? Based upon some recent tests with the MergePDFs macro, I think it would be better to apply values to the PDF fields before the merge. Currently, after running the forms through the merge process, the first few pages retain their fields but subsequent pages lose them (i.e. there are no longer any fields to apply data to). Thoughts? Also, I'm not entirely certain what the linked solution is doing? It appears that it is writing a FDF file to then use to populate the fields in the PDF file...a basic breakdown could be useful just to wrap my head around it. Thanks again for your help.

[Kenneth Hobs]

The PDF must contain the fieldnames. You can use Acrobat to view those but the link shows how to get the field names using Acrobat. The FDF is the form file with the fieldnames and the values to insert in it. I created the routine to make creating the FDF file easy. As shown in the link's #2 post, the link that most reference for FDF tutorial is: http://www.excelhero.com/blog/2010/04/excel-acrobat-pdf-form-filler.html

[nreynolds]

Ken,

I've had to put this on the back-burner for a while while working on some other projects. Now I'm back at it and have a few more questions for you. I've successfully been able to use the MakeFDF macro to fill form fields in individual pdf files. However, I have not been successful in combining this with the macro that merges these individual files into a single file. A basic description of what I'm trying to do: I have a number of standard engineering drawings that will be combined into a singular drawing package. Within Excel, the user selects which drawings to include in the package and simply hits a button to generate the merged pdf file. Each of these individual drawing sheets has unique fields that should auto-populate from information within the excel workbook. I've been able to accomplish this for the individual sheets with the MakeFDF macro but can't figure out how to combine this with the merge process. Some more help would be appreciated.

I've tried to accomplish this using two different setups. The first trial involved running the MakeFDF macro on each sheet selected by the user prior to the merge process. This works up to the point that it opens the .fdf file as a .pdf. I then have to select to trust this document from within the Acrobat application before the fields will populate. When the Merge process runs (called within the same macro called from the user toggled button), it still merges the pdf files, but does not include any filled fields. So, for this setup to work, is there a way to simply save that fdf file over the existing pdf file prior to merging (all behind the scenes without additional user interaction)? Does the fdf file need to be opened or can the process be done within the vba programming?

The second setup I've tried is to run the MakeFDF macro on the merged file that is formed within the Merge macro. The issue with this setup has been that the merged file does not recognize those fields after the merge process so the macro doesn't write any information. If I open up the merged pdf, the fields are there and able to be selected and written to, but when I try to edit these fields within Adobe Pro, it tells me that there are currently no fields associated within the file and if I would like Adobe to search for fields automatically. Thus, it almost seems that the Merge process is somewhat flattening the pdf in respect to recognizing fields.

I think the most potential for success is in the first method. At this point, I'm open to all suggestions!

[Kenneth Hobs]

If you can not automatically merge to an FDF and save as a PDF, then merging PDF files may not be feasible. I gather that PDF forms is what you want.

If you do want to merge PDF files, let me know which you prefer.
1. Adobe Acrobat
2. 3rd pary program with VBA object, PDFCreator (earlier version better than later version).
3. Other 3rd Party program command line method (VBA automated of course): e.g. PDFtk, PDFfill, PDFsam, etc.

[nreynolds]

Option 1....we're using Adobe. I have Pro but the anticipated user will only have Reader.

I could be wrong, but the referenced MakeFDF coding opened the .fdf file as a .pdf file. It didn't necessarily auto-save. I struggled with coming up with a workable code that actually saved the opened file as a pdf and closed it. Of course, if I'm having to individually select to trust the document every time, it becomes a mute point since I don't want to be doing that every time for every file...

The end goal is for a single PDF file that consists of merged drawing files that were selected by the user. The previous Merge macro that you helped me out with worked just fine in that regard. The issue now becomes we're trying to add the feature that these drawings are auto-filled based upon the information and design calculations performed in the excel workbook. So I made each of those existing pdf files (previously "flat") into forms and added text fields. The idea is to have these fields filled in the final pdf package...

If you want some sample files or a look at the existing Merge coding let me know. Although, you should be rather familiar with the Merge code!

Thanks for your help, Ken.

[nreynolds]

My terminology could be a little off too...it would be preferable to have the final product still be in "form" format. But really, the simplest way to have these files filled and merged would be just fine...even if we wind up losing the "form" status of the file.

[Kenneth Hobs]

(1) will not work if your end user is going to be doing the merge and does not have Acrobat.

For you, here is (1).

Sub MergePDFs(MyPath As String, MyFiles As String, Optional DestFile As String = "MergedFile.pdf")' ZVI:2013-08-27 http://www.vbaexpress.com/forum/showthread.php?47310-Need-code-to-merge-PDF-files-in-a-folder-using-adobe-acrobat-X
' Reference required: VBE - Tools - References - Acrobat

Dim a As Variant, i As Long, n As Long, ni As Long, p As String
Dim AcroApp As New Acrobat.AcroApp, PartDocs() As Acrobat.CAcroPDDoc

' Adjust MyPath string if needed.
If Right(MyPath, 1) = "\" Then p = MyPath Else p = MyPath & "\"
a = Split(MyFiles, ",")
ReDim PartDocs(0 To UBound(a))

' Save to MyPath folder if target folder for merged PDF file was not input. (ken)
If InStr(DestFile, "\") = 0 Then DestFile = p & DestFile

On Error GoTo exit_
If Len(Dir(DestFile)) Then Kill DestFile
For i = 0 To UBound(a)
' Check PDF file presence
If Dir(p & Trim(a(i))) = "" Then
MsgBox "File not found" & vbLf & p & a(i), vbExclamation, "Canceled"
Exit For
End If
' Open PDF document
Set PartDocs(i) = CreateObject("AcroExch.PDDoc")
PartDocs(i).Open p & Trim(a(i))
If i Then
' Merge PDF to PartDocs(0) document
ni = PartDocs(i).GetNumPages()
If Not PartDocs(0).InsertPages(n - 1, PartDocs(i), 0, ni, True) Then
MsgBox "Cannot insert pages of" & vbLf & p & a(i), vbExclamation, "Canceled"
End If
' Calc the number of pages in the merged document
n = n + ni
' Release the memory
PartDocs(i).Close
Set PartDocs(i) = Nothing
Else
' Calc the number of pages in PartDocs(0) document
n = PartDocs(0).GetNumPages()
End If
Next

If i > UBound(a) Then
' Save the merged document to DestFile
If Not PartDocs(0).Save(PDSaveFull, DestFile) Then
MsgBox "Cannot save the resulting document" & vbLf & DestFile, vbExclamation, "Canceled"
End If
End If

exit_:

' Inform about error/success
If Err Then
MsgBox Err.Description, vbCritical, "Error #" & Err.Number
ElseIf i > UBound(a) Then
MsgBox "The resulting file was created in:" & vbLf & p & DestFile, vbInformation, "Done"
End If

' Release the memory
If Not PartDocs(0) Is Nothing Then PartDocs(0).Close
Set PartDocs(0) = Nothing

' Quit Acrobat application
AcroApp.Exit
Set AcroApp = Nothing

End Sub