Close Excel without prompting to save

Problem

Have you ever been prompted to save changes to your Microsoft Excel file when you did make any changes? From a Microsoft support article, one of the common reasons for this is the re-calculation of “volatile functions”. For example. the formula “=TODAY()” will get re-calculated when the file is opened, resulting in the prompt to re-save the file (on close if you have not already saved it).

This is not a big deal when working with the final manually. You simply click [Yes] or [No] and go on your way.

However, if you have an automated script that reads data from that Excel file, this prompt can be problematic. You end up with all these prompts you have to go and manually close afterwards. That defeats the purpose of the automation.

How do you get Excel to not prompt the saving of the file (assuming that your are automating using Ruby’s win32ole library)?

Solution

If you create a “test.xlsx” spreadsheet that contains the formula “=TODAY()”, the following script (if in the same folder) will open the file and close it. However, on close, the prompt to save the file will be displayed.

require 'win32ole'

filename = 'test.xlsx'
spreadsheet_path = File.join(File.absolute_path(__FILE__), '..', filename)

begin
  excel = WIN32OLE::new('excel.Application')
  workbook = excel.Workbooks.Open(spreadsheet_path)
	
  # Get data from the workbook
ensure
  excel.Quit
end

From a StackOverflow question, I learned that you can make the workbook think it has already been saved:

workbook.saved = true

Adding this line before quitting will prevent the dialog from occurring – effectively closing the file without saving changes:

require 'win32ole'

filename = 'test.xlsx'
spreadsheet_path = File.join(File.absolute_path(__FILE__), '..', filename)

begin
  excel = WIN32OLE::new('excel.Application')
  workbook = excel.Workbooks.Open(spreadsheet_path)
	
  # Get data from the workbook
ensure
  workbook.Saved = true
  excel.Quit
end