Saveas issue Overwrite existing file ( Excel V

Question

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.

1. Why do I get this error? Why can't I select 'No'
2. What other option do I have to display that the file is already there and select No or Canceland not get the run-time error.?

Answer 1

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