give option to replace existing file when saving

[Erin9903]

I have the following code in one of my macros

    strFilename = Range("E2").Value    OutPut = MsgBox("Choose the path where to save your file", vbOKOnly, "Select Folder")
    
      With Application.FileDialog(msoFileDialogFolderPicker)
         .Title = "Select a Folder"
         .AllowMultiSelect = False
         .InitialFileName = ThisWorkbook.Path & "\"
         .Show
         strFolder = .SelectedItems(1)
      End With
      
      'save the file
      ActiveWorkbook.SaveAs Filename:=strFolder & "\" & strFilename

Works great except for if the file name is already there. How can I give the option to save over the existing file, just as Excel normally would if you were trying SaveAs and then choosing a file from the list in a folder?

 

[Col Delane]

I think you need to use:

Application.Dialogs(xlDialogSaveAs).Show( various arguments)

... but I think you'll have to rearrange your existing code.

Here's some details from http://codevba.com/excel/dialogs.htm#SaveAs

This is the Excel file Save As dialog. Opening the files this way allows you to set features in advance such as excel filetype, possible recommendation for opening read only and password protection.
http://www.mrexcel.com/forum/excel-...between-getsaveasfilename-xldialogsaveas.html

<code>[COLOR=blue]Dim[/COLOR] strFilename [COLOR=blue]As[/COLOR] [COLOR=blue]String[/COLOR]: strFilename = "report1"
[COLOR=blue]Dim[/COLOR] strFolder [COLOR=blue]As[/COLOR] [COLOR=blue]String[/COLOR]: strFolder = "C:\temp" [COLOR=green]'initial directory - NOTE: Only works if file has not yet been saved![/COLOR]
[COLOR=blue]Dim[/COLOR] xlfFileFormat [COLOR=blue]As[/COLOR] XlFileFormat: xlfFileFormat = XlFileFormat.xlOpenXMLWorkbook [COLOR=green]'or replace by other XlFileFormat[/COLOR]
[COLOR=blue]Dim[/COLOR] strPassword [COLOR=blue]As[/COLOR] [COLOR=blue]String[/COLOR]: [COLOR=green]'strPassword = "password" 'The password with which to protect the file - if any[/COLOR]
[COLOR=blue]Dim[/COLOR] booBackup [COLOR=blue]As[/COLOR] [COLOR=blue]Boolean[/COLOR]: [COLOR=green]'booBackup = True  '(Whether to create a backup of the file.)[/COLOR]
[COLOR=blue]Dim[/COLOR] strWriteReservationPassword [COLOR=blue]As[/COLOR] [COLOR=blue]String[/COLOR]: [COLOR=green]'strWriteReservationPassword = "password2" ' (The write-reservation password of the file.)[/COLOR]
[COLOR=blue]Dim[/COLOR] booReadOnlyRecommendation [COLOR=blue]As[/COLOR] [COLOR=blue]Boolean[/COLOR]: booReadOnlyRecommendation = [COLOR=blue]False[/COLOR] [COLOR=green]'(Whether to recommend to the user that the file be opened in read-only mode.)[/COLOR]
[COLOR=blue]Dim[/COLOR] booWorkbookSaved [COLOR=blue]As[/COLOR] [COLOR=blue]Boolean[/COLOR] [COLOR=green]' true if file saved, false if dialog canceled[/COLOR]
[COLOR=blue]If[/COLOR] [COLOR=black]Len[/COLOR](strFolder) > 0 [COLOR=blue]Then[/COLOR] [COLOR=black]ChDir[/COLOR] strFolder
booWorkbookSaved = Application.Dialogs(xlDialogSaveAs).Show(Arg1:=strFilename, Arg2:=xlfFileFormat, Arg3:=strPassword, _
                                            Arg4:=booBackup, Arg5:=strWriteReservationPassword, Arg6:=booReadOnlyRecommendation)
</code>

 

[Fluff]

@Erin9903
From what you've shown of your code, it should give you the opportunity to overwrite the existing file.
Have your turned DisplayAlerts off?