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: 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?
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.