Menu
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?
866
Run the above macro twice from a macro-enabled workbook. The second time that you run it, you will see a confirmation dialogue box asking if you want to overwrite the existing file or not. Now, the file will overwrite the existing file without making a mention of it.
Save As -> Replace File If you are accustomed to selecting "File -> Save As" when saving documents, you can also overwrite the file with your changes this way. Select "Replace File. This is the same behavior as File Save." The original file will be overwritten.
In Microsoft Excel, you can create a Microsoft Visual Basic for Applications (VBA) macro that suppresses the Save Changes prompt when you close a workbook. This can be done either by specifying the state of the workbook Saved property, or by suppressing all alerts for the workbook.
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.
45
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
