How Can I Make Changes to and Then Re-Save an Existing Excel Spreadsheet?

How Can I Make Changes to and Then Re-Save an Existing Excel Spreadsheet?

Hey, Scripting Guy! How can I open an existing Excel spreadsheet, add some additional information to that spreadsheet, and then save my changes? Every time I call the SaveAs method a dialog box pops up asking me if I want to save my changes.

-- RW

Hey, RW. Generally speaking, you can avoid that dialog box by using the Save method rather than the SaveAs method. If you open an existing spreadsheet and make some changes to it, just call the Save method; that should save your document without displaying the confirmation dialog box. For example, here’s a sample script that opens the file C:\Scripts\Test.xls, writes the current date and time into cell A1, saves the file, and then quits:

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
Set objWorkbook = objExcel.Workbooks.Open("C:\Scripts\Test.xls")
Set objWorksheet = objWorkbook.Worksheets(1)

objWorksheet.Cells(1, 1).Value = Now
objWorkbook.Save()
objExcel.Quit

In most cases, that should do the trick. However, it’s also true that the SaveAs method offers more options than the Save method. If you want to add password protection to the spreadsheet, create a backup version of the file, or save in another format, you’ll need to use SaveAs. Likewise, suppose that every morning you run a script that grabs some data and populates cells in a brand-new spreadsheet. Every morning you want to save that file as C:\Scripts\Daily_report.xls. Because this is a new spreadsheet (remember, you didn’t open the existing Daily_report.xls), you need to use the SaveAs method. And, when you do, a dialog box will pop up telling you that a file by that name already exists and asking you if you want to replace it.

So how do you get around these dialog boxes? The secret is to set the DisplayAlerts property (part of the Excel Application object) to FALSE. The DisplayAlerts property suppresses the display of dialog boxes and alert messages; instead of allowing you to choose whether to do X, Y, or Z, Excel automatically selects the default action for you. The default action for overwriting existing files is Yes. So when DisplayAlerts is set to False, the revised worksheet will be saved and you won’t be nagged and bothered by dialog boxes. Here’s a sample script that sets DisplayAlerts to FALSE and then uses the SaveAs method to save the changes:

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
objExcel.DisplayAlerts = FALSE

Set objWorkbook = objExcel.Workbooks.Open("C:\Scripts\Test.xls")
Set objWorksheet = objWorkbook.Worksheets(1)

objWorksheet.Cells(1, 1).Value = Now
objWorkbook.SaveAs("C:\Scripts\Test.xls")
objExcel.Quit

And just for the heck of it, here’s a script that creates a new worksheet and then overwrites an existing worksheet, again by setting DisplayAlerts to FALSE and by using SaveAs:

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
objExcel.DisplayAlerts = FALSE

Set objWorkbook = objExcel.Workbooks.Add
Set objWorksheet = objWorkbook.Worksheets(1)

objWorksheet.Cells(1, 1).Value = Now
objWorkbook.SaveAs("C:\Scripts\Test.xls")
objExcel.Quit

If you’re having problems saving files, setting DisplayAlerts to FALSE will more than likely take care of things for you.