Timing problems: several documents to one PDF with one VBA
Posted: Mon Dec 10, 2007 3:39 pm
Hello,
I have some problems with printing several documents to one PDF with one VBA-script (Excel).
Basically, everything works. That means: when I wait until the PDF-file is printed or until a new printjob is added to the existing PDF-file (MsgBox after the printcommand with an OK-button), everything works fine. But that means also that printing this kind of documents cost a lot of time. I want the VBA-script to run unattended.
When I remove the MsgBox command, I get problems: “Error copying PDF document to destination file”. My conclusion is: the process of the running VBA-script and the process of the Bullzip printer are individual processes who are running independent. When the VBA-script generates a printcommand, then the Bullzip process is making the PDF-file or is adding pages to the PDF-file. But when the VBA-script proceeds independent of the Bullzip process, then the VBA-script can generate the next printcommand, while the Bullzip process is still busy making the PDF-file. Then you get the errormessage.
I tried to solve this with some VBA-code: check whether the PDF-file is open or not. If he is open: wait some more time. If he is closed: proceed with the VBA-script (see code below). But sometimes, I still get this error.
My questions:
- Is my analysis of the problem correct?
- Is there a more easy way to solve the problem?
- Is there a way to make the solution I found more secure?
Thanks if you can help me!
<code>
i = 1
newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 10
waitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait waitTime ' wait some time
Do While i < 50
On Error Resume Next
Open strFileNameFor Binary Access Read Write Lock Read Write As #1
Close #1
If Err.Number <> 0 Then
blnFileOpen = True
newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 5
waitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait waitTime
Err.Clear
Else
blnFileOpen = False
i = 50 ‘force end of loop
End If
i = i + 1
Loop
<code>
I have some problems with printing several documents to one PDF with one VBA-script (Excel).
Basically, everything works. That means: when I wait until the PDF-file is printed or until a new printjob is added to the existing PDF-file (MsgBox after the printcommand with an OK-button), everything works fine. But that means also that printing this kind of documents cost a lot of time. I want the VBA-script to run unattended.
When I remove the MsgBox command, I get problems: “Error copying PDF document to destination file”. My conclusion is: the process of the running VBA-script and the process of the Bullzip printer are individual processes who are running independent. When the VBA-script generates a printcommand, then the Bullzip process is making the PDF-file or is adding pages to the PDF-file. But when the VBA-script proceeds independent of the Bullzip process, then the VBA-script can generate the next printcommand, while the Bullzip process is still busy making the PDF-file. Then you get the errormessage.
I tried to solve this with some VBA-code: check whether the PDF-file is open or not. If he is open: wait some more time. If he is closed: proceed with the VBA-script (see code below). But sometimes, I still get this error.
My questions:
- Is my analysis of the problem correct?
- Is there a more easy way to solve the problem?
- Is there a way to make the solution I found more secure?
Thanks if you can help me!
<code>
i = 1
newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 10
waitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait waitTime ' wait some time
Do While i < 50
On Error Resume Next
Open strFileNameFor Binary Access Read Write Lock Read Write As #1
Close #1
If Err.Number <> 0 Then
blnFileOpen = True
newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 5
waitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait waitTime
Err.Clear
Else
blnFileOpen = False
i = 50 ‘force end of loop
End If
i = i + 1
Loop
<code>