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.
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?
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. :)
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.
Yes, both the codename and the autofilter changes are in 6.1.1
We tried the latest update and both changes are working great.
Thanks a lot.