COM PdfCreator MergeFiles with Excel VBA

Hi,
I want to merge pdf files throught Excel vba. I have read the documentation and I can merge the pdf files, so it´s OK.

The problem is that only works one of two times, the other time the some jobs didn´t arrive at Queue. That happens always 1 of 2 times: the second time, the fourth time... and so on
When this happens, the jobs didn´t arrive, because I get the message "The jobs didn´t arrive" (see code below)

If after I merge files, I restart Excel, then It doesn´t happen... but restart Excel every time is no good solution for users

When it doesen´t work (second time, fourth time...), is like the files continue in the Queue. If I try to print directly from excel (directly, not by code) then it appears some files in the PDFCreator memory (see image)

I have installed the latest version 3.2.2

This is the code I have used:

'File names
Const FILE1 As String = "C:\File1.pdf"
Const FILE2 As String = "C:\File2.pdf"
Const MERGED_FILE As String = "C:\Merge.pdf"

'declare objects
Dim oQueue As New PDFCreator_COM.Queue
Dim oPdfCreatorObj As New PDFCreator_COM.PdfCreatorObj
Dim oPrintJob As PDFCreator_COM.PrintJob

'initialize queue
oQueue.Initialize

'add files to queue
oPdfCreatorObj.AddFileToQueue FILE1
oPdfCreatorObj.AddFileToQueue FILE2

'wait for jobs for 15 seconds
If oQueue.WaitForJobs(2, 15) = False Then
MsgBox "The jobs didn´t arrive"
oQueue.ReleaseCom
Exit Sub
End If

'merge all jobs
oQueue.MergeAllJobs

'get the job and print it
Set oPrintJob = oQueue.NextJob
oPrintJob.SetProfileByGuid ("DefaultGuid")
oPrintJob.ConvertTo (MERGED_FILE)

'release
oQueue.ReleaseCom

Thanks!

1 Like

Hi,

I have seen this happen before and I think you can work around it by only initializing the queue once and releasing the COM object after you have finished merging all files. We will have a closer look at this for the next PDFCreator update.

Best regards

Robin

Hi,

I´ve tried 2 options, but the result is still the same: it only works properly one of two times:

  • Option 1:
    Initialize Queue
    Merge fist time (works ok)
    Merge second time (dosen´t work)
    Release Queue

  • Option 2:
    Initialize Queue
    Merge (works ok)
    Release Queue
    Initialize and Release without merge
    This option only works 1 of 2 times.. like always

Can I try something more before the new version?
Thanks

In your code, in the begin I put:

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

And before oQueue.MergeAllJobs, I put:

'-----
Dim Return As Integer
If oQueue.Count < 2 And Return < 6 Then
oQueue.Clear
oQueue.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."
MERGED_FILE = "C:\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.

P.S.: Change:

'-----
wait for jobs for 15 seconds
If oQueue.WaitForJobs(2, 15) = False Then
MsgBox "The jobs didn´t arrive"
oQueue.ReleaseCom
Exit Sub
End If
'-----

for:

'-----
oQueue.WaitForJobs 2, 15
'-----

Hi,
I am new to this forum, but have used the above samples of code to get MS Access to merge two pdf documents. Thank you for the code
In my experimenting with the above samples, I noticed that I can halve the operating time of the above by adding an a loop to clear the job queue straight after it is initialised and then going back to "TOP" and rerunning the creation process ( Avg operating time no extra loop 11.4 seconds vs Avg time with extra loop added in 4.8 seconds)
Below is the code for the extra loop I added in

q.Initialize ' original line of code

If Cntr2 = 1 Then ' Cntr2 is dim as integer and set to 1 at very start (before TOP)
q.Clear
q.ReleaseCom
Cntr2 = 2
GoTo TOP
End If

Not sure why it works, but it also halves the run time for the late binding version as well 21 seconds to 11 seconds.

ALSO replaced the "wait"code with the following code, it allows the process to continue as soon as the correct number of files are in the queue - no longer have to wait the 10 seconds

Do Until q.Count >= 2
Sleep (500)
cntr1 = cntr1 + 1 ' dim cntr1 as integer at very start - before top
If cntr1 = 20 Then ' have waited 10 seconds go to start (TOP) and try again
q.Clear
q.ReleaseCom
GoTo TOP 'retry
End If
If cntr1 >= 40 Then ' have started again and still waited another 10 seconds, call the error and exit
MsgBox "error"
GoTo EndSub
End If
Loop

Hope these help

1 Like

Thanks for the feedback.

I simply removed the part of the code: "oQueue.WaitForJobs 2, 15" and it went right and fast. I do not know why it is necessary to wait for the jobs.

I have also adapted from "If and Then <6 Then" to "If and Then <10 Then" and "ElseIf Return> = 6 Then" to "ElseIf Return> = 10 Then" because in the tests I noticed that in some situations there were up to 6 returns to the 'TOP:'. So that the error never occurrency, I increased the number to 10 for safety.

I did not test the code you presented because in my case I do the union of files in variable quantity, from 2 to 24, but I saw that your was very good and congratulations for seeing the side effect (elapsed time) and let me know.

That's why I prefer to work as a team, because the power of reasoning is always magnified.

Thank you again.

In addition, after several tests successfull without terminating Excel at no one time, PDF Creator saved all the parts that have been merged in the background. A small side effect for a great solution.