Re: Bug with named ranges when inserting a new sheet.

I have an Excel file that has named ranges on sheets 1, 2 & 3. These named ranges are used to populate dropdowns in sheet 0. I am reading in this file, and I want to programmatically insert a new sheet between sheets 0 and 1. I am able to do this via the C1XLBook.Sheets.Insert method. The problem is that this screws up my named ranges. After inserting the new sheet, the named ranges all reference the wrong sheet, as if they were based on the index of the sheet instead of the name. For example, I have a range on the 2nd sheet that is defined as 'My Sheet Name'!$A$2:$A$28. After inserting a new sheet at index 1 and saving the file, the range is now defined as 'Newly Inserted Sheet'!$A$2:$A$28, which of course is wrong. It's as if C1Excel is internally defining the named ranges by the index of the sheet instead of the name of the sheet. Everything is fine if I just insert the new sheets after all the existing sheets, but that isn't really what I want. And as far as I can tell, there's no method for reordering or changing the indices of the existing sheets.

I have an Excel file that has named ranges on sheets 1, 2 & 3. These named ranges are used to populate dropdowns in sheet 0. I am reading in this file, and I want to programmatically insert a new sheet between sheets 0 and 1. I am able to do this via the C1XLBook.Sheets.Insert method. The problem is that this screws up my named ranges. After inserting the new sheet, the named ranges all reference the wrong sheet, as if they were based on the index of the sheet instead of the name. For example, I have a range on the 2nd sheet that is defined as 'My Sheet Name'!$A$2:$A$28. After inserting a new sheet at index 1 and saving the file, the range is now defined as 'Newly Inserted Sheet'!$A$2:$A$28, which of course is wrong. It's as if C1Excel is internally defining the named ranges by the index of the sheet instead of the name of the sheet. Everything is fine if I just insert the new sheets after all the existing sheets, but that isn't really what I want. And as far as I can tell, there's no method for reordering or changing the indices of the existing sheets.

---

http://helpcentral.componentone.com/cs/forums/p/77046/210540.aspx#210540

I don't understand why the sheet index has anything to do with the definition of the named range. The range is defined by the name of the sheet, not the index of the sheet (e.g. 'Sheet1'!$A$2:$A:28, not 1!$A$2:$A:28).