Pause Execution until Report Finished Printing

I have a line of code in an Access 2003 .mdb that opens a report that outputs a file (note: the report is set to a printer that prints to a local port set to a file name).  The very next line of code uses the WScript.Shell Object to run a DOS command to rename that file.  It looks like the program sometimes tries to rename the file before the report is finished creating the file.  Is there a way to halt execution of the next line of code until the report is finished outputting the file?
LVL 1
Declan_BasileITAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Nick67Commented:
You DoEvents in a loop until the file is closed, or a determined-by-you period of time expires (so the machine doesn't hang if something goes wrong)

Test if the file is closed with this
https://support.microsoft.com/en-us/kb/291295

Function IsFileOpen(filename As String)
    Dim filenum As Integer, errnum As Integer

    On Error Resume Next   ' Turn error checking off.
    filenum = FreeFile()   ' Get a free file number.
    ' Attempt to open the file and lock it.
    Open filename For Input Lock Read As #filenum
    Close filenum          ' Close the file.
    errnum = Err           ' Save the error number that occurred.
    On Error GoTo 0        ' Turn error checking back on.

    ' Check to see which error occurred.
    Select Case errnum

        ' No error occurred.
        ' File is NOT already open by another user.
        Case 0
         IsFileOpen = False

        ' Error number for "Permission Denied."
        ' File is already opened by another user.
        Case 70
            IsFileOpen = True

        ' Another error occurred.
        Case Else
            Error errnum
    End Select

End Function

Open in new window

Anders Ebro (Microsoft MVP)Microsoft DeveloperCommented:
I think the easiest way to determine when the file is done being used is to try to rename it, and if it fails, then the file is still in use. There might be smarter ways of doing it, but I think that is how I would do it.

Code example provided below:
Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
Public Sub RenameFile()
   Dim sOriginalFileName As String
   Dim sNewFileName As String
   sOriginalFileName = "C:\aaef.docx"
   sNewFileName = "C:\NewName.docx"
   Dim Iterations As Long
   On Error Resume Next
   Do
      Iterations = Iterations + 1
      Err.Clear
      Sleep 500 'Sleep for 0.5 seconds
      Name sOriginalFileName As sNewFileName
      
   Loop While Err.Number <> 0 And Iterations <= 20 'As long as en error is generated the file is busy. Max attempts 20
   If Err.Number <> 0 Then
      MsgBox "Timeout: The file could not be renamed"
      Else
      MsgBox "File renamed succesfully"
   End If
   
End Sub

Open in new window

Nick67Commented:
The very next line of code uses the WScript.Shell Object to run a DOS command to rename that file.

You are doing an awful lot of stuff with DOS that you could be doing in VBA.
Is there a reason for that?
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

Declan_BasileITAuthor Commented:
I'm using LPR to copy the output file to an IP.  I don't believe I can do that in VBA, so I've been making calls to DOS commands via WScript.Shell.

Will it work to simply open the report in the dialogue window mode?  Will the program then wait until the report is finished outputting the file before is continues to the next line of code?
Nick67Commented:
You're going to create a loop
And I do like Sleep.
Put this in a module by itself
Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

'VBA command to create the LPR file
Dim SucessOrDeath as Boolean
Dim x as integer
SucessOrDeath =  False


Do Until SucessOrDeath =  true
     SucessOrDeath = Not IsFileOpen(TheNameAndPathofTheFileYouAreWaitingFor)
    x = x + 1
    if x = 60 then
        Msgbox "The File was not created/renamed successfully"
       exit do
   end if
Loop

Open in new window


and the checking function

Function IsFileOpen(filename As String)
    Dim filenum As Integer, errnum As Integer

    

    On Error Resume Next   ' Turn error checking off.
    filenum = FreeFile()   ' Get a free file number.
    ' Attempt to open the file and lock it.
    Open filename For Input Lock Read As #filenum
    Close filenum          ' Close the file.
    errnum = Err           ' Save the error number that occurred.
    On Error GoTo 0        ' Turn error checking back on.

    ' Check to see which error occurred.
    Select Case errnum

        ' No error occurred.
        ' File is NOT already open by another user.
        Case 0
         IsFileOpen = False

        ' Error number for "Permission Denied."
        ' File is already opened by another user.
        Case 70
            IsFileOpen = True
            Sleep 500

        ' Another error occurred.
        Case Else
            Error errnum
            Sleep 500
    End Select



End Function

Open in new window


This will throw the code into a loop until it either has succeeded, or failed 60 times (with  a half-second pause between each failure)

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Declan_BasileITAuthor Commented:
I just needed to use the sleep function to give it a little more time.  Telling me about the sleep function and how to reference it is what I needed.  Thank you.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.