Method 'SaveAs of Object'_Workbook' failed error handling routine help needed.

[blbat]

The following code takes the results of previous working code (not shown here) that updates an existing Excel file.

With ActiveWorkbook.Sheets("mysheetname")
    .Copy
    ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & Application.PathSeparator & "mysheetname", FileFormat:=51
End With

When this code executes it properly calls the Excel Message:
"this file already exists in this location. Do you want to replace it?"
with the Yes | No | Cancel buttons.

If the user selects "Yes" there is no error.
If the user selects "No" or "Cancel" the Run-time error '1004' is thrown, with message "Method 'SaveAs' of Object '_Workbook' failed"

I need some help with an error handler, but don't know the proper way, or location to put it.
I placed an handling routine inside the "With" statement as such:

With ActiveWorkbook.Sheets("mysheetname")
    On Error GoTo ErrHandle
        .Copy
        ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & Application.PathSeparator & "mysheetname", FileFormat:=51
    ErrHandle:
        If Err.Number = 1004 Then
              MsgBox "File exists, you must select 'Yes" to overwrite"
              Resume
          End If

End With

and it works...but is not optimal.I need to capture the user response "Cancel" to exit sub. otherwise they are in an endless loop.

any ideas would be greatly appreciated.

 

[rlv01]

Is it your intent to offer the user a choice about overwriting an existing file? Because if not then you can just use this:

    With ActiveWorkbook.Sheets("mysheetname")
        .Copy
        Application.DisplayAlerts = False
        ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & Application.PathSeparator & "mysheetname", FileFormat:=51
        Application.DisplayAlerts = True
        ActiveWorkbook.Close False
    End With

If you DO want to offer a choice, then this way is expedient, if not too elegant.

    With ActiveWorkbook.Sheets("mysheetname")
        .Copy
        On Error Resume Next
        ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & Application.PathSeparator & "mysheetname", FileFormat:=51
        On Error GoTo 0
        ActiveWorkbook.Close False
    End With

Personally, I like to add code to do the testing so I can better control what I do with user response

    Dim FilePath As String, WritePerm As Boolean
    With ActiveWorkbook.Sheets("mysheetname")
        FilePath = ThisWorkbook.Path & Application.PathSeparator & .Name & ".xlsx"
        
        With CreateObject("Scripting.FileSystemObject")
            WritePerm = False
            If .FileExists(FilePath) Then
                Select Case MsgBox("File already exists. Overwrite?", vbYesNoCancel Or vbQuestion, Application.Name)
                    Case vbYes
                        WritePerm = True
                    Case vbCancel
                        Exit Sub
                End Select
            Else
                WritePerm = True
            End If
        End With
        
        If WritePerm Then
            .Copy
            Application.DisplayAlerts = False
            ActiveWorkbook.SaveAs Filename:=FilePath, FileFormat:=51
            Application.DisplayAlerts = True
            ActiveWorkbook.Close False
        End If
    End With

 

[blbat]

I appreciate the help rlv01.
I liked what you did with that last bit...so used it.
The Only thing I did different is directly address the Case vbNo
although testing it with that Case vbNo commented out showed no difference in the execution of the code. Must be some Excel native cabability to handle "No"?

marking it as SOLVED.

 Dim FilePath As String, WritePerm As Boolean

    With ActiveWorkbook.Sheets("mysheetname")
        FilePath = ThisWorkbook.Path & Application.PathSeparator & .Name & ".xlsx"
        
        With CreateObject("Scripting.FileSystemObject")
            WritePerm = False
            If .FileExists(FilePath) Then
                Select Case MsgBox("File already exists. Overwrite?", vbYesNoCancel Or vbQuestion, Application.Name)
                    Case vbYes
                        WritePerm = True
                    Case vbNo
                        Exit Sub
                    Case vbCancel
                        Exit Sub
                End Select
            Else
                WritePerm = True
            End If
        End With
        
        If WritePerm Then
            .Copy
            Application.DisplayAlerts = False
            ActiveWorkbook.SaveAs Filename:=FilePath, FileFormat:=51
            Application.DisplayAlerts = True
            ActiveWorkbook.Close False
        End If
    End With

 

[rlv01]

The Only thing I did different is directly address the Case vbNo
although testing it with that Case vbNo commented out showed no difference in the execution of the code. Must be some Excel native cabability to handle "No"?

Nothing wrong with putting in a Case vbNo . I left it out because it was not needed due to the way the code is structured. But it does not hurt anything.