Wait if Windows is still using the file

Question

I have a macro code that splits XPS files and it works fine if you wait for Windows or SAP to be done with writing the file.
I need some method to see if the file can be opened or if it's still used by Windows/SAP.

You make a print from SAP and print as XPS document.
Switch to my Excel document to split the file in parts, in the split process I need to rename the file to a zip-file and unpack it and that is where the error occurs.

    a = 1
    On Error GoTo tryagain:
tryagain:
    a = a + 1
    If a = 10 Then Exit Sub
    DoEvents
    ' rename from example 1.oxps -> 1.zip
    Name pth & Fname As pth & Left(Fname, InStr(Fname, ".") - 1) & ".zip"

    On Error GoTo 0

In theory the above snippet should loop 10 times (or 9 or whatever it is.. not important at the moment) and try to rename the file and if it fails each time exit the sub.
In reality I get an error on the Name line with error 75 when a = 3.
That means it has looped once past the Name line and failed, gone back to tryagain: and then failed but errored out.

Does the coed run the On Error GoTo 0 at the first fail? That is the only reason I can see to this behaviour.

What are my options to this? How can I make sure my code waits until the file can be renamed and the keeps going?

score 1 · Accepted Answer · answered Nov 08 '18 at 08:49

I found a solution.

I create an error handler that waits and then resumes back to the code.

'the main code only has goto err if there is an error and a goto point called tryagain.
    On Error GoTo err 
tryagain:
    Name pth & Fname As pth & Left(Fname, InStr(Fname, ".") - 1) & ".zip"



' error handler:
err:
    Application.Wait (Now + TimeValue("0:00:01")) ' add a one second wait
    DoEvents
    Resume tryagain ' go back and try again.

I have tested it on my code and it seems to work fine.
My code waits about five-six seconds before it starts running.

Wait if Windows is still using the file

1 Answers1