Supress “save changes” when creating a document via ClosedXML

Feb 15, 2012 at 6:35 PM

Everything works fine when I make an instance of Excel file and applies later workbook.SaveAs(file).

One of the issues being reported is that when they preview, make no changes, and try to close Excel, it prompts with the standard "do you want to save your changes". Their opinion is that as they haven't made any changes, it shouldn't show this message.

Is there any way of suppressing this message?.  by the way I've noticed that this situation appears only when the original file contains formulas.

This is the original file:

Contacts
FName LName Outcast DOB Income
John Galt ######### 21-Ene-19 $ 2,000
Hank Rearden FALSO 04-Mar-07 $ 40,000
Dagny Taggart FALSO 15-Dic-21 $ 10,000
      Average: $ 17,333

If I suppress the formula in Income column, saves the file and make an instance of it, and later open this instance with Excel and close it, the prompt "do you want to save your changes" doesn´t shows.

Maybe, It's something related with Excel but anyway I'd appreciated any kind help.

Thanks in advance.

Xabier.

 

 

Coordinator
Feb 15, 2012 at 7:17 PM

What's happening is that ClosedXML puts the formulas on the sheets but it doesn't resolve them. Instead it tells Excel to do it. In effect Excel is really modifying the file when you open it and so it asks the user if they want to save the changes.

You could turn off calculate formulas automatically but then your users won't see the formula results unless they manually refresh the workbook.

There's no way around this as far as I know.

Feb 15, 2012 at 8:53 PM

Hi Manuel,

Thank you very much for your answer and at the same time I'll send you greetings for your excellent ClosedXML which with OpenXML help to me to avoid the interop hell when this runs in a server. Now, I don´t have Excel processes in the server that consume memory and freeze my .net applications. Go ahead!

Xabier.

 

Feb 15, 2012 at 10:52 PM

A work around to avoid the prompt "do you want to save your changes" would be the next:

a. Save your file with extension xlsm because a macro will be add.

b. Open xlsm file, press Alt + F11 to go to Microsoft Visual Basic.

c. Double click ThisWorkbook and add this code in the event BeforeClose.

' Exit Excel saving changes
Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim thisWkBook As Workbook
   
    Set thisWkBook = ThisWorkbook
    thisWkBook.Close savechanges:=True
End Sub

d. Change True to False in order to exit from Excel without saving any change.

e. Save your xlsm file.

f. Rembember to put automatic calculation of formulas in your application with ClosedXML.

// Instantiate file xlsm
var workbook = new XLWorkbook("C:\Test.xlsm");
 
// Positioned in first sheet
var ws = workbook.Worksheet(1);
.
.
.

 

// And the workbook calculation mode:
workbook.CalculateMode = XLCalculateMode.Auto;
 
// Save file
workbook.SaveAs("C:\Test.xlsm");
g. Finally, if you open Test.xlsm with Excel and click in Close, the file will be saved without asking to you commit the changes.
I hope this could be useful to somebody else.
Xabier.