VBA loop getting hung up on file save

[Photomofo]

Good Evening,

I have a program that periodically gets stuck saving a file to a network location. I've written the following snip of code to try to prevent this from happening. The basic idea is to use the Application.OnTime feature to check and see if the program gets stuck saving a file. Unfortunately this isn't working.

Is there a better way to back out of a file save if a given time period has elapsed?

'Save on Q Drive

Path = "Q:\XYZ"

If Path & "Alice Data - " & Desk & ".xlsx" <> "" Then

Sheets("Control Panel").Range("A2") = "SAVING"

Kickout_Time = Now + TimeValue("00:01:00")

Application.OnTime Kickout_Time, "Kickout_1”

ActiveWorkbook.SaveAs Filename:=Path & "Alice Data - " & Desk & ".xlsx", FileFormat:=51, CreateBackup:=False

Sheets("Control Panel").Range("A2") = ""

End If

 

[Joe4]

Without diving too deeply into the code, I see a few issues right off the bat.

You are missing the backslash at the end of your file path, i.e.

Path = "Q:\XYZ\"

Without it, the "XYZ" will become part of your file name (and not a folder).

Also, note that this line will ALWAYS be true:

If Path & "Alice Data - " & Desk & ".xlsx" <> "" Then

Since you are hardcoding "Alice Data - " and ".xlsx", that string can NEVER be empty.
So that line of code is really worthless and serving no purpose here, as it is impossible for that statement to ever be false, as currently written.

Also, it is HIGHLY recommend that you do not use reserved words (names of existing functions, objects, properties, methods, etc) like "Path" as the name of your variables, procedures, or functions. Doing so can cause errors and other unexpected results. One way that can help identify if a word is a reserved word is try to type the word into a VBA module by itself in lower-case letters like this:

Sub Test()
    path
End Sub

If VBA then automatically capitalizes the first letter like this:

Sub Test()
    Path
End Sub

then it is a reserved word (or a global variable you have set somewhere else), and you should NOT use it as a variable.
Otherwise, when you reference "Path" in your code, it may be confused about whether you are reference the built in "Path" property or your "Path" variable, and you may not get the expected results.

When in doubt, I often preface my variables with the word "My" to ensure that this situation does not occur, i.e. "MyPath".