VBA Save As problem with file that already exist

[DVAL]

Hi!

I have the following macro:

Sub save()
FN = Sheet1.Range("H30").Text
ActiveWorkbook.SaveAs ThisWorkbook.Path & "\" & FN
ActiveWorkbook.close
End Sub


that works correct except if I already have the file with the same name.
In that case I got Excel message:
A file named " ......... " already exists in this location.
If I click 'No' or 'Cancel' the SaveAs gives me an error:
Run-time error '1004'

I don't want to use the Application.DisplayAlerts = False,
because I want the user to be aware that there is a file already named the
same.

I want on case 'No' to bring up the save as dialog box
and on Case Cancel to exit from macro

Thank you for reading this especially if you are able to help me out on
this.

 

[Dave Peterson]

I think I'd just look for the error and then decide what to do:

Option Explicit
Sub SaveItNow()
Dim FN As String
FN = Sheet1.Range("H30").Text

On Error Resume Next
ActiveWorkbook.SaveAs ThisWorkbook.Path & "\" & FN
If Err.Number <> 0 Then
MsgBox "Not saved!"
Err.Clear
Else
MsgBox "saved" 'comment out after testing
ActiveWorkbook.Close
End If
On Error GoTo 0
End Sub

You have a mixture of ThisWorkbook and Activeworkbook. Are you sure you want that?

And I wasn't sure if you wanted to close the activeworkbook if the save was
canceled.

 

[DVAL]

Hi
this is almost what I need. I made little changes.

Sub save()
Dim FN As String
FN = Sheet1.Range("H30").Text
On Error Resume Next
ActiveWorkbook.SaveAs ActiveWorkbook.Path & "\" & FN
If Err.Number <> 0 Then
Application.Dialogs(xlDialogSaveAs).Show
Err.Clear
Else
ActiveWorkbook.Close
End If
On Error GoTo 0
End Sub

I want this DialogSaveAs to show up if I pick 'No', and it works fine,
but if i pick Cancel I want to exit from macro, and not to close workbook.
Something like--> go back and try again

 

[Dave Peterson]

First, I wouldn't use Save as the procedure name. I'd do my best not to use
reserved words as my procedure/variable names.

It may not confuse excel, but it could confuse me.

And if you show the SaveAs dialog, the user can still overwrite that file, right?

Option Explicit
Sub SaveMeNow()

Dim FN As String
Dim WasSaved As Boolean
Dim ret As Boolean

FN = Sheet1.Range("H30").Text
WasSaved = False

On Error Resume Next
ActiveWorkbook.SaveAs ActiveWorkbook.Path & "\" & FN
If Err.Number = 0 Then
WasSaved = True
Else
Err.Clear
ret = Application.Dialogs(xlDialogSaveAs).Show
If ret = True Then
'saved with some name
WasSaved = True
End If
End If
On Error GoTo 0

If WasSaved = True Then
ActiveWorkbook.Close savechanges:=False
Else
MsgBox "not saved!"
End If

End Sub