Simple and Bulletproof Save/SaveAs Procedure
 

I've been trying to emulate the Save button (or CTRL-S) in as simple and
bulletproof a manner as possible. This is the best I could do.

Using ActiveWorkbook.SaveAs required using Application.DisplayAlerts = False
because the SaveAs method produces its own "Do you want to replace the
existing file? Yes, No, Cancel" message box if a file with the same name
already exists. Without DisplayAlerts = False, the user would get TWO
"Replace?" message boxes, and pressing "No" or "Cancel" on the message box
produced by ActiveWorkbook.SaveAs results in a run-time error.

In most cases "SendKeys "^s", True" is an elegant solution, but I was
wondering if anyone could improve upon this code.

Dim oSaveName As Variant
Dim oReturnValue As Long

If MsgBox("Save?", vbYesNo) = vbYes Then
If ActiveWorkbook.Path = "" Then
Do
oSaveName = Application.GetSaveAsFilename(, "Microsoft Office
Excel Workbook (*.xls), *.xls")
If TypeName(oSaveName) = "Boolean" Then Exit Do
If Dir(oSaveName) < "" Then
oReturnValue = MsgBox("Replace?", vbYesNo)
End If
Loop Until oReturnValue = vbYes
If TypeName(oSaveName) < "Boolean" Then
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs (oSaveName)
Application.DisplayAlerts = True
End If
Else
ActiveWorkbook.Save
End If
End If

Simple and Bulletproof Save/SaveAs Procedure
 

If you're just interested in a simple and bulletproof save (not saveas)
function, you can just add the add-in autosave. Tools--Add-ins.

If you're on a network you may need to get your it department to install the
add-ins on your excel installation.

"Lazzaroni" wrote:

I've been trying to emulate the Save button (or CTRL-S) in as simple and
bulletproof a manner as possible. This is the best I could do.

Using ActiveWorkbook.SaveAs required using Application.DisplayAlerts = False
because the SaveAs method produces its own "Do you want to replace the
existing file? Yes, No, Cancel" message box if a file with the same name
already exists. Without DisplayAlerts = False, the user would get TWO
"Replace?" message boxes, and pressing "No" or "Cancel" on the message box
produced by ActiveWorkbook.SaveAs results in a run-time error.

In most cases "SendKeys "^s", True" is an elegant solution, but I was
wondering if anyone could improve upon this code.

Dim oSaveName As Variant
Dim oReturnValue As Long

If MsgBox("Save?", vbYesNo) = vbYes Then
If ActiveWorkbook.Path = "" Then
Do
oSaveName = Application.GetSaveAsFilename(, "Microsoft Office
Excel Workbook (*.xls), *.xls")
If TypeName(oSaveName) = "Boolean" Then Exit Do
If Dir(oSaveName) < "" Then
oReturnValue = MsgBox("Replace?", vbYesNo)
End If
Loop Until oReturnValue = vbYes
If TypeName(oSaveName) < "Boolean" Then
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs (oSaveName)
Application.DisplayAlerts = True
End If
Else
ActiveWorkbook.Save
End If
End If