How can I avoid Excel show Save dialog when automation app exits

Question

I have a excel automation application written in Delphi, that accesses a given excel workbook and extracts applying some internal logic different data of the sheets.

It has worked perfectly for years on Excel 2003 and 2007.

Recently some users have Excel 2010. On those users, when my application exits, Excel shows a dialog box asking the user to Save the unmodified worksheet. The message says something about versions of the formulas to be saved in a new 2010 format. Sorry I don't have the exact words because it is not an english office package.

How can my app prevent excel 2010 showing this annoying and useless box?

Most of the users have Excel 2007, so most of the files that my application needs to process, regardless of the actual Excel version of the user running my app, are in that format, and will be in the future, so migrating all the files is not an option.

Do you close the workbook anywhere in your app? If not, close the workbook without saving changes, then Exit the App. — Scott Holtzman, Oct 23 '12 at 18:45
To make sure that this dialog does not appear: `App.WorkBooks.Open(ExpandUNCFileName(filename),1);`. Open the file as read only. — LU RD, Oct 23 '12 at 19:20
I can't reproduce it right now, the Excel 2010 users are in another space. I will try LU suggestion that looks promising. Be back to you with the requested info and with the results of the test. — PA., Oct 24 '12 at 14:29
I'm back. @Scott the dialog text was: "¿Desea guardar los cambios realizados en XX.xls? Microsoft Excel actualiza las fórmulas al abrir archivos guardados con una versión anterior de Excel." — PA., Oct 24 '12 at 19:47

score 12 · Accepted Answer · answered Oct 23 '12 at 19:42

This is a routine for printing an Excel sheet through automation. After having some trouble with excel saving dialogs popping up now and then, this routine solved all troubles:

procedure PrintExcelReport( Const filename : String;
                                  sheet    : Integer);
{- Sheet is counted from 1 and upwards !! }
Var 
  App : OleVariant;
begin
  App:= CreateOleObject('Excel.Application');
  Try
    App.Visible:= 0;
    App.WorkBooks.Open(ExpandUNCFileName(filename),1);  // Open read only
    Try
      App.ActiveWorkBook.WorkSheets[sheet].Select;
      App.ActiveWorkBook.ActiveSheet.PrintOut; // Prints active sheet
    Finally
      App.ActiveWorkBook.Saved:= 1;
      App.DisplayAlerts:= 0;
      App.ActiveWorkBook.Close(SaveChanges:= 0);
    End;
  Finally
    App.Quit;
    App:= UnAssigned;
  End;
end;

Adapt it to fit your application.

I implemented with slight changes and it works. The magic isthe combination of open in read only, and setting Saved before closing. Thanks. — PA., Oct 24 '12 at 19:50
in case you wonder why I did not implement it fully... My logic does not open an ActiveWorkbook, when I tried `App.Workbooks.Close(SaveChanges:= 0)` I got a OLE runtime error, so I removed it. — PA., Oct 24 '12 at 19:51

How can I avoid Excel show Save dialog when automation app exits

1 Answers1