Results 1 to 3 of 3

Thread: Waiting in VBA?

  1. #1
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,194
    Thanks
    201
    Thanked 785 Times in 719 Posts
    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

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  2. Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts
    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

  4. #3
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,194
    Thanks
    201
    Thanked 785 Times in 719 Posts
    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

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •