How to use workbook.saveas with automatic Overwrite
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
?
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)
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.