Print to PDF using VBA and merge resulting PDFs into single document

[alexsheehan]

Hello there!


I have managed to create a way of mail-merging in Excel using an earlier version of my code below, basically pulling in large amounts of data incrementally to the same template "form" and then printing each in turn to a PDF. However, what I wanted to do was try and combine/merge these separate PDF print jobs into a single PDF file.


I have had a go at trying to prodice the relevant code myself, but cannot work out how to get it to function properly (correct syntax).


Can anyone help me out please?


My code;-

[pre]

Sub PrintForms()

Dim StartRow As Integer
Dim EndRow As Integer
Dim Msg As String
Dim i As Integer

Dim PDFFileName As String
Dim MySheet As Worksheet

'/ Automatically sets the save directory with no prompt

PDFFileName = "c:Documents and SettingsasheehanDesktopmyPDF.pdf"
Sheets("Form").Activate
StartRow = Range("StartRow")
EndRow = Range("EndRow")

If StartRow > EndRow Then
Msg = "ERROR" & vbCrLf & "The start row must be less than the end row!"
MsgBox Msg, vbCritical, APPNAME
End If

For i = StartRow To EndRow
Range("RowIndex") = i
If Range("Preview") Then
ActiveSheet.PrintPreview
Else
ActiveSheet.PrintOut

Set MySheet = ActiveSheet
MySheet.Range("Print_Area").PrintOut copies:=1, preview:=False, ActivePrinter:="Adobe PDF", printtofile:=True, collate:=True, prtofilename:=PDFFileName
End If
Next i

'/=====
'/ Wait until all print jobs have entered the print queue
Do Until Range("RowIndex") = "EndRow"
DoEvents
Loop

'/ Combine all PDFs into a single file and stop the printer

'/ the code below was leeched from another forum so may need 'tweaking', but I don't know how
With PDFFileName
.cCombineAll
.cPrinterStop = False
End With
'/

'Wait until the file shows up before closing PDF Creator
Do
DoEvents
Loop Until PDFFileName = PDFFileName

'/=====

Range("RowIndex") = 1

End Sub

[/pre]

 

[Luke M]

Not sure you can do it in Excel, but most pdf writers will let you combine multiple sheets and save them as one document. =/