How to use workbook.saveas with automatic Overwrite

Question

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?

score 91 · Accepted Answer · edited Sep 06 '19 at 21:12

91

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)

edited Sep 06 '19 at 21:12

Dorian

2,515
2
7
29

answered Jan 31 '13 at 20:42

Sorceri

7,482
1
23
37

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
3

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
3

@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

score 24 · Answer 2 · edited Dec 11 '18 at 15:25

24

I recommend that before executing SaveAs, delete the file 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.

edited Dec 11 '18 at 15:25

Pragmateek

12,648
9
66
105

answered Jul 05 '18 at 10:45

Uttam

485
1
5
9

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. – sbnc.eu 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

score 7 · Answer 3 · edited Sep 06 '19 at 19:12

7

To split the difference of opinion

I prefer:

   xls.DisplayAlerts = False    
   wb.SaveAs fullFilePath, AccessMode:=xlExclusive, ConflictResolution:=xlLocalSessionChanges
   xls.DisplayAlerts = True

edited Sep 06 '19 at 19:12

Marcucciboy2

3,053
3
15
33

answered Mar 16 '19 at 07:51

Harry S

371
4
4

`ConflictResolution` is not intended to be a true/false property https://docs.microsoft.com/en-us/office/vba/api/excel.xlsaveconflictresolution – Marcucciboy2 Sep 06 '19 at 19:11
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

How to use workbook.saveas with automatic Overwrite

3 Answers3

Linked

Related