How to use workbook.saveas with automatic Overwrite

excel vba excel-2010
bob.mazzo picture bob.mazzo · Jan 31, 2013 · Viewed 264.2k times · Source

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?

Answer

Sorceri picture Sorceri · Jan 31, 2013

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)

Related questions

Excel VBA Run-time error '13' Type mismatch

I created a macro for a file and first it was working fine, but today I've been opening and restarting the file and macro hundreds of times and I'm always getting the following error: Excel VBA Run-time error '13' …

Read More
Copy an entire worksheet to a new worksheet in Excel 2010

I have found similar questions that deal with copying an entire worksheet in one workbook and pasting it to another workbook, but I am interested in simply copying an entire worksheet and pasting it to a new worksheet -- in …

Read More
Copying and pasting data using VBA code

I have a button on a spreadsheet that, when pressed, should allow the user to open a file, then copy columns A-G of the spreadsheet "Data", then paste the data from those columns on the current sheet. I have a …

Read More