Hi. After upgrading from version 5 to version 8, my users have noticed a change with the behavior of named ranges. For example, I have an xlsm with a named range that refers to $A$2 in a sheet that gets added programatically server-side. Once the data is generated server-side and the xlsm is opened, the named range gets changed to include all rows in Column A i.e. $A$2:$A$10. I’ve tried several versions ranging from 5.x to 7.x and none of those versions exhibited this behavior. Is this possibly a bug? If not, is there a workaround for this behavior?
Hi, here is a working example project showing how the named range “EffectiveDate” gets changed from a specific cell reference to a range following processing by Aspose Cells. This behavior was not present in versions prior to 8.x.
Hi. Here is the same project built against version 7 Aspose Cells. You will see that after processing, the named range “EffectiveDate” is defined as Sheet1!$A$2. If this change in behavior was intentional, was it considered a bug prior to the change or was just an intentional change in behavior? This change is causing issues for some of our customers who have coded macros around the prior behavior.
Thanks for looking into this. I should point out that it is critical for us to have the ability to set a named range to a specific cell and not have it overridden when data is added by Aspose.
Hi. This is getting us closer but what we’ve found is (noadd) only helps us if we apply it to all cells. I don’t see a way to have one named range stay referencing only $A$2 while another named range expands to reference $B2:$B (as data grows).
The problem is we do have a requirement to have named ranges on the same sheet where some stay static, usually only referencing one cell and others that grow with the data. If there is no way to achieve this behavior it is going to be a pretty major hardship for us. We have lots of macros that require this ability. If the columns that used noadd would stay static and the columns that do not would grow, that would work fine for us. This doesn’t appear to be the case though. We would appreciate any help you can offer us that would help us avoid rewriting a lot of our macros in order to upgrade to version 8 and beyond.
prudential rawal:The problem is we do have a requirement to have named ranges on the same sheet where some stay static, usually only referencing one cell and others that grow with the data.
Thanks for providing us sample project.
I have evaluated your project with older version v7.x and you are right, it is giving different behavior as you pointed out. Well, as a workaround, you may still accomplish your task with latest version of the product i.e., Aspose.Cells for .NET v8.5.x. First you need to update your markers to:
&=Data1.StartDate(noadd, copystyle) &=Data1.EndDate(noadd, copystyle)
then, you need to insert blank rows/range accordingly, so your second range should be extended accordingly for your requirements, you need to add a line of code to your code segment, it would work as per your needs:
designer.Workbook = new Workbook(@"…\demo.xlsm");
int index = 2;
//Insert n blank rows to accomplish your task/ requirements
designer.Workbook.Worksheets.Cells.InsertRows(index, dt.Rows.Count - index);
Hope, this helps a bit.