76

In this section of code, Excel ALWAYS prompts: "File already exists, do you want to overwrite?"

Application.DisplayAlerts = False
Set xls = CreateObject("Excel.Application")
Set wb = xls.Workbooks.Add
fullFilePath = importFolderPath & "\" & "A.xlsx"

wb.SaveAs fullFilePath, AccessMode:=xlExclusive, ConflictResolution:=True   

wb.Close(True)

Why does db.SaveAs always prompt me to overwrite existing file if I have DisplayAlerts = False?

Adrian Mole
  • 49,934
  • 160
  • 51
  • 83
bob.mazzo
  • 5,183
  • 23
  • 80
  • 149

4 Answers4

99

To hide the prompt set xls.DisplayAlerts = False

ConflictResolution is not a true or false property, it should be xlLocalSessionChanges

Note that this has nothing to do with displaying the Overwrite prompt though!

Set xls = CreateObject("Excel.Application")    
xls.DisplayAlerts = False
Set wb = xls.Workbooks.Add
fullFilePath = importFolderPath & "\" & "A.xlsx"

wb.SaveAs fullFilePath, AccessMode:=xlExclusive,ConflictResolution:=Excel.XlSaveConflictResolution.xlLocalSessionChanges    
wb.Close (True)
TourEiffel
  • 4,034
  • 2
  • 16
  • 45
Sorceri
  • 7,870
  • 1
  • 29
  • 38
  • for some reason I'm still getting prompted to overwrite the file: Application.DisplayAlerts = False wb.SaveAs fullfilePath, ConflictResolution:=Excel.xlLocalSessionChanges – bob.mazzo Feb 04 '13 at 21:37
  • 4
    you need to tell the workbook you are working on to not display the alerts. I have updated the code above....Just look at the Application.DisplayAlerts should be wb.Application.DisplayAlerts – Sorceri Feb 04 '13 at 22:09
  • thanks @Sorceri. I'll go with that and considered it answered. Thanks for your time ! -Bob – bob.mazzo Feb 05 '13 at 20:33
  • Does `wb.Application.DisplayAlerts = False` need to go below `Set wb = xls.Workbooks.Add`? –  Jun 07 '13 at 12:36
  • 4
    @JackDouglas - As written, you're correct - the `DisplayAlerts=False` line should be after setting the Workbook object. However, you can set it globally by using just `Application.DisplayAlerts`. And you should also probably change it back to True after you're done blocking Alerts. – Kevin Pope Jun 25 '13 at 17:08
  • +1 for use of wb.Application.DisplayAlerts. I didn't know that existed. – Chuck The Nerd Mar 26 '14 at 19:21
  • 4
    @Sorceri your answer has many errors, please consider revising! **First** of all, ConflictResolution has nothing to do with the SaveAs overwrite prompt. (See [here](https://msdn.microsoft.com/en-us/library/office/ff198339(v=office.15).aspx) for details - it is about conflicts between multiple editors in a shared excel file.) **Second**, the `Application.DisplayAlerts` is a member of the `Application` object, **not** the `Workbook` object. So after creating the new instance of Excel application, use this command: `xls.DisplayAlerts = False`. (This is the real reason why OP's code didn't work.) – vacip Dec 03 '15 at 11:34
  • 1
    In your code, `wb.application.displayalerts` gives the same result as `xls.displayalerts`. See [here](https://msdn.microsoft.com/en-us/library/office/ff835918(v=office.15).aspx) for details about `Applicaion` VS `Workbook.Application`. So although `wb.application` is technically correct, it is misleading in my opinion (as seen from some commenters suggesting the use of Application.Displayalerts insted...) But whichever you use, please move the code to the correct position, that is **after** creating the object itself. Now your code produces an error. – vacip Dec 03 '15 at 11:49
  • @vacip Good catch on the display alerts being before setting the object. I removed the WB reference. I do suggest you look at the saveas method of the workbook object. https://msdn.microsoft.com/en-us/library/microsoft.office.tools.excel.workbook.saveas.aspx – Sorceri Dec 03 '15 at 15:25
  • @Sorceri thank you for willing to revisit this age-old thread! MSDN says about the SaveAs method's ConflictReslution: "Returns or sets the way conflicts are to be resolved whenever a **shared workbook is updated**." It is about workbooks with multiple editors. It has no effect on overwriting existing files. – vacip Dec 05 '15 at 09:11
  • 3
    In this specific question, OP creates a new instance of Excel (which is useless and is a bad idea, but anyway, he does). Because of this, you need to set the DisplayAlerts property for the new Excel instance like this: `xls.DisplayAlerts = False` right **after** the `Set xls = CreateObject("Excel.Application")` line, not before. Thank you for your patience and openness! It is important, as your answer is on the first page of Google for many searches. :) – vacip Dec 05 '15 at 09:15
27

I recommend that before executing SaveAs, delete the file if it exists.

If Dir("f:ull\path\with\filename.xls") <> "" Then
    Kill "f:ull\path\with\filename.xls"
End If

It's easier than setting DisplayAlerts off and on, plus if DisplayAlerts remains off due to code crash, it can cause problems if you work with Excel in the same session.

Uttam
  • 596
  • 1
  • 6
  • 11
  • Mind you that if the file is open in another process. it will throw an exception – Mustafa Oct 03 '18 at 03:26
  • 1
    Use some kind of an error handling to make sure DisplayAlerts is turned back on in case of an unexpected termination, like `On Error Goto Cleanup` or whatever way is available for error handling in the given context. – Bence Szalai May 24 '19 at 18:14
  • C# version: `if (File.Exists(@"C:\test.txt")) { File.Delete(@"C:\test.txt"); }` – Dominic Isaia Jan 06 '21 at 22:31
11

To split the difference of opinion

I prefer:

   xls.DisplayAlerts = False    
   wb.SaveAs fullFilePath, AccessMode:=xlExclusive, ConflictResolution:=xlLocalSessionChanges
   xls.DisplayAlerts = True
Marcucciboy2
  • 3,156
  • 3
  • 20
  • 38
Harry S
  • 481
  • 6
  • 5
  • 1
    `ConflictResolution` is not intended to be a true/false property https://learn.microsoft.com/en-us/office/vba/api/excel.xlsaveconflictresolution – Marcucciboy2 Sep 06 '19 at 19:11
  • 2
    xls created with CreateObject. It's a different instance of Excel... So the Application.DisplayAlerts is set in the **original** instance meanwhile the wb is saved in the second instance. This answer shows setting the DisplayAlerts in the second instance which should work. – Mike May 20 '21 at 15:30
-1

Finally got it right, everything above is so confusing.

    Sub SaveAndClose()
    Dim wb1 As String
    
    Application.Calculation = xlCalculationAutomatic
    
    'this only works if the following equation is in C43 in sheet "data"
    '=LEFT(MID(CELL("filename",C41),SEARCH("[",CELL("filename",C41))+1, SEARCH("]",CELL("filename",C41))-SEARCH("[",CELL("filename",C41))-1),75)
    'the vba equation has double quotes everywhere that is how you use a formula in vba.
'vba code recreates this incase it gets deleted by accident. 
    
    ThisWorkbook.Sheets("Data").Range("C43").ClearContents
    ThisWorkbook.Sheets("Data").Range("C43").Formula2R1C1 = _
            "=LEFT(MID(CELL(""filename"",R[-2]C),SEARCH(""["",CELL(""filename"",R[-2]C))+1, SEARCH(""]"",CELL(""filename"",R[-2]C))-SEARCH(""["",CELL(""filename"",R[-2]C))-1),75)"
        'https://techcommunity.microsoft.com/t5/excel/cell-reference-containing-file-name-changes-when-opening-second/m-p/2417030
    
    wb1 = ThisWorkbook.Sheets("Data").Range("C43").Text
    If ThisWorkbook.Name = wb1 Then
    'MsgBox (wb1)
    Workbooks(wb1).Close SaveChanges:=True
    End If
    
    End Sub

this will allow the spreadsheet to determine its own name and then only then can the sub run something against that name. this is so when you have multiple sheets running duplicate sheets but with different names you don't accidently close the wrong sheet. this is a huge win for CYA in my book.

This will also bypass the overwrite message too, you can have the code automatically run in the background on another workbook while you are working in a different workbook without being affected.

DeerSpotter
  • 417
  • 1
  • 6
  • 17