How to avoid "A file named ... already exists in this location. Do you want to replace it?" prompt on subsequent save?

Question

I save all worksheets in a workbook as individual CSV files.

If I make a change to any of the worksheets and run the macro again, it prompts me with the "A file named ... already exists in this location. Do you want to replace it?".

If I click Yes, the prompt comes up for every worksheet. If I click no, the macro throws an error.

Is there a way to avoid the prompt?

Sub CSVAutomation()
Dim ws As Worksheet, wb As Workbook
Dim pathh As Variant

Set wb = ActiveWorkbook

With Application.FileDialog(msoFileDialogFolderPicker)
    .AllowMultiSelect = False
    If .Show = -1 Then  'a folder was picked
       pathh = .SelectedItems(1)
    End If
End With

If pathh = False Then Exit Sub   'no folder picked; pathh is false

Application.ScreenUpdating = False
For Each ws In wb.Sheets(Array("01 - Currencies", ...."14 - User Defined
 Fields"))
    ws.Copy
    With ActiveWorkbook
        'Application.DisplayAlerts = False   'to avoid overwrite warnings
        '  pathh is a string (variant) of the path of the folder; does not 
 need pathh.Path
        .SaveAs pathh & "\" & ws.Name, xlCSV
        .Close SaveChanges:=False
    End With
Next ws

Application.ScreenUpdating = True

End Sub

You could get around the first problem by turning off alerts. `Application.DisplayAlerts = False` You might solve the second problem by using `AccessMode` shown in this [post](http://stackoverflow.com/a/14634781/2521004) — Automate This, Feb 02 '16 at 16:36
The first time you save the CSV files, that is the sheets of the Workbook, you close that Workbook??? Because sometimes Excel do not release the files and is like it was open. — Elbert Villarreal, Feb 02 '16 at 16:37

score 1 · Accepted Answer · answered Feb 02 '16 at 16:44

Check my comment and (as Portland Runner says) you could turn off some alerts I used this

    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Application.AutomationSecurity = msoAutomationSecurityForceDisable
    Application.AskToUpdateLinks = False

Using a procedure to put inside and used every time to turn it of and another to turned on helpme a lot with all the alerts.

Sub Alerts(ScreenUpdate As Boolean, DisplayAlerts As Boolean, AutoSecurity As Boolean, AskToUpdate As Boolean)
        Application.ScreenUpdating = ScreenUpdate
        Application.DisplayAlerts = DisplayAlerts
        Application.AutomationSecurity = IIf(AutoSecurity, msoAutomationSecurityForceDisable, msoAutomationSecurityByUI)
        Application.AskToUpdateLinks = AskToUpdate
End Sub

How to avoid "A file named ... already exists in this location. Do you want to replace it?" prompt on subsequent save?

1 Answers1