SaveAs error handling

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Here is the problem...I tell the workbook where to save and with what name
using code, part of the file name is a date entered by the user. If the user
enters the same date then I want to give them the option of overwriting the
file or canceling the save. I have tried the following code and can't get it
to work properly. It works great if I put in Application.DisplayAlerts =
False and just allow it to overwrite the existing file, but if there is
already a lot of info in that file that would not be good. I would like it to
be a critical message and they can either choose yes or no, if they choose
yes then it would resume the save, if no then I will put some code in so that
certain sheets are shown. Any help is greatly appreciated.

On Error GoTo SaveOrNot

ActiveWorkbook.SaveAs Filename:= _
myFileName, FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False

MsgBox "File Saved to " & myFileName
End If
Application.ScreenUpdating = True

SaveOrNot:
Dim Res As Long
Res = MsgBox("A File With This Name Already Exists In This Location! Do You
Want To Replace It?", vbYesNo)
If Res = 6 Then
Resume
ElseIf Res = 7 Then
End If
End Sub
 
how 'bout this:
If Len(Dir(MyFileName)) > 0 Then
'File already exists Insert msgbox code here
Else
'File does not exist. Insert save code here
End If

HTH

Charles Chickering
 
Back
Top