Wait if Windows is still using the file

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:
    a = a + 1
    If a = 10 Then Exit Sub
    ' 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?

1 answer

  • answered 2018-11-08 08:49 Andreas

    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 
        Name pth & Fname As pth & Left(Fname, InStr(Fname, ".") - 1) & ".zip"
    ' error handler:
        Application.Wait (Now + TimeValue("0:00:01")) ' add a one second wait
        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.