If user says "No" to FileSaveAs Overwrite?

[OceansideDJ]

My macro asks the user for a FileSaveAsName, then tries to save the file with:

ActiveWorkbook.SaveAs Filename:=sFileSaveName, ...

How can I tell if the user gets the "File exists, Overwrite?" prompt, and
answers "no"?

Thanks

 

[Harald Staff]

Hi

You let the user do user things and check if the file path and filename is
identical to sFileSaveName in the end.

But I suspect this is not your final goal. You want to always overwrite, or
not allow overwrite, or not to ask the user ?

HTH. Best wishes Harald

 

[OceansideDJ]

Harald,
If the user picks "No" to the overwrite, I want to ask for another filename.

Currently, if the the user picks "no", I get runtime error 1004, Method
"SaveAs of object '_Workbook' failed.

Could I detect error 1004?

 

[Jake Marx]

Hi OceansideDJ,

If the user picks "No" to the overwrite, I want to ask for another
filename.

Currently, if the the user picks "no", I get runtime error 1004,
Method "SaveAs of object '_Workbook' failed.

Could I detect error 1004?

Yes, you can use On Error Goto or On Error Resume to trap runtime error
1004. Here's one way:

Sub Demo()
Dim vFileName As Variant
Dim bSaved As Boolean

vFileName = True
Do Until (vFileName = False) Or bSaved
vFileName = Application.GetSaveAsFilename
On Error Resume Next
If vFileName <> False Then
ActiveWorkbook.SaveAs vFileName
bSaved = (Err.Number = 0)
End If
On Error GoTo 0
Loop

If bSaved Then MsgBox "Saved"
End Sub

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

 

[OceansideDJ]

Jake,
Excellent. Thanks for a more elegant solution than mine.

 

[Dave Peterson]

Another way is to check for yourself:

Option Explicit
Sub testme01()
Dim myFileName As Variant
Dim OkToSave As Boolean
Dim resp As Long

Do
myFileName = Application.GetSaveAsFilename _
(filefilter:="Excel files, *.xls")
If myFileName = False Then
Exit Sub
End If

OkToSave = True
If Dir(myFileName) = "" Then
'do nothing special
Else
resp = MsgBox(prompt:="Overwrite Existing file?", _
Buttons:=vbYesNoCancel)
Select Case resp
Case Is = vbCancel
MsgBox "Try again later"
Exit Sub
Case Is = vbNo
OkToSave = False
End Select
End If

If OkToSave Then
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:=myFileName, _
FileFormat:=xlWorkbookNormal
Application.DisplayAlerts = True
Exit Do
End If
Loop

End Sub