Pause Execution until Report Finished Printing

Declan_Basile
Declan_Basile used Ask the Experts™
on
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?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2014

Commented:
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

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

Most Valuable Expert 2014

Commented:
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?
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

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?
Most Valuable Expert 2014
Commented:
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)

Author

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.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial