2007 sp2, range names disappear

cecil
StarLounger
Posts: 98
Joined: 09 Sep 2010, 16:01

2007 sp2, range names disappear

Post by cecil »

I have found a bit of discussion regarding this issue using Google, but I have not found a solution. I assume there is one by now. Can someone point me to it.

We have workbooks converted from prior versions to xlsm files. Workbooks have links to other workbooks. The lowest one in the food chain ocassionally looses a range name. The range name it looses is "volumes", so I don't think this is a reserved word issue. The range name is used in two scopes, sheet level and workbook level. It always looses the book level name. No VBA code is run to open/close workbooks.

Thanks in advance for your help.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: 2007 sp2, range names disappear

Post by HansV »

I use named ranges quite a bit in Excel 2007 SP2 and I've never lost one yet. But I don't use the same name as a local and global name...

There was an acknowledged problem with named ranges in SP1, and there's a hotfix available: Description of the Excel 2007 hotfix package (Excel.msp, Xlconv.msp, Graph.msp): April 30, 2009. I assume it has been incorporated into SP2, but I'm not sure about that.
Best wishes,
Hans

cecil
StarLounger
Posts: 98
Joined: 09 Sep 2010, 16:01

Re: 2007 sp2, range names disappear

Post by cecil »

Thanks. That helps. I will work with this info tomorrow and see if it solves the problem.

I checked the SP2 doc (http://support.microsoft.com/kb/953195" onclick="window.open(this.href);return false;) but did not see where this issue was addressed.

cecil
StarLounger
Posts: 98
Joined: 09 Sep 2010, 16:01

Re: 2007 sp2, range names disappear

Post by cecil »

I am to the point where I can make the process repeatable.

I have 2 workbooks as follows

Book1 contains two named ranges. MyRange1 has a scope of sheet1 and MyRange1 has a scope of Workbook. Note: two distinct ranges, two different scopes, both have the same range name.
Book2 has links to book1’s MyRange1 at the workbook scope. See the formula’s in column B. =VLOOKUP(A2,book1.xlsm!MyRange1,2)

If I open book2, then open book1, The range with the workbook scope is missing and the link in Book2 is changed to look at the sheet scoped range name.

As Hans said, one would think the HotFix would be in SP2, but I don't know that it is.
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: 2007 sp2, range names disappear

Post by HansV »

It took many tries, but eventually I was able to reproduce the error. I have the latest updates for Excel 2007 SP2, but not the hotfix, so apparently that hotfix has not been included in SP2 or a later update.

The obvious workaround would be to avoid using the same name at the worksheet level and at the workbook level. If I rename one of the names, the error does not occur, however often I open/close/save the workbooks.
Best wishes,
Hans

cecil
StarLounger
Posts: 98
Joined: 09 Sep 2010, 16:01

Re: 2007 sp2, range names disappear

Post by cecil »

HansV said: <<The obvious workaround would be to avoid using the same name at the worksheet level and at the workbook level. >>

I understand that. However, we have these huge models that this affects, and the output is due Monday. We wish we knew this before we converted the files. If I open the #1 before #2 and close #2 before #1, that also appears to get us by. We will repair before we need to run the models next year.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: 2007 sp2, range names disappear

Post by HansV »

Ouch! I hope you'll be able to meet the deadline!
Best wishes,
Hans