Return users response to built in Excel "Overwrite Existing File" Yes/No/Cancel

[Micscopau]

Hi All,

The exact opposite of this question has been asked many times, and I have used the solution... but the time has come where I need to know if the user has selected No or Cancel to overwrite an existing File.
I do not want to hide this alert.

when using the code:

Application.ActiveWorkbook.SaveAs (SaveName)

If the file already exists and a user selects "yes" then excel does it's thing and saves the file.

If the user selects "no" or "cancel" then an error occurs. Which can be trapped.

I am unsure how to delineate between the user selecting no or cancel. If they select no, then my code will go into:

SaveName = Application.GetSaveAsFilename()

and if they click cancel, I plan to exit sub...

Although clicking no/cancel returns an excel error, near as I can tell, it is a generic "Method 'Save As' of Object '_Workbook' failed" and not a specific error code response for the seperate no/cancel buttons.

One idea I had was to preemptively display the overwrite dialog box and store the users response as a variable, but I'm not sure if this is even possible.

Thanks,
Mike

Windows 7
Excel 2007-2010

 

[shg]

There is no "No" option with GetSaveAsFileName.

Do you mean this?

    sFile = Application.GetSaveAsFilename
    If sFile = "False" Then Exit Sub

 

[mikerickson]

You would have to test if the file already exists and then ask your own "Overwrite? Yes,No,Cancel" message box.

 

[vaskov17]

Sub SaveAs()
    Dim res As Integer
    Dim SaveName As String
    
    SaveName = "c:\Book1.xlsx"
    
    'check if file exists
    If Dir(SaveName, vbNormal) <> "" Then
        'get user input
        res = MsgBox("Overwrite file?", vbYesNoCancel)
        
        If res = vbYes Then
            'save file
        ElseIf res = vbNo Then
            'get iser input file name
        Else
            'exit sub if cancel
            Exit Sub
        End If
    Else
        'do stuff if file doesn't exist
    End If
End Sub

 

[Micscopau]

to shg, the "yes/no/cancel" is the user response options from the Application.SaveAs

Thanks Mike and Vaskov17, I was starting to think this was the only work around.

Is there no way to force VBA to display the already built in excel prompt windows?

Since we can conveniently display the ".GetSaveAsFilename" pop up window, I wasn't sure if there was code to also display other such pre-made messages, like the AlertBeforeOverwriting prompt, or the Excel Help window, or the Developer/VBA/Module window...

 

[vaskov17]

You could also use:

Application.FileDialog(msoFileDialogSaveAs).Show

since it differentiates between the buttons the user clicks