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.