I have an MS Access database that I use to create invoices for our charity. For each member exactly one invoice should be exported as PDF. The file name is equal to the invoice number.
In March 2020 I used it for the first time and it worked without problems. Now I have version PDFCreator 4.0.2 and there are considerable problems with the output. File name and print job do not always match. I have outsourced the VBA script to a function. The path with file name and ID is passed.
Up to number 46 file name and PDF content are correct. At number 47 is the error. Filename is correct, but the content of number 46.
I have tried to find the error, but with each run it is always different numbers that are not correct. The number of wrong files is also different.
My guess was that the print job could not be read into the queue in time. If this happens, it starts again from the beginning - until Export = True. But that seems not to be the reason. He had this several times and still exported correctly.
Which also happens sometimes: when the function is called for a new file, that the initialization cannot take place because it is already initialized. It seems that the ReleaseCom did not work in the previous run.
Do you have an idea?
I have found a solution.
Step 1: I run through everything and create a multiple array with ID and full path
Step 2: I have rebuilt the function and it is now called once at the end
This is my VBA Function:
Function Safe2PDF(invoices)
'invoices = Array(id, invoice_number, filePath)
Dim PDFCreatorQueue As Variant
Dim printJob As Variant
Set PDFCreatorQueue = CreateObject("PDFCreator.JobQueue")
Application.Printer = Application.Printers("PDFCreator")
PDFCreatorQueue.Initialize
n = UBound(invoices)
StartTime = Now
For i = 1 To UBound(invoices)
Me!txt_export_pdf = i & " / " & n 'Counter
path = invoices(i)(2)
DoCmd.OpenReport "Rechnung", acViewNormal, , "p.[ID] = " & invoices(i)(0), acHidden
If Not PDFCreatorQueue.WaitForJob(10) Then
Debug.Print "Timeout: " & invoices(i)(0) & " - print job did not reach the queue within 10 seconds"
Else
Set printJob = PDFCreatorQueue.NextJob
printJob.SetProfileByGuid ("DefaultGuid")
printJob.ConvertTo (path)
If (Not printJob.IsFinished Or Not printJob.IsSuccessful) Then
Debug.Print "Error: " & invoices(i)(0) & " - Could not convert the file: " & FullPath
Else
Debug.Print "OK: " & invoices(i)(0) & " - finish"
End If
End If
Next i
MsgBox "Time: " & Format(Now- StartTime, "hh:mm:ss"), , "Duration"
PDFCreatorQueue.ReleaseCom
Set PDFCreatorQueue = Nothing
End Function
