I am QA'ing the 7.1.0.5 version of .Cells with our application. We are having a problem with one particular named range in on a sheet that comes from an XLS file imported into a workbook using the Combine function.
The file Cash_Flow_Float.xls contains a single sheet called Float that defines a number of named ranges. The failure I'm looking at is in Checks_Cleared_in_Period which has Workbook scope in this file.
QA__Cash_Flow_V10.xlsx is the output from a previous version of .Cells. You can see that in this workbook there is a Float worksheet, and on it is the named range Checks_Cleared_in_Period that points to the correct range at Workbook scope.
QA__Cash_Flow_V11.xlsx was generated with the latest .Cells. If you look at the Float sheet, you'll see that the named range does not exist. However, if you look at the first worksheet, Graphs, you'll find the named range there, though it does point to the correct cells on the Float sheet. This named range appears to have had its scope changed from Workbook to the Graphs sheet. There is another named range, Float, that has also been changed to Graph scope, but not that there are also named ranges that haven't been changed, such as Float_Start_Date.
If it matters, the Graphs sheet happens to come from our template file, and is the only sheet in that file. All other sheets are either generated by our application or come from other XLS files using Combine.
You can see a use of the broken named range on 'CF All Segments':B86. (You have to expand the group to see this cell.) It expects to find the named range on the Float sheet (or at workbook scope.)
This is a show-stopper bug for us in terms of deploying the latest .Cells.
Unfortunately, I don't have sample code. I only have an extremely large and complicated application. Given that this worked several versions ago, is there any way for you to make progress on this without me spending significant time to write an entirely new application?
I have also attached our template file. The one unusual thing this does is have in it undefined named ranges, which are defined by the application as it creates new sheets. This mechanism seems to work fine -- the references in the template file link up to the new sheets correctly -- but could be involved in the failure.
It would be helpful, if you could drill down the problem and provide us a simpler code replicating the problem. You could also provide us some screenshots which you can create using Ms-Paint and highlight the problem with red-circles.
This will help us sort out your problem quickly.
Anyway, we have logged your issue in our database. Development team will look into it and once we have some fix or update available, we will help you asap.
Thanks for looking into this. I know a simple example will make it easier to find and fix and I will spend some time today trying to produce one. Meanwhile, I have attached some screenshots so you can see what happens (you can also look for yourself in the files I provided.)
In the first attachment you can see that the named range Checks_Cleared_in_Period is at Workbook scope. This is the file that is Combine'd into the larger workbook.
In the second, created with an older version of .Cells, you can see that Combine created the named range at the Float worksheet scope. While I would prefer that it maintain Workbook scope, as most of the other named ranges do, at least this works in a predictable way.
In the third, created with the latest version of .Cells, you can see that Combine created the named range at the Graphs worksheet scope. This is just wrong. I don't understand why it picked the Graphs sheet, other than it being the first sheet in the output workbook.
I have been able to reproduce the problem with a simple program. I've attached a ZIP file with the program, the two supporting template XLS files, and two outputs. The _old output was made with .Cells 5.3.0.6. Using the name manager, you can see that the named ranges that come from the combine.xls Float sheet appear in the output file on the Float sheet (though I would have preferred they remained at workbook scope, that's less important.) In the _new output, made with .Cells 7.1.0.5, these ranges are on the Graphs sheet, which is clearly wrong.
If you look at the "Generated Sheet" sheet, you can see that the formula in cell B2 works correctly in the _old file but exhibits the #NAME? failure in the new file. This reproduces the bug.
I hope this helps in getting a quick fix for this problem.