Merge multiple pdf or ps (distiller) files into one single pdf in VBA?

[cogivina]

Hi,

I'm using the code from this thread below:

http://www.tek-tips.com/viewthread.cfm?qid=416330

I'm able to create a pdf file from a specific excel range name, but I need help to write code in order to merge the all distiller files into one single pdf file.

Thanks.

Dim PSFileName As String, PSFile As String
Dim PDFFileName As String

Dim fs As FileSystemObject
Dim NewPs As Variant

PSFile = "C:\myPostScript_"
PDFFileName = "C:\myPDF.pdf"

' Print the Excel range to the postscript file
Dim MySheet As Worksheet

Set MySheet = ActiveSheet

For i = 1 To 2
Print_Sel = "PrintSel_" & i
PSFileName = PSFile & i & ".ps"

MySheet.Range(Print_Sel).PrintOut copies:=1, preview:=False, ActivePrinter:="Acrobat Distiller", printtofile:=False, collate:=True, prtofilename:=PSFileName

Next i

''''Here - I don't know how to merge PSFileName1 and PSFileName2 into one single PDF file.

' Convert the postscript file to .pdf
Dim mypdf As PdfDistiller
Set mypdf = New PdfDistiller

mypdf.FileToPDF PSFileName, PDFFileName, ""

'''''''''''''''''''

' Delete ps - distiller files - after pdf is created.
Set fs = New FileSystemObject
With New FileSystemObject
For i = 1 To 2
PSFileName = PSFile & i & ".ps"
.DeleteFile PSFileName
Next i
End With

 

[macropod]

Hi cogivina,

There are three ways you could deal with this:
1. Name a discontiguous range in your workbook, spanning PrintSel_1 & PrintSel_2, then use that new named range as your print range;
2. Use the standard copy & concatenate functions to append one .ps file to the other (I *think* postscript files merged this way will work); or
3. Use Adobe Acrobat or Ghostscript to merge the PDF files after they've been created.

I'd go for '1', which also means you could print direct to PDF by defining the ActivePrinter appropriately, rather than going through the print-to-file then distil-to-pdf process you now have.

Cheers

[MS MVP - Word]