Is there a result code for the SaveAs method that I could use to
conditionally direct the execution of this code around the block of code that
starts "If Err.Number..." if the user elects not to save the file?

Here is the code:

If fs.FileExists(strPathName) Then
On Error Resume Next
ActiveWorkbook.SaveAs Filename:=strPathName, _
FileFormat:=xlNormal, _
ReadOnlyRecommended:=False, _
CreateBackup:=False
If Err.Number = 1004 & Left(strFileName, 2) < "ts" Then
Msg = "Save aborted. This timesheet will now close. Open " _
& strFileName & " directly in " & strSaveDirectory & "."
MsgBox Msg, vbOKOnly, "Error"
ActiveWorkbook.Close SaveChanges:=False
End If
On Error GoTo 0
Err.Clear

Thank you.

John Wirt

I don't think SaveAs returns any value. How about doing it like this:

If fs.FileExists(strPathName) Then
If vbYes = MsgBox("Overwrite existing file?", vbYesNo) Then
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:=strPathName, _
FileFormat:=xlNormal, _
ReadOnlyRecommended:=False, _
CreateBackup:=False
Application.DisplayAlerts = False
Else
Msg = "Save aborted. This timesheet will now close. Open " _
& strFileName & " directly in " & strSaveDirectory & "."
MsgBox Msg, vbOKOnly, "Error"
ActiveWorkbook.Close SaveChanges:=False
End If
End If


--
Hope that helps.

Vergel Adriano


"JWirt" wrote:

Is there a result code for the SaveAs method that I could use to
conditionally direct the execution of this code around the block of code that
starts "If Err.Number..." if the user elects not to save the file?

Here is the code:

If fs.FileExists(strPathName) Then
On Error Resume Next
ActiveWorkbook.SaveAs Filename:=strPathName, _
FileFormat:=xlNormal, _
ReadOnlyRecommended:=False, _
CreateBackup:=False
If Err.Number = 1004 & Left(strFileName, 2) < "ts" Then
Msg = "Save aborted. This timesheet will now close. Open " _
& strFileName & " directly in " & strSaveDirectory & "."
MsgBox Msg, vbOKOnly, "Error"
ActiveWorkbook.Close SaveChanges:=False
End If
On Error GoTo 0
Err.Clear

Thank you.

John Wirt

YEP, you've got the problem and it looks like the solution. Thanks. Joh

"Vergel Adriano" wrote:

I don't think SaveAs returns any value. How about doing it like this:

If fs.FileExists(strPathName) Then
If vbYes = MsgBox("Overwrite existing file?", vbYesNo) Then
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:=strPathName, _
FileFormat:=xlNormal, _
ReadOnlyRecommended:=False, _
CreateBackup:=False
Application.DisplayAlerts = False
Else
Msg = "Save aborted. This timesheet will now close. Open " _
& strFileName & " directly in " & strSaveDirectory & "."
MsgBox Msg, vbOKOnly, "Error"
ActiveWorkbook.Close SaveChanges:=False
End If
End If


--
Hope that helps.

Vergel Adriano


"JWirt" wrote:

Is there a result code for the SaveAs method that I could use to
conditionally direct the execution of this code around the block of code that
starts "If Err.Number..." if the user elects not to save the file?

Here is the code:

If fs.FileExists(strPathName) Then
On Error Resume Next
ActiveWorkbook.SaveAs Filename:=strPathName, _
FileFormat:=xlNormal, _
ReadOnlyRecommended:=False, _
CreateBackup:=False
If Err.Number = 1004 & Left(strFileName, 2) < "ts" Then
Msg = "Save aborted. This timesheet will now close. Open " _
& strFileName & " directly in " & strSaveDirectory & "."
MsgBox Msg, vbOKOnly, "Error"
ActiveWorkbook.Close SaveChanges:=False
End If
On Error GoTo 0
Err.Clear

Thank you.

John Wirt

Except, I think the second Application.DisplayAlerts should be True.
John

Ahh... Copy-paste got me again. You're right. The second one should be true..


--
Hope that helps.

Vergel Adriano


"JWirt" wrote:


Except, I think the second Application.DisplayAlerts should be True.
John