Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Is there a simple procedure to hide rows if a condition is met | Excel Discussion (Misc queries) | |||
Trim function as a procedure - a simple example | Excel Programming | |||
Excel crashes on simple procedure | Excel Programming | |||
Is there a simple procedure to set my macros in Excel 2003 to be . | New Users to Excel | |||
Help 'splain unusual behavior in simple procedure | Excel Programming |