Problem to merge multiple PDF with Access VBA

PDFCreator
1

Hi all.
I’m trying to merge multiple access report (exported as single PDF) to one single PDF.

I found a great help on this forum with some code (sorry I don’t remember the author of the post, it was posted for Excel), and I set the code to work with my “problem”.

It seems to work fine: I’ve 3 PDFs (in this case 3 single pages PDF) to merge to 1, and it does it, I get a single PDF (3 pages)

Then I deleted this new created file and re-run the routine, but instead to get once again the merged one (3 pages) I get a single PDF but with only the first PDF inside (1 page)
If I delete this new file and re-run again the routine, I get the right file (3 pages); but if I delete that file and re-run, I get the single page again. And so on…

Practically: during the “odd-merging” I get the right ones, during the “even-merging” I get the wrong ones

I don’t understand why…

Thanks for any advice

Andrea

Here comes the code (the part of the code where I define all the vars is working fine so I omitted that part):

Dim oPDF As PdfCreatorObj
Set oPDF = Nothing
Set oPDF = New PdfCreatorObj

'On Error GoTo EndSub ’ this is commented out for debuging purposes
Dim q As PDFCreator_COM.Queue

Set q = New PDFCreator_COM.Queue

oPDF.AddFileToQueue sFilenames(1)

oPDF.AddFileToQueue sFilenames(2)

oPDF.AddFileToQueue sFilenames(3)

q.Initialize
Debug.Print "oPDF isinstancerunning: " & oPDF.IsInstanceRunning ’ close Excel and open if true.

q.WaitForJobs 2, 5

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 (sPDFPath & “” & sPDFName)
Debug.Print job.IsFinished
Debug.Print job.IsSuccessful
Debug.Print "q.Count3: " & q.Count
Wend

EndSub:

q.ReleaseCom

2

UPDATE

I found some issue that maybe can be the cause of the problem.

while “even-merging” process, the queue is not well processed.
I started the pdfcreator.exe and it reports the presence of files queued. If I open the queue I can see the 2nd and the 3rd pages “waiting for merging”

I tried by adding a line q.Clear before the q.ReleaseCom at the end of routine, but nothing change

I don’t understand while during the “even-merging” process it seems to ignore the line q.MergeAllJobs

Andrea

3

I have the same issue, but hadn’t worked out that it’s happening on the even/odd cycle. Thanks for working it out!

Now that I know, I’ll just set it up to do a dummy merge between each of my actual merges, so they should work fine.

4

Nice to ear I wasn’t the only one with this problem :relaxed:

What do you mean with “set up a dummy merge” ?

5

I haven’t got it working yet, but I’m sure I’ll work it out.

The basic idea is that it swaps between merging correctly and incorrectly each time it runs (and I believe it resets on startup, but I’ll need to test that a few more times to be sure).

Making use of that, I’m aiming to run

  • Desired function
  • Dummy function

Desired function is the one I want to run. Dummy function is a quick one that merges two sample pdf files (if they’re 1 page it’s faster).

Once I pull it off, the desired function will be on the ‘odd’ (so it works every time) and the dummy function will be on the ‘even’ (it doesn’t matter that it keeps only 1 page of the merged docs, it’s only there so that the desired function works properly).

I haven’t quite got it working yet, but I’ll look at it again in a couple of weeks and I expect to get it working.

Hope that helps!

6

I’m at the same point as you are. The code only works every second time. The PdfCreatorObj seems to print to a wrong instance of the queue every second time. Opening PdfCreator after some failed tries shows me the queue with the missing PDFs. I read somewhere that AddFileToQueue only handles PS-files. Obviously it also handles PDF file… I also tried it with oPDF.PrintFile or with oPDF.PrintFileSwitchingPrinters, but they both did not work.

Does anyone have an idea of how to make this code running correctly?

For those who are interested, here is my (similar) Code in Access VBA:

Const sMergedPDFname = "C:\test.pdf"

Dim objPdf As PDFCreator_COM.PdfCreatorObj
Dim objQue As PDFCreator_COM.Queue
Dim objPjb As PDFCreator_COM.PrintJob

Sub PDFCreatorCombine()
    On Error GoTo ErrPdf
    If Len(Dir(sMergedPDFname)) > 0 Then Kill sMergedPDFname
    Set objQue = New PDFCreator_COM.Queue
    With objQue
        Set objPdf = New PDFCreator_COM.PdfCreatorObj
        .Initialize
        objPdf.AddFileToQueue "C:\AGB_DE.pdf"
        objPdf.AddFileToQueue "C:\AGB_FR.pdf"
        objPdf.AddFileToQueue "C:\AGB_IT.pdf"
        .WaitForJobs 3, 2
        Debug.Print "Queue.Count after send " & .Count
        ListJobs
        .MergeAllJobs
        Set objPjb = .NextJob
        objPjb.ConvertTo sMergedPDFname
        .Clear
    End With
ExitPdf:
    If Not objQue Is Nothing Then objQue.ReleaseCom
    
    Set objPdf = Nothing
    Set objQue = Nothing
    Set objPjb = Nothing
    Exit Sub

ErrPdf:
    FMsgBox Error, vbExclamation
    GoTo ExitPdf
End Sub

Sub ListJobs()
    Dim i As Integer
    For i = 0 To objQue.Count - 1
        Set objPjb = objQue.GetJobByIndex(i)
        Debug.Print "- " & objPjb.PrintJobInfo.PrintJobName
    Next i
    Set objPjb = Nothing
End Sub