Saveas issue Overwrite existing file ( Excel VBA)

excel, vba

Solution

Try this method.

I have commented the code so you shouldn't have any problem understanding it. Still if you do then simply ask :)

Sub Sample()
    Dim fName As Variant

    '~~> Offer user to Save the file at a particular location
    fName = Application.GetSaveAsFilename

    '~~> Check if it is a valid entry
    If fName <> False Then
        '~~> Check before hand if the file exists
        If Not Dir(fName) <> "" Then
            '~~> If not then save it
            ActiveWorkbook.SaveAs Filename:=fName
        Else
            '~~> Trap the error and ignore it
            On Error Resume Next
            If Err.Number = 1004 Then
                On Error GoTo 0
            Else '<~~ If user presses Save
                ActiveWorkbook.SaveAs Filename:=fName, _
                FileFormat:=xlWorkbook, _
                ConflictResolution:=xlLocalSessionChanges
            End If
        End If
    End If
End Sub

Problem

I have the following macro that is correct except for the `SaveAs` gives me an error if I click `No` or `Cancel`,if I click `yes` is working fine. ``` ActiveWorkbook.SaveAs Filename:=FileName, FileFormat:=xlWorkbook, ConflictResolution:=xlLocalSessionChanges Application.DisplayAlert =True ``` But when I come to `SaveAs` part I get the following error when I select `No` to the save. Excel message: A file named " ......... " already exists in this location. Do you want to replace it? I click 'No' or `cancel` and get the run time error 1004 .... Method `SaveAs` of object `_Workbook` failed. 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. - Why do I get this error? Why can't I select 'No' - What other option do I have to display that the file is already there and select `No` or `Cancel`and not get the run-time error.?

Original source