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

PA. picture PA. · Oct 23, 2012 · Viewed 8.2k times · Source

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.

Answer

LU RD picture LU RD · Oct 23, 2012

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.