Merge PDFs with Excel VBA


#1

I’m trying to merge some PDF’s with Excel VBA, using the reference to PDFCreator.COM.tlb.
Are there some examples of this done?

Also I get the error ‘user-defined type not defined’ for PDFCreator.PdfCreatorObj


#2

Hi,

In the program directory is are some examples for the COM interface for different languages: C:\Program Files\PDFCreator\COM Scripts

Best regards


#3

Thanks for the reply.
I made the code and it is working now. Just a little bug tho, if I merge the same files as last run it sometimes don’t merge all the files…one of the printjobs ends up in a queue. But that’s OK.

If anyone need it, here’s my code:
Sub mergePDF()

Dim file1 As String
Dim file2 As String

file1 = ThisWorkbook.Path & Application.PathSeparator & "P1.pdf"
file2 = ThisWorkbook.Path & Application.PathSeparator & "P2.pdf"

Dim outPath$
outPath = ThisWorkbook.Path & Application.PathSeparator & "outputFile.pdf"

Dim oPDF As PdfCreatorObj
Set oPDF = New PdfCreatorObj
oPDF.AddFileToQueue file1
oPDF.AddFileToQueue file2

Debug.Print "oPDF isinstancerunning: " & oPDF.IsInstanceRunning ' close Excel and open if true.

'On Error GoTo EndSub ' this is commented out for debuging purposes
Dim q As PDFCreator_COM.Queue
Set q = New PDFCreator_COM.Queue
q.Initialize
q.WaitForJobs 2, 10

Debug.Print "q.Count: " & q.Count ' here it prints either 1 or 2. Should always be 2.

q.MergeAllJobs

Dim job As PDFCreator_COM.PrintJob

While q.Count > 0
        Set job = q.NextJob
        job.SetProfileByGuid ("DefaultGuid")
        job.ConvertTo (outPath)
        Debug.Print job.IsFinished
        Debug.Print job.IsSuccessful
        Debug.Print "q.Count3: " & q.Count
Wend

EndSub:
q.ReleaseCom

End Sub

#4

Hi Eirjens!

Thanks for the code. I can’t seem to find any decent answers - everything I could find works only for the older versions.

Have you been able to fix the issue with the two files not being merged if you pick the same files?


#5

If I remember correctly I just made sure no files would have the same name.
The main code will also detect if there’s an error and stop/skip gracefully.


#6

Hello,
I need to merge 3 PDFs and to give a name to this file.
2 pages comes from Excel avec the rest from an existing PDF.
Is it possible to use Excel VBA to merge them ?
I use pdfforge manually to merge them but it takes time when you have a lot to do :slight_smile:
Thanks in advance for your help


#7

from sheets?
yes, you can merge any number of PDFs with VBA by adding the PDFCreator reference.
Make one sub to save the sheets as PDF, then tweak the code above to merge.

If all the PDFs are saved to the same folder, you can make a sub that read all the file-paths into an array, then pass the array to the merger and iterate it (the AddFileToQueue part).
I prefer reading all the files from a folder into a sheets, then mark the ones I want to merge.