Merge PDFs with Excel VBA

PDFCreator PDFCreator English
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
3 Likes
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.

1 Like
8

Good Evening! In your code, before line 4 (between Dim file2 As String and file1 = This...), I put:

'-----
TOP:
'-----

And before q.MergeAllJobs, I put:

'-----
Dim Return As Integer
If q.Count < 2 And Return < 6 Then
q.Clear
q.ReleaseCom
Return = Return + 1 'count the number of times this action returned without proceeding
GoTo TOP
ElseIf Return >= 6 Then
MsgBox "The merge PDF return a error."
outPath = ThisWorkbook.Path & Application.PathSeparator & "ERROR_MERGE.pdf"
End If
'-----

I chose 6 times to avoid looping forever and because I think of merges involving more than 2 files.

As the error occurrence pattern is every 2 times, on the first return, at most, the queue is already correctly defined.

With this, you do not need to quit Excel every 2 times to avoid the error.

9

Hi eirjens!

Its a great code! Very good to have a quick start with my tasks!

Thanks a lot for sharing!!!

10

Hi eirjens,

You code is fabulous, thank you so much! It was exactly what I was looking for...
One thing tho...is there any way to stop the Adobe Acrobat (i'm using the standard 2017 version) from opening the merged files? I use it for a big number of documents and it keep adding the newly created files to the pile until eventually it stops (cannot have more than 50 pdfs open at the same time)

Massive thanks,
Anca

11

I want to generate PDF files from an excel file. After this coding runs, the result is that I will have multiple PDF files with the suffix "-index.pdf" - (e.g., 875458-indexl.pdf ) I can change the suffix to anything.

And I will extract another set of PDF files from a zip file that have the identical file name as 875458.pdf .

At the end, in one folder, there will be multiple pairs of files as below.

875458.pdf
875458-index.pdf
875460.pdf
875460-index.pdf
954784.pdf
954784-index.pdf

My goal is to find a way to merge each pair of files into one file.

The end result would be as below.

875458.pdf 
875460.pdf
954784.pdf