Waiting in VBA?

Topic

New Reply

I have the following code:

Code:

On Error GoTo Waitforpdfcreator
Try_Again:
       Name zBillPath & "rptAnnualBilling.pdf" As _
            zBillPath & "Bill" & Format(rst![OwnerID]) & ".pdf"
On Error GoTo 0
  .
  . 
  .
   GoTo GetOut
   
Waitforpdfcreator:
   Resume Try_Again

The purpose of this code is to wait until PDFCreator creates a PDF file from an Access report so I can successfully rename it, since the next iteration will create the report with the same file name. The question is do you know a better method of making VBA wait until the .PDF file is created before it continues?

PS. The code works fine it just seems very inelegant!

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Replies

32-bit Windows has an API function that is useful for this situation, called Sleep.

Example:

Top of module:

Code:

Option Explicit
'Declare Sleep API
Private Declare Sub Sleep Lib "kernel32" (ByVal nMilliseconds As Long)

In your procedure:

Code:

Dim ieSrc As New InternetExplorer
With ieSrc
    .Visible = True     'show window
    .navigate strURL 'open page
    While Not .readyState = READYSTATE_COMPLETE
        Sleep 500      'wait 1/2 sec before trying again
    Wend
End With

Edit: In your procedure, you might test using:

While Dir(yourfilename) = vbNullString

Reply | Quote

Jefferson,

Thanks much works great. Surprisingly enough that works just fine in Windows 7 64 Bit with Office 2003 32bit! I read here #3 that the 64 bit version has both 32 & 64 bit versions of the API and automatically selects the proper code?

I also wound up keeping my old code, slightly modified with another sleep call, because even after bumping the wait up to 1.25 seconds {1.250} I would still get to renaming the file before PDFCreator was done with it and get an error 75. So I modified the Error Handler code to tack on another 3/4 second wait if the err.number was 75 and to display a message if it wasn’t. it now looks like this.

Code:

On Error GoTo Waitforpdfcreator
Try_Again:

       Do While Dir(zBillPath & "rptAnnualBilling.pdf") = vbNullString
         Sleep 1250           '** wait 1.25 secs before trying again **
       Loop
       
       Name zBillPath & "rptAnnualBilling.pdf" As _
            zBillPath & "Bill" & Format(rst![OwnerID]) & ".pdf"
On Error GoTo 0
    .
    .
    .
   GoTo GetOut

Waitforpdfcreator:
   Select Case Err.Number
         Case 75
             Sleep 0.75  '*** Wait another 3/4 second. ***
             Resume Try_Again
         Case Else
             MsgBox "Module: BillingsCode" & vbCrLf & _
                    "Routine: EmailMailBills" & vbCrLf & _
                    "Error: " & Err.Number & " " & _
                    Err.Description, vbCritical + vbOKOnly, _
                    "Unexpected Error:"
             Resume GetOut
   End Select
   
GetOut:

Thanks again!

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote