Excel prompts me to save FlexCel generated file

Muegel_Michael · May 7, 2011, 10:21am

I have created a file using TFileFormats.Xls format. When I open this file, do nothing, and close Excel 2010 prompts me to save. If I save and then re-open, no more prompts. I don't think I am doing anything too exciting. It will definately be confusing to end-users if they are prompted.

File created is http://www.muegel.org/xfer/report.xls.

adrian · May 7, 2011, 3:22pm

Hi,

This problem is normally caused by Excel recalculating the formulas, and I won't lie, it is a complex one. Let me explain:

The easiest way to see what is happening is with a simple example: Create a file in Excel, and in A1 write "=Today()". Save and close. Now every time you open and close the file, Excel will ask for saving.

Why is this happening? Simply because "=Today" is a "volatile" formula that is recalculated every time you open the file, and even when you don't change anything yourself, Excel detects that the formula result changed and asks for saving. Actually whenever Excel recalculates a formula (even if the result is the same) it will mark the file as modified.

This doesn't happen normally when you are using the same Excel in the same machine, but if you start sharing xls files around, it is very common to see Excel ask for saving changes, even when there are no changes. I remember for example when ms released Excel 2003 sp1, it changed how some formulas would be calculated, and so opening almost any file created in Excel 2003 with no sp would trigger this message.

In FlexCel case, by default FlexCel marks all formulas to "recalc on open". This is so if there was any formula not supported or that Excel calculates differently, Excel will calculate the correct formula values when you open the file. But of course, this has the side effect of making Excel ask for saving when closing, since when it recalculates, the file is automatically "changed".

So the first step in getting rid of this message is to tell FlexCel not to make formulas recalc on open. (of course understanding that now Excel won't recalculate them). To do it, call:

xls.RecalcForced = false;

before saving the file. (and please remember the drawbacks of doing so, press F1 on "recalcforced" to see it in more detail).

Now, this is just the top of the iceberg. Xls files are also different depending on the Excel version, and an xls file created by Excel 2010 is very different from an xls file created by Excel 2003. FlexCel by default will create a file as if it was created by Excel 2003. And guess what? Excel will detect the version of xls too, and offer to save, if the file is older and has formulas.

As an example, you can create an empty file in office 2007, write "=1+1" at A1, save and open in 2010. When you close, you will see a message asking for saving and telling you that the formulas were created by an older Excel version.

So, to make sure Excel 2010 doesn't ask, you also need to create a "2010" xls, not a 2003 xls that would be what Xls.NewFile() creates. There are 2 ways to do it:

1) in 5.5 we added a new overload of NewFile() that allows you to specify which version of xls you are creating. For example the code:

        XlsFile x1 = new XlsFile(true);
        x1.NewFile(1, TExcelFileFormat.v2010);
        x1.SetCellValue(1, 1, new TFormula("=1+1"));
        x1.RecalcForced = false;
        x1.Save(@"r:\r.xls");

Will not trigger a Save dialog when opened in 2010.

If you change v2010 by v2007 in the code above, it will ask when opening in 2010, but not in 2007. The only good news here is that saving as 2010 won't trigger a save dialog in any Excel, and it will work until the next Excel version arrives. Excel 2015 will again ask for saving all those "old" files when you open them.

If you need FlexCel 5.5 to use the new NewFile overload, please email me and I can send you a beta version, final version should be coming soon, but the beta version is stable and usable in production.

2)Second way if you don't want to update to 5.5 is not to use "newfile", but just open an empty file created with Excel 2010. When FlexCel saves the file, it will save it as 2010 xls.

Well, this is in short the story. You can try with setting RecalcForced= false and creating a 2010 xls, but I am not really sure it is worth, especially if you consider that it won't work for the next Excel version anyway. And this is not a FlexCel problem, when Excel.next comes, this message will show in all files created by Excel itself too.

I also don't think it is too confusing to end users, they are used to Excel asking form time to time even when they didn't change anything, and they don't even realize, they just save or dismiss the dialog, I know I do.

Regards,

Adrian.

Muegel_Michael · May 8, 2011, 12:13am

Thanks for the information Adrian. I am opening an Excel 2003 format template via FlexCel, editing, then saving using Xls.Save(filename, FlexCel.Core.TFileFormats.Xls) (Excel 2003). I use Excel 2010 to edit/view. When I added RecalcForced = false, the save prompt went away when closing the file :-).

So I did not see this behavior:

Excel will detect the version of xls too, and offer to save, if the file is older and has formulas.

As
an example, you can create an empty file in office 2007, write "=1+1"
at A1, save and open in 2010. When you close, you will see a message
asking for saving and telling you that the formulas were created by an
older Excel version.

I am using Framework 2.0 unfortunately and could not test TFileFormats.Xlsx. But I am not doing anything fancy so Excel 2003 is fine.

Regards,
-Mike

adrian · May 8, 2011, 5:50am

Glad you could fix it. About why you don't see the prompt, my guess is that you have edited the template with 2010. Even if it was created with Excel 2003, when you open and save a 2003xls with Excel 2003, it will be converted to Excel 2010xls.

Anyway, what I wanted to say is that the problem might not be 100% fixed, you might see the issue again when Excel 2013 comes out. But also, this is not a FlexCel issue, but an Excel issue and for the record it also happens with xlsx.

You can see an example here:

www.tmssoftware.net/public/flexcel/r07.xls

I have created this file with Excel 2007, no FlexCel involved. At least here, it prompts for saving in 2010 but of course not in 2007. This problem happens always that you are dealing with files created by different Excel versions. And FlexCel can emulate any Excel version, but not all of them at the same time. That is, if you save as 2003xls you are not saving as 2010xls and vice versa.

Regards,

Adrian.