PDA

View Full Version : Sleeper: Excel bombs out when I try and Save the workbook



talie
05-31-2005, 09:28 PM
I've created a button in my Workbook that when users select it they are prompted for a filename. If they use a unique filename the code works fine but if they try and use an existing filename Excel unexpectedly quits without saving and without asking if they want to overwrite the selected file. Interestingly, ActiveWorkbook.Save works if I comment out the rest of the code in the function.

The code I'm using is below:


Sub saveToolkit()
fName = Application.GetSaveAsFilename
If fName <> False Then
If fName <> ActiveWorkbook.Name Then
ActiveWorkbook.SaveAs Filename:=fName
Else
ActiveWorkbook.Save
End If
End If
End Sub

I hope someone can help?...

HaHoBe
05-31-2005, 10:12 PM
Hi, talie,

the following code works fine with Excel97


Option Explicit

Sub saveToolkit()
Dim fName As Variant
fName = Application.GetSaveAsFilename( _
fileFilter:="Workbooks (*.xls), *.xls")
If fName <> False Then
If fName <> ActiveWorkbook.FullName Then
If MsgBox("Save as existing file?", vbOKCancel, "Overwrite file?") = vbOK Then
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs FileName:=fName
Application.DisplayAlerts = True
Else
Exit Sub
End If
Else
ActiveWorkbook.Save
End If
End If
End Sub

Ciao,
Holger

Bob Phillips
06-01-2005, 01:07 AM
I've created a button in my Workbook that when users select it they are prompted for a filename. If they use a unique filename the code works fine but if they try and use an existing filename Excel unexpectedly quits without saving and without asking if they want to overwrite the selected file. Interestingly, ActiveWorkbook.Save works if I comment out the rest of the code in the function.

Have you virus checked your system?

talie
06-01-2005, 06:50 PM
Thanks Holger, your code partially helped - however, I'm still getting the error. Perhaps if I tried explaining the problem a different way:

Saving the ActiveWorkbook with a new filename (one that doesn't already exist in the selected folder) works with no errors.

Saving the ActiveWorkbook under its own filename (i.e. no change in filename) also works with no errors.

However, Saving the ActiveWorkbook with a Different and Pre-Existing filename (i.e. the file already exists in the selected folder) Does Not Work. This is where Excel unexpectedly Quits without saving and asks if I'd like to send an Error Report to Microsoft.

Is there code to help with this?
Is there are way to activate the SaveAs sub-menu item on the File menu from a macro?

:banghead:

HaHoBe
06-02-2005, 05:56 AM
Hi, talie,

I tested the code again but couldn?t get it not to do what the code is supposed to do :dunno . The only other idea I have at present is to check whether the chosen file is closed and thus available.

Ciao,
Holger

MWE
06-02-2005, 08:11 AM
Running Excel2000 under WinXP, I could not get the original code to fail (and Excel quits). It does fail with a 1004 run time error under several conditions, e.g., selecting No or Cancel to file overwrite. I poked around several of the "save" operations and could not find an easy way to trap errors. So, I added some standard error handling that, at least, keeps Excel running and lets the user know what has happened


Sub saveToolkit()
Dim FName As Variant
FName = Application.GetSaveAsFilename
If FName <> False Then
If FName <> ActiveWorkbook.Name Then
On Error GoTo Error_SaveAs
ActiveWorkbook.SaveAs Filename:=FName
Else
ActiveWorkbook.Save
End If
End If
Exit Sub
Error_SaveAs:
MsgBox "error executing SaveAs with filename = " & FName & vbCrLf & _
"no save done", vbCritical
End Sub

FYI: I have encountered cases where Excel fails during VBA execution for reasons I could never sort out. I eventually rebuilt the spreadsheet and things were OK