If user says "No" to FileSaveAs Overwrite?
 

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

If user says "No" to FileSaveAs Overwrite?
 

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" skrev i melding
...
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

If user says "No" to FileSaveAs Overwrite?
 

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?

If user says "No" to FileSaveAs Overwrite?
 

Hi OceansideDJ,

OceansideDJ wrote:
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]

If user says "No" to FileSaveAs Overwrite?
 

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

If user says "No" to FileSaveAs Overwrite?
 

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


OceansideDJ wrote:

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?

--

Dave Peterson