3

I have an Excel file with a named range (just one cell). There are other cells who's formulas use this name. I am trying to programatically rename the cell using EPPlus. My first attempt I simply removed the old name and added the new:

workbook.Names.Remove("OldName");
workbook.Names.Add("NewName", mySheet.Cells["B2"]);

This worked to rename the range, but the formulas still use "OldName" so they now come up with #NAME? instead of the corresponding cell's value.

If I get a reference to the name using LINQ like var nameReference = workbook.Names.First(x => x.Name == "OldName") I notice by trying to set the name property like nameReference.Name == "NewName" that it is a read-only property.

Is there a method I haven't seen for renaming these named ranges? If not, what alternatives are there to my situation? If I could get a reference to all cells who's formulas call this named range I could change each formula. I think I would need to simply scan every used cells' formulas for this reference, which I think would take a while, especially considering I actually may have dozens of Excel workbooks to scan through.

kilkfoe
  • 445
  • 5
  • 11
  • 1
    Looking at the [corresponding source code](http://epplus.codeplex.com/SourceControl/latest#EPPlus/ExcelProtectedRangeCollection.cs) this seems to be not easily possible. – Georg Jung Apr 19 '16 at 23:40
  • Looking at the Remove() function, it does access the node it's deleting by using xpath `"...[@name='" + item.Name` - perhaps a Rename() function could be created that simply changes the value. But yeah, that would still only allow you to rename the named range, not actually update any formulas that depend on the name – kilkfoe Apr 20 '16 at 06:34

0 Answers0