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?

Microsoft AccessVBA

Last Comment

Declan Basile

8/22/2022 - Mon

Nick67

1/18/2016

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

SOLUTION

Anders Ebro (Microsoft MVP)

1/18/2016

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

GET A PERSONALIZED SOLUTION

Ask your own question & get feedback from real experts

Find out why thousands trust the EE community with their toughest problems.

Nick67

1/18/2016

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?

Declan Basile

1/18/2016

ASKER

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?

Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!

Walt Forbes

ASKER CERTIFIED SOLUTION

Nick67

1/18/2016

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

⚡ FREE TRIAL OFFER

Try out a week of full access for free.

Find out why thousands trust the EE community with their toughest problems.

Declan Basile

1/19/2016

ASKER

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.