Any idea if the VBA bug "works every second time" will be adressed?


#1

The bug:
I also created some VBA code in access to merge two pdf files. When doing this it only works every second time. I am using PDFCreator 3.4.1 This bug has been discussed before:

COM PdfCreator MergeFiles with Excel VBA - 2018
Problem to merge multiple PDF with Access VBA - 2017
Merging Files only works every second time Excel VBA - 2018
Merge PDFs with Excel VBA - 2018
Queue.MergeAllJobs() leaves a job orphaned - 2017

In the first forum, there is a work around with a loop. In a different forum post: https://www.mrexcel.com/forum/excel-questions/870539-combine-pdfs-using-vba.html looks like it might have a solution with "unbound" function but i couldn't seem to get it working. The last link above seems to attribute the error to MergeAlljobs.

My Aim:
I am wondering if there will be any fix to this bug?

Ideas:
the bug seems to be about adding files to the queue. I am trying to add two files. The first file is always added but the second file is only added every second try.

Cheers,

A


#2

For my issue, Queue.MergeAllJobs() leaves a job orphaned, I realized that I was trying to use an unsupported method - I was trying to enque non postscript files, pushing them directly to the PDFCreator queue. Instead, I have a working solution by using Acrobat Reader to "print" to the PDFCreator printer; this provides the postscript file that PDFCreator expects and it queues the job. Here's some code snippets that work for my implementation:

Public Sub PDFCreatorPrint(vFromPaths As Variant, fullPath As String, Optional vAddBlankPage As Boolean, Optional vPageCount As Long)
Dim PDFCreatorQueue As Variant
Dim printJob As Variant

Set PDFCreatorQueue = CreateObject("PDFCreator.JobQueue")

On Error GoTo Err_exit

Set printJob = CreateObject("PDFCreator.PdfCreatorObj")

On Error GoTo Err_exit

If Not isPDFCreatorRunning Then
    PDFCreatorQueue.Initialize
Else
    Set PDFCreatorQueue = Nothing
    Set printJob = Nothing
    Exit Sub
End If

...
SetApplicationPrinter "PDFCreator" 'custom procedure to set Application.Printer
...
'timing parameters
Dim dTime As Date, T As Integer, beg As Integer, n As Date, pdfOutput
n = Now
T = 30
beg = 0

'Queue the documents contained in vPath
Dim jobCount As Integer, vPath, strExecutable As String
jobCount = 0
For Each vPath In Split(CStr(vFromPaths), ",")
    If Dir(CStr(vPath)) <> "" Then 'file exists
        If getAdobeReaderPath And gAcroRd32 <> "" Then 'Acrobat Reader is installed and recognized
            strExecutable = Chr$(34) & gAcroRd32 & Chr$(34) & " /N /H /T "
            strExecutable = strExecutable & Chr$(34) & vPath & Chr$(34)
            strExecutable = strExecutable & " " & Chr$(34) & Application.Printer.DeviceName & Chr$(34)
            
            Shell strExecutable, vbHide 'use Acrobat Reader to "print" to the PDFCreator printer, i.e., queue each document
        
            DoCmd.Hourglass True
            
            dTime = DateAdd("s", T, Now)
            jobCount = jobCount + 1
            
            Do Until PDFCreatorQueue.Count = jobCount Or Now > dTime 'wait
                'iterate
                beg = beg * 0
            Loop
            
            DoCmd.Hourglass False
            
            'check if file made it into the queue
            If PDFCreatorQueue.Count < jobCount Then
                MsgBox "PDFCreator - unable to add the file [" & vPath & "] to the queue.", vbOKOnly, "PDFCreator"
            End If
        End If
    Else
        MsgBoxCall "Unable to locate " & vPath & " - file does not exist.", vbOKOnly, "PDFCreator"
    End If
Next vPath

dTime = DateAdd("s", 60, Now)
T = 7
beg = 0
Do Until PDFCreatorQueue.Count = jobCount Or Now > dTime 'wait
    If Not PDFCreatorQueue.WaitForJobs(jobCount, T) Then
        'iterae
        beg = beg + T
    End If
Loop
If PDFCreatorQueue.Count > 0 Then
    
    PDFCreatorQueue.MergeAllJobs
    
    Set printJob = PDFCreatorQueue.NextJob
    
    printJob.SetProfileSetting "OpenViewer", "False"
    
    printJob.ConvertTo (fullPath) 'produce the final merged PDF doc
    
    If (Not printJob.IsFinished Or Not printJob.IsSuccessful) Then
        MsgBox "PDFCreator - Could not convert the file: " & fullPath, vbOKOnly, "PDFCreator"
    End If
End If

On Error GoTo 0

Err_exit:
If Err.Number <> 0 Then
MsgBox "Could not create the file at " & fullPath & ". An error occurred while printing to the PDFCreator printer " & Application.Printer.DeviceName & ". Error " & Err.Number & " - " & Err.Description, vbOKOnly, "PDFCreator Error"
End If

PDFCreatorQueue.ReleaseCom

Set PDFCreatorQueue = Nothing
Set printJob = Nothing

...


#3

Hi,

the COM example "MergedMultipleFiles2Tif" from the Testpage2PDF.docm prints the active document three times and merges all jobs afterwards, this works without any issues and every time.

Best regards

Robin


#4

Hi @Matt_Trost, thank you for responding. That is an interesting solution possibly to the same problem. I am currently using the loop work around (link provided in first post). I think if I was going to do that I would rather do it in python. Similar level of shell code but much faster processing.

Hi @Robin.W, yeah that is one of the reasons why I think the bug is an issue with adding the PDFs to the queue. I am using PdfCreatorObj.AddFileToQueue. The other reason is that my code is based on the COM example and the Count in the Msg Box ["there are now " & Queue.Count] before the merge would go 1,2,1,2 etc when run.

I do wonder if I can create more then one PdfCreatorObj.. hmm. Another interesting factor is that my code will through an error if file2 doesn't exist. So some of oPDF.AddFileToQueue file2 works.

Here is my code anyway:

Private Sub Command42_Click()
    On Error GoTo MyErrorHandler
    
    Dim fullPath
    Dim PDFCreatorQueue As Variant
    Dim printJob As Variant
    Dim oPDF As Variant
    Dim i As Integer
    
    Set PDFCreatorQueue = CreateObject("PDFCreator.JobQueue")
    Set oPDF = CreateObject("PDFCreator.PdfCreatorObj")  'PDFCreator.clsPDFCreator
    
    file1 = Application.CurrentProject.Path + "\" + Trim(Me!Code.Value) + ".pdf"
    file2 = Application.CurrentProject.Path + "\" + Trim(Me!Code.Value) + "_LN.pdf"
    
    
    fullPath = Application.CurrentProject.Path & "\" + Trim(Me!Code.Value) + "_Merged.pdf"
    MsgBox "Initializing PDFCreator queue..."
    
    MsgBox "Sending the 2 files to the queue. This can take a minute.."
    
    PDFCreatorQueue.Initialize
    
    DoCmd.Hourglass True  ' turn on Hourglass
TOP:
    oPDF.AddFileToQueue file1
    oPDF.AddFileToQueue file2

    If Not PDFCreatorQueue.WaitForJobs(2, 15) Then
        If i < 6 Then
            PDFCreatorQueue.Clear
            i = i + 1
            GoTo TOP
        End If
    Else
        DoCmd.Hourglass False ' turn off hourglass
        MsgBox "There are now " & PDFCreatorQueue.Count & " file(s) in the queue to be merged."
        
        PDFCreatorQueue.MergeAllJobs
        Set printJob = PDFCreatorQueue.NextJob
        printJob.SetProfileByGuid ("DefaultGuid")
        
        MsgBox "PDFCreator will now create the merged file. This can take a few minutes for large files."
        DoCmd.Hourglass True  ' turn on Hourglass
        printJob.ConvertTo (fullPath)
        DoCmd.Hourglass False ' turn off hourglass
        
        If (Not printJob.IsFinished Or Not printJob.IsSuccessful) Then
            MsgBox "Creation failed: Could not merge files or file count was not 2."
        Else
            MsgBox "Creation finished successfully"
        End If
    End If

    PDFCreatorQueue.ReleaseCom
    Exit Sub
    
MyErrorHandler:
    PDFCreatorQueue.ReleaseCom
    Dim Msg As String
    Msg = "Error No " & Err.Number & ": " & Err.Description
    
    If Err.Number = -2146233079 Then
        MsgBox Msg + " Please clear the queue, turn off PDFCreator and try again."
    Else
        MsgBox Msg
    End If


End Sub

#5

Hi,

PdfCreatorObj.AddFileToQueue can only be used to add Postscript files to the queue, at least according to current documentation. Adding PDFs might somehow work, as PDFCreator does generally support direct PDF processing, but the issue you are describing probably won't happen if you print the files to the queue instead. If you only need to merge the PDFs with PDFCreator, you could however work around this and use the command line interface instead, as this supports direct PDF processing/merging without printing.

Best regards

Robin


#6

Thank you @Robin.W,

Ok, so the is the same as Matt_trost's. Can you use PDFCreator to print files to the queue?

Currently, there seems to be 2 options: Adobe print to PDFCreator. In which case I would rather use python. Or PDFCreator command line, for which I would rather use python.

However, the primary need is to make it easy for the end user. Ideally, just install the PDFCreator/Adobe/python file and it work without having to configure the VB script etc. This seems to leave Matt's option or am executable python script.


#7

Hi,

the PDFCreator object has a void PrintFile(string filepath).
This however will just use .NET to lookup and execute the command of the "print to" shell verb, which will then probably just use your default viewer to print the file to the PDFCreator printer.
There is a rather old ticket in our system to enable direct processing of PDFs though the COM interface, I can bring it up in our next team meeting and see if it can get implemented in the near future.

Best regards

Robin