CODEWITHSUNDEEP
excelvba
user1266515
user1266515

Reputation: 796

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

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?".

Replace Prompt for each and every worksheet

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

Macro error if on clicking 'No'

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

Upvotes: 1

Views: 6230

Answers (1)

Elbert Villarreal
Elbert Villarreal

Reputation: 1716

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

Upvotes: 1

Related Questions