Flexcel: sheetcodename read/write

Hi,

Is it possible to change the attribute SheetCodeName to read/write instead of read-only?
The implementation of this SheetCodeName isn't working well.

We want to use Excel files that our clients can change as much as they prefer with pivot tables etc. We want to use a template of our own or a template from our clients and our program should detect two specific sheets and update the values in those sheets without changing anything else in the other sheets.
We wanted to see whether or not we could use the SheetCodeName to identify the sheets so our users can change the SheetName themselves.
We noticed that this doesn't work wel. If we have a Sheet with SheetName 'Sheet1' and SheetCodeName 'Code1', we can look for the SheetCodeName and we will find the right Sheet. When we use Flexcel, the resulting file will have the sheet with the same SheetName and SheetCodeName, but when we use this generated file again, somehow afterwards the SheetCodeName of that Sheet is changed into the same string as the SheetName. So we are limited in the use of the SheetCodeName. If the SheetCodeName was read/write instead of read-only, we could programmatically change the SheetCodeName into the correct value each time we use a file and everything will probably work fine.

Hi,


The thing with CodeName is as follows:
The reason xls/x files have a CodeName is basically macros. VBA macros will refer to the sheet codenames, not names, and so you can rename sheets and macros will still work.

This is why if you record a macro in an empty Excel file, and then rename Sheet1 to MySheet, you will see the sheet is referred as Sheet1(MySheet) in the Macro editor. Even when you renamed the sheet to MySheet, for the macros it still is Sheet1. (because that is the codename).

Now, the reason FlexCel doesn't allow to change CodeNames (and if you look at the code you'll see there is code to do it, just commented out) is because FlexCel doesn't parse macros (and we have no plans to do that either). Macros in an xls/x file are stored as a binary blob with ByteCode for the compiled VBA (not source code) and well, to support macros we would have to develop a VBA interpreter which is outside our scope. But you can imagine what happens if you rename the CodeName in Sheet1 to be MySheet, but don't update the VBA bytecode from Sheet1 to MySheet. Excel will just crash when opening the file if it has references to codenames that don't exist. As we can't update the bytecode, FlexCel doesn't allow you to change the codename. And this is the same reason we don't allow you to delete a sheet if there is a macro: If the macro referenced that sheet and we removed it, the file will crash Excel.

I understand you probably don't even have macros at all and if this is the case it wouldn't matter to change the codename, so what we could try to do is to allow changing the codename only if there aren't macros in the file. But this used to be that way some time ago, and it generated other issues because it can also be uses in buttons or other stuff and we would need to update those too if you changed it. So given that CodeName isn't an incredibly useful thing to start with, we ended up making SheetCodeName read only.

If there is no other solution, we can look at making it read/write again (As long as you don't have macros). But I am not sure on why your codenames change, they should remain fixed once the file is created.

Do you have a workflow I can use to reset a CodeName? You say "When we use this generated file again, somehow the SheetCodeName is changed".  Do you have some more details on that? When you use this generated file again in FlexCel or in Excel? And ideas of something you are doing that can be resetting the CodeName?  IT shouldn't change once the sheet is created, either by Excel or FlexCel. Of course if you duplicate the sheet and remove the original it will change, but this would be expected since the sheet isn't the same anymore.

The other way to change the CodeName from Excel is to press Alt-F11 to open the Macro Editor, then select the sheet in the Up-Left panel, and change the "name" property in the botom-left panel to the codename you want. But this is something that most likely your users aren't doing.



Right now we have the following code:


try
  sheetIndex := xls.GetSheetIndex('Dossiergegevens');
except
  on E : Exception do
    sheetIndex := 0;
end;
if sheetIndex = 0 then
  for j := 1 to xls.SheetCount do
  begin
    xls.ActiveSheet := J;
    if xls.SheetCodeName = 'Dossiergegevens' then
      sheetIndex := J;
  end;
if sheetIndex = 0 then
begin
  sheetIndex := xls.SheetCount + 1;
  xls.AddSheet;
end;
xls.ActiveSheet := sheetIndex;
xls.SheetName := 'Dossiergegevens';



We want to look for the right sheet to update only that data.
If our users change the SheetName, then the CodeName is kept.
If we open an Excel file that has a sheet that has the right SheetName, then we don't have a problem. If we open an Excel file that has a sheet from which they changed the SheetName, we could identify the right sheet using the SheetCodeName.
If we can't find that sheet, we create a new Sheet with that SheetName.
If we do find the right sheet, we change it's SheetName back to our default SheetName.
We do this, because we noticed that if we used the changed SheetName, somehow when the file was saved by Flexcel, it changed the SheetCodeName into the same value as the SheetName.

After identifying the rigt Sheet, we clear all it's data, just to be sure all the unnecessary data isn't kept. Maybe this also clears the SheetCodeName?

So originally we used the same code as above, but we didn't have the last line: xls.SheetName := 'Dossiergegevens';

Our template has a Sheet with SheetName equal to SheetCodeName = 'Dossiergegevens'.
In our original code, our clients used our template to generate their audit file.
This generated file could be altered by them with new Sheets etc. If they used this altered file without changing the SheetName 'Dossiergegevens', everything worked without a problem. And they could keep using that file to update the right sheet.
If they changed the SheetName 'Dossiergegevens' into 'Dossiers' for example, the first time they used that file in our program, our code wouldn't find the Sheet with SheetName 'Dossiergegevens' and would check all the sheets to see if there is a sheet with SheetCodeName 'Dossiergegevens'. That sheet would be found and the right sheet would be updated. However, in our original code, we noticed that if they used their altered Excel file, after the update the SheetCodeName was changed when Flexcel saved that Excel file and would have changed this also in 'Dossiers'. If they used that file for a new update, our code wouldn't find the sheet with SheetName 'Dossiergegevens' and would also not find the sheet with SheetCodeName 'Dossiergegevens' as both the SheetName and SheetCodeName would be 'Dossiers'. And we would add an additional Sheet with SheetName 'Dossiergegevens'.

That's why we added that last line. If they changed the SheetName from 'Dossiergegevens' to 'Dossiers, we can identify the Sheet using it's SheetCodeName. We then change the SheetName back to 'Dossiergegevens' and when Flexcel saves that file, both SheetName and SheetCodeName would have the right value 'Dossiergegevens'.

After identifying the rigt Sheet, we clear all it's data, just to be sure all the unnecessary data isn't kept. Maybe this also clears the SheetCodeName?


I guess this will be the cause. If you are using xls.ClearSheet(); it will also clear the code name. Or are you using a different method to clear the sheet?

If this was the cause (calling ClearSheet()), I've changed it so now it preserves the CodeName. I don't like this can of changes that can affect old users (who trusted ClearSheet to clear the code name), but in this case makes sense: ClearSheet preserves the sheet name, it should also preserve the sheet code name. And I can't really see this breaking anyone's code, so adding an overload ClearSheet(preserveCodeName) looks overkill.

So now ClearSheet preserves the codename, and you won't need to set the sheet name anymore.

Just in case, I also added the ability to set the codename directly, this will work only if you don't have macros. But it shouldn't be needed in your case.


A last note: Note that if an user opens the file in Excel 2003 or earlier, and the file doesn't have macros, Excel will also remove the codenames when saving. Codenames as said are related to macros even if here you are using them for something different. So Excel 2003 doesn't see any reason to read or save codenames if the file doesn't have macros. Excel 2007 or newer will preserve them even without macros. This was just a warning because you can't really trust CodeName if your users are using Excel 2003. (And this is one of the reasons originally we didn't provide the ability to set the codename, at the time we implemented the feature Excel 2003 was very common and the "losing" of codenames was confusing. Today I think most people is in Excel 2007/2010 so it isn't a big problem anymore.)

I hope this helps. We are aiming for a release today, if not possible it will be next week.

Hi Adrian,

Thanks for the support.
I understand that using these codenames would be problematic for macro's, but like you said, we specific don't use any macro's. Our clients probably wouldn't save anything in Excel 2003, because we use specific pivot tables from Excel 2007 and up.

Thanks for changing this method. This makes more sense if you wouldn't change the SheetName with the xls.ClearSheet(); method, that you would also keep the SheetCodeName unchanged.
Having the ability to set the codename is also a welcomed feature. :)

Sincerely,

Tim Smet

Hi Adrian,

Are these changes included in the latest 6.1.1 version?
We are waiting for these changes and for the changes about the problem with the SetAutoFilter to test both changes at once.

Sincerely,
Tim Smet

Yes, both the codename and the autofilter changes are in 6.1.1

Regards,
  Adrian

Hi Adrian,
We tried the latest update and both changes are working great.
Thanks a lot.