Merge PDFs into single document using VBA

shirdesh · Oct 4th 2016

Hello I am new to this group and was wondering if anyone could help me with how to merge multiple pdf files in single file using VBA macro.
I have to create a final Pdf file that has all the different reports in it and i get individual reports in pdf format from different teams. I am using pdf creator version 2.3.2. I found the code online but it throws an error
when the code reaches .MergeAllJobs it throws an error saying Run time error and " The queue must not be empty". the test file i am testing does exists in those location that i mentioned in the code. below s the code for your reference.

appreciate all the help guys.

Code

Sub Test_PDFCreatorCombine()
Dim fn(0 To 1) As String, s As String
fn(0) = "C:\Users\SVellore\Documents\VBA\Report Tool\_Projects\_02-Templates\NA\temp1.pdf"
fn(1) = "C:\Users\SVellore\Documents\VBA\Report Tool\_Projects\_02-Templates\NA\temp2.pdf"
s = "C:\Users\SVellore\Documents\VBA\Report Tool\_Projects\_02-Templates\NA\PDFCreatorCombined.pdf"
PDFCreatorCombine fn(), s
If vbYes = MsgBox(s, vbYesNo + vbQuestion, "Open?") Then Shell ("cmd /c " & """" & s & """")
End Sub
'Macro Purpose: Print to PDF file using PDFCreator
' Designed for early bind, set reference to: PDFCreator - Your OpenSource PDF Solution
Sub PDFCreatorCombine(sPDFName() As String, sMergedPDFname As String)
Dim oPDF As PDFCreator.PdfCreatorObj, q As PDFCreator.Queue
Dim pj As PrintJob
Dim v As Variant, i As Integer
Dim fso As Object
Set fso = CreateObject("Scripting.FileSystemObject")
Set q = New PDFCreator.Queue
With q
'On Error Resume Next
.Initialize
If LBound(sPDFName) = 0 Then
.WaitForJobs UBound(sPDFName) + 1, 1
Else
.WaitForJobs UBound(sPDFName), 1
End If
Set oPDF = New PDFCreator.PdfCreatorObj 'PDFCreator.clsPDFCreator
i = 0
For Each v In sPDFName()
If fso.FileExists(v) Then oPDF.PrintFile v
i = i + 1
Next v
On Error GoTo EndNow
.MergeAllJobs
Set pj = q.NextJob
With pj
.SetProfileByGuid "DefaultGuid"
.SetProfileSetting "Printing.PrinterName", "PDFCreator"
.SetProfileSetting "Printing.SelectPrinter", "SelectedPrinter"
.SetProfileSetting "OpenViewer", "false"
.SetProfileSetting "OpenWithPdfArchitect", "false"
.SetProfileSetting "ShowProgress", "false"
.ConvertTo sMergedPDFname
End With
EndNow:
.ReleaseCom
End With
End Sub[COLOR=#333333][/COLOR]

Display More

Thanks,
shirdesh

donethat · Oct 4th 2016

Re: Pdf merging

Are you just starting out trying to use PDF Creator 2.3.2? Only help I can offer is that I was never able to successfully use PDF Creator 2.3.2 in VBA.
I'm stuck on 1.7.3 - fortunately that suits my needs.
I believe 1.7.3 is still available for download but I don't know if it supports the functions you are using.
Good luck

shirdesh · Oct 4th 2016

Re: Pdf merging

I am currently using VBA code to print excel sheets to pdf reports from pdf creator. its working fine with 2.3.2 version.
But i am not able to merge the pdf files in 2.3.2 as it throws error. do you have any alternate ways of merging pdf's using vba.

donethat · Oct 4th 2016

Re: Pdf merging

Sorry I don't.
Please tell me: what version of Excel you are on. I'm on 2016 via Office 365 but 2.3.2 did not work when I was on Excel 2010 either.
Just curious - maybe I have to try harder!

shirdesh · Oct 4th 2016

Re: Pdf merging

I am using excel 2013 and printing excel sheets works nicely through pdf creator. if you search online you can find code for printing of excel sheet through pdf creator.

**Kenneth Hobson** · Oct 5th 2016

Re: Pdf merging

Acrobat, not reader, would be the better 3rd party method. PDFsam is not bad.

For the newer version, this seemed to work, somewhat. It is slower than the older version of pdfcreator.

Code

'http://www.excelguru.ca/forums/showthread.php?4733-merging-multiple-PDF-files-into-a-single-PDF-file-via-VBA-macro
Sub Test_PDFCreatorCombine()
Dim fn(0 To 1) As String, s As String
fn(0) = "x:\pdf\ken.pdf"
fn(1) = "x:\pdf\ken2.pdf"
s = "x:\pdf\PDFCreatorCombined.pdf"
PDFCreatorCombine fn(), s
If vbYes = MsgBox(s, vbYesNo + vbQuestion, "Open?") Then Shell ("cmd /c " & """" & s & """")
End Sub
'Macro Purpose: Print to PDF file using PDFCreator
' (Download from http://sourceforge.net/projects/pdfcreator/)
' COM interface, http://www.pdfforge.org/pdfcreator/manual/com-interface
' Designed for early bind, set reference to: PDFCreator - Your OpenSource PDF Solution
Sub PDFCreatorCombine(sPDFName() As String, sMergedPDFname As String)
Dim oPDF As PDFCreator.PdfCreatorObj, q As PDFCreator.Queue
Dim pj As PrintJob
Dim v As Variant, i As Integer
Dim fso As Object
Set fso = CreateObject("Scripting.FileSystemObject")
Set q = New PDFCreator.Queue
With q
.Initialize
If LBound(sPDFName) = 0 Then
.WaitForJobs UBound(sPDFName) + 1, 1
Else
.WaitForJobs UBound(sPDFName), 1
End If
Set oPDF = New PDFCreator.PdfCreatorObj 'PDFCreator.clsPDFCreator
i = 0
For Each v In sPDFName()
If fso.FileExists(v) Then oPDF.PrintFile v
i = i + 1
Next v
.MergeAllJobs
Set pj = q.NextJob
With pj
.SetProfileByGuid "DefaultGuid"
.SetProfileSetting "Printing.PrinterName", "PDFCreator"
.SetProfileSetting "Printing.SelectPrinter", "SelectedPrinter"
.SetProfileSetting "OpenViewer", "false"
.SetProfileSetting "OpenWithPdfArchitect", "false"
.SetProfileSetting "ShowProgress", "false"
.ConvertTo sMergedPDFname
End With
.ReleaseCom
End With
End Sub

Display More

donethat · Oct 5th 2016

Re: Pdf merging

Thank you both for the info. I'll revisit my code and see if I can get 2.3.2 working.

shirdesh · Oct 5th 2016

Re: Pdf merging

Hello Kenneth,

Thanks for the reply.
But unfortunately i still have the error. I see the code you posted is same that i used.
at .MergeAllJobs i get an run time error and it says "queue must not be empty".
any thoughts on what could be the reason. i am using 2 files temp1 and temp2 for testing and they exist in the mentioned folder as well and are not password protected.

appreciate your help on this.

Thanks
Shirdesh

shirdesh · Oct 5th 2016