Excel 2007 lost names

The last post was about Excel 2007 losing certain formulas when saved in compatibility mode.

This post is across all file formats – from titme to time, for no apparent reason Excel 2007 will lose name ranges (this is SP1).

Its not file or user specific, not even name specific – just sometimes the odd one or two names disappear.

One workaround we have been doing is to define them in the workbook_open event – but this of course destroys the main benefit of names – that they adapt to edits. If you hardcode the address in VBA, you might as well just use the hardcoded ref and accept to risks and problems.

One approach I have used in the past is to put a ref in a cell as in =A1:D100. that will probably return some sort of error, but it will adpat with inserts and deletes within that range. Then on open define the name to refer to the formula of that cell.

Anyone else seen this disappearing name issue and got a reproducable case?

Or a solution?

cheers

Simon

Advertisements

23 Responses to “Excel 2007 lost names”

  1. Charlie Hall Says:

    As I mentioned in the last topic, I found that replacing the embedded dots (as in ‘long name v1.1.xltm) with dashes solved the problem for one model that I have been working on. Seems unlikely, but it was repeatable – but it also had to do with some number of times I saved it – after a while it would start to save without losing the range names, but then the next version it would start to lose the range names again. When I saved the file as a macro xls even with the embedded dots, the problem did not occur.

    I did try changing the range names, but that seemed to move the problem as opposed to fixing it – as in other names disappeared instead. This was a model with lots of global names (client’s style) – not sure if that contributed to the problem.

    –Charlie

  2. Bob Phillips Says:

    My experinece is exactly the same as Charlie. The daftest thing is that changing the name just moves it aong to another.

    I have an example that I can reproduce on my box any time, but I have sent it to MS and they say they cannot reproduce it, which makes it sound that the environment is a factor.

    As I mentioned earlier, I am not yet SP1, too mnay reported problems with SP1, so of course MS suggested it might be SP1 that fixed it, but Simon’s post suggest that he sees it in SP1.

    I noticed it because I have some workbook open code that uses the name, and the code failed … an ordered failure, but a failure all the same. As I said to Jon in another conversation

    … I agree with your conclusion, but as to whether the problem is sporadic, who knows. If you have a big spreadsheet that uses many names, it is quite possible that the disappearing name is buried deep in some table. I discovered mine because I was using that name in VBA, and so the code errored. My client encountered the problem and thought everything was fine thereafter. Unfortunately, we all know that less sophisticated users don’t check that their spreadsheets calculate correctly, and are too willing to accept errors as the norm …

    I just think this is a potentially horrendous error. If anyone has an absolutely reproducible example, I would love to see it and I can escalate it to MS as I am in dialogue.

    Charlie, you can get the release from Office Button>Excel Options>Resources>about Microsoft Excel 2007

  3. Bob Phillips Says:

    BTW, my solution. I created two names, _refPendings and _refPending, and on the open code I check both and action the one thta is there. Crap solution I know, and it only works for VBA not in Excel proper, but I had to get it working.

  4. Charlie Hall Says:

    @Simon
    Thanks for posting this topic – there was very little on Google about it, and its impact is significant, and hopefully a rare occurrence.

    I think I will put a check for the number of range names in the workbook_open event – and store the number in the beforesave event. At least then the user will know the model is likely corrupt and can start to look for the missing range names (comparing to previous versions)

    –Charlie

  5. Bob Phillips Says:

    I just created an O12 RTM VM, and opened a file that I consistently have a problem with. Apart from the fact that it saw my file as corrupt (obviously a later path changes the file format) and fixed it, the names were all there, not just saved if created, but actually there. So that suggests to me that the disappearing names are not being deleted, Excel just loses track of them This validates the fact that I thought I saw them in the XML sometimes when Excel didn’t see them.

  6. Chris Says:

    We are experiencing the problem on a worksheet that we have used for years, updated it to Excel 2007 format, it has worked since Sept and there are now disappearing range names.

  7. jonpeltier Says:

    I’ve heard a bit about this problem lately. Some people are taking to building tables with names and refers to definitions in a spare worksheet, and when problems start happening, they run through the list and recreate missing names.

  8. Harlan Grove Says:

    Are these lost names more likely to become lost due to their names or their contents? That is, are short or long names more likely to disappear? Are names referring to ranges or names referring to constants or other expressions more likely to disappear? Or does this appear to be random/arbitrary?

  9. Charlie Hall Says:

    There does not appear to be a pattern (yet). In my experience, I was losing about 5-6 names, and it was the same names each time. Most of the names referred to some portion of a table (column or row or subtotal), but some did not. Some names were long, others were short. If I changed the name, the problem moved to another name. All the names in my workbook referred to a range, rather than contained a formula or constant.

  10. Philip Says:

    I have the same issue. I have two ranges on the workbook and the second range always disappear when I copy and paste the file from one folder location to another. I end up hard-coding the range in VBa.

  11. rob Says:

    getting fairly consistent names loss on a spreadsheet … pain in the butt … loses different names all the time

  12. Patrick O'Beirne Says:

    For those who experience this:
    Does it only happen on the next file open?
    Might it help if you put in a class handler that on every event (maybe sheetchange and workbooksave is enough) tests that all names are still there and if not msgboxes “Whoops you just lost the name Zxxyz”
    If it does not do that while the file is open, then does it happen on saving?
    Just trying to think of a detection method here.

  13. Bob Phillips Says:

    That is similar to what I do Patrick. But it is utterly ridiculous that we have to, it negates the advantage of names. I see someone on ListServ has reported the issue today.

  14. Patrick O'Beirne Says:

    Thanks Bob – have you decided anything from the nature of the occurrences? In what circumstances it happens?
    If anyone has a workbook where it always happens, and is happy to release it, you could send it to me for a test.
    If it can be reproduced on two machines then we have it nailed. Perhaps a comparison of the two XML sources might indicate what corruption is happening.
    Does it still happen when you save in XLSB?

  15. Bob Phillips Says:

    Hi Patrick,

    No, no such luck.

    I had a workbook where it happened to me every time and I sent it to MS and it didn’t happen there. I hadn’t installed SP1 so they said maybe SP1 had fixed it. I fired up a VM with SP1 and lo and behold it didn’t happen on this workbook. But others have this probelm with SP1. One guy at Nick’s site said he could reproduce it every time, but he never sent me the file. He also claimed that MS were issuing a hotfix in Arpil but that never materialised.

    The odd thing is that when it happened to me, you could still see the name in the embedded XML.

  16. Trefor Says:

    I have a similar issue.

    I have update from Office 2003 to 2007 and now I am having this issues as well.

    My code creates a new file in Office 2007, then copies a sheet from the 2003 upgraded file to the new file. So technically the file is new, but the sheet had been upgraded from a 2003 file. Without reason and without warning I note 2 named ranges get removed (there maybe more, but I have only stumbled on these 2). The named range is for a single cell and contains a formula.

    This happens more often than not when I create the new file, but its always the same to named ranges.

    I have upgrade to SP2 and applied every available fix/patch including the hot fix (KB968863) and it still broken.

    Does anyone have any suggestions?

  17. Trefor Says:

    I have been playing with this most of today trying to figure this out and this is what I have found out:

    (Looking at Name Manager)

    1. Some, but not all names have a “Refers To” that point to the Source workbook instead of the Target workbook. What is also strange is my source file is on a network drive “P:”, but the “Refers To” says “C:” and the rest of the path is correct. At this point the Value is reporting correctly albeit from the Source sheet I presume.

    2. On completion of the code posted above I close the Target file and attach it to an email. When you open the attachment from the email all the Named Ranges where they referred to the Source file now have a Value of “#REF!”. So the phantom disappearing Named Ranges that I have found many people talking about may be a broken link in the named range. i.e. if you look at the cell where you expected a named range its gone, but if you look in Name Manager its there, but with a broken reference to the cell.

    3. Being new to Name Manager, I then realised that what I was looking at when I selected Name Manager from the Target, was all the Named Ranges for all open workbooks. So with this in mind, what I was seeing is the Source Named Range and a completely missing Named Range from the Target i.e. the VBA copy does not seem to have copied the Named Range.

    4. I re-ran the above code and paused it at the point it had copied the second sheet which was the first sheet with a bunch of Named Ranges on it and sure enough it looks like the Ranges did not copy.

    5. I decided to manually deleted the sheet and manually copy the sheet back with the code still in a paused state. This gave me some very interesting and varied results:

    a. Result 1 – Named Range copied ok, range showing Value = “#REF!” and Refers To = “=#REF!xxxx” where xxxx = the correct cell reference.
    b. Result 2 – Named Range did not copy, range to source showing the correct Value,

    6. Decided to delete the sheet again and see what is left. The Result 2 above remained unchanged. The good copy from Result 1 has good as you would expect, but the #REF! lines remain.

    7. Copy the sheet back and deleted all the #REF! lines and I now seem to be where I was at 4 above. So now I have about half pointing to the Target sheet and the other half pointing to the Source sheet.

    8. Decided to close the Target, manually open a new workbook and manually copy the sheet, it worked fine. At this point there is no reference to the Source file for this sheet.

    9. Deleted the workbook I manually created, restarted the code and skip over the file create part and manually created a new one. Allowed the code to SaveAs to the temp file name. Then manually copy the sheet, checked and only reference was to the local sheet so all GOOD at this point. Deleted the sheet and allowed the code to continue to copy the sheet as normal (it now BROKEN):

    a. Result 1 – Named Range did not copy, range showing Value = “#REF!” and Refers To = “=#REF!xxxx” where xxxx = the correct cell reference.
    b. Result 2 – Named Range did not copy, range to source showing the correct Value,

    10. Deleted the sheet, deleted all references to ranges with errors and allowed the code to copy again. So now I have about half pointing to the Target sheet and the other half pointing to the Source sheet.

    11. Deleted the sheet, deleted all references to ranges with errors and manually copied again, its still BROKEN. No change to the above, i.e. I have about half pointing to the Target sheet and the other half pointing to the Source sheet.

    This has to be an Excel bug, it just does not make sense. It seems once the workbook is “broken” it is not possible to manually or via code to successfully copy a sheet.

    Plan B

    I wrote some code to create me a workbook in 2003, with 30 sheets each with 1000 unique named ranges on them. Copied the file to another computer I have running 2007. Then I slightly modified the code I posted earlier so it would run with my test workbook and it all worked perfectly. (How to kill a couple of hours and not be able to create a simple test program.)

    I am running out of ideas here.

  18. Patrick O'Beirne Says:

    “On completion of the code posted above”
    I don’t see any code posted above.
    Care to share it?
    send to sysmod.com, email mail3

    In short, are you saying that a reference to Sheet1 in the source becomes #REF in the target because during a pause in execution you deleted the sheet being referred to? That would be expected behaviour.

    Did you attempt a clean up by round-tripping the source file, ie saving as HTML, opening and re-saving as XLS?

  19. Trefor Says:

    Patrick,

    Thanks for your reply, code is below, sorry about that.

    What I am saying is I have a workbook where a copy a bunch of sheets to a new workbook.

    If I copy the sheet manually it works fine.
    If I copy the sheet with the below code the Named Ranges are corrupted as described below.
    If I now delete the sheet again, remove all the corrupted reference and try to manually copy the sheet again the Named Ranges are corrupted as described below.

    “Did you attempt a clean up by round-tripping the source file, ie saving as HTML, opening and re-saving as XLS?” I had never heard of this until I read your comment. I read enough on the net to become dangerous, but not enough to save my self a lot of time wasting.

    Just to put this in perspective, if I save my file in HTML:
    • 10,000 VBA lines of code gets stripped. Although this was fairly easy to copy back.

    • All Named Ranges that point to multiple cells get stripped as well as any that I presume the round tripping perceives as duplicates. I have approx. 500 named ranges and I spent about 5 hours today copy back all the missing ranges and truncated references. While it took me much longer than I was expecting I thought it was a worth while exercise as the missing references may have been an indication that 2007 was not happy and they needed to be fixed.

    • 100’s of conditionally formatted cells get the conditional formatting removed, 100’s of cells with Validation get the Validation removed, 1000’s of cells with formulas get the formulas replaced with values. This would take me weeks to re-create, so I decided to starting copying entire sheets from the old workbook to the new. After coping 6 or so sheets of my 50 sheet workbook I realised the Named Ranges in Name Manager were reverting from a scope of the sheet to workbook which I suspect might be part of the problem, so I have stopped for now.

    I can see that round tripping might actually clean a few things up, but I virtually need to re-create the entire workbook. There has to be an easier way to achieve the same result? OR an answer to why 2007 is doing this in the first place that is needs clean up.

    In VBA code:
    oBook.SaveAs FileName:=AppPath & “\Temp\TSWorkbookTemp.xlsm”, FileFormat:=xlOpenXMLWorkbookMacroEnabled
    xSheetCounter = 0
    For Each WSSource In Workbooks(WorkbookMain).Sheets
    xSheetCounter = xSheetCounter + 1
    If xSheetCounter = 1 Then
    WSSource.Copy After:=oBook.Sheets(1)
    Set WSTarget = WSSource
    Else
    WSSource.Copy After:=oBook.Sheets(WSTarget.Name)
    Set WSTarget = WSSource
    End If
    ‘ NOTE: There is an excel issue limiting the number of worksheets that can be copied to another workwork. The work around is to periodically save the workbook.
    ‘Save, close, and reopen after every 20 iterations:
    If xSheetCounter Mod 20 = 0 And xSheetCounter 0 Then
    oBook.Close SaveChanges:=True
    Set oBook = Nothing
    Application.DisplayAlerts = False
    Set oBook = Application.Workbooks.Open(AppPath & “\Temp\TSWorkbookTemp.xlsm”)
    Application.DisplayAlerts = True
    End If
    Next

  20. Sterling Says:

    I have a very large Excel workbook that always seems to corrupt the names on any other open workbooks. Odd though, it only happens on one of my workstations (when it is opened there). I think the issue must have something to do with a combination of OS | SP | Version of Excel. NOTHING on MS Support site about this…they act as if it doesn’t exist. Anyone know if Excel 2010 corrects this?

  21. Joe Serdakowski Says:

    To deal with this problem, create a worksheet in your workbook with the worksheet name “Names”

    Include in the “DefineNames” procedure the following line:

    Sub DefineNames()
    ‘…
    Set sN = ThisWorkbook.Sheets(“Names”)
    ‘…
    End Sub

    Include the following line in the Workbook_BeforeSave procedure:

    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    ‘…
    Call WriteNames
    ‘…
    End Sub

    Place the following procedure someplace in a VBA module:

    Sub WriteNames()
    Call DefineNames
    sN.Cells.Clear
    i = 1
    j = 0
    For Each c In bO.Names
    If InStr(CStr(c.RefersToR1C1), “#REF”) = 0 Then ‘ occasionally a reference has been deleted and is no longer in use
    j = j + 1
    End If
    i = i + 1
    sN.Cells(i, 1) = c.Name
    sN.Cells(i, 2) = “_” + c.RefersToR1C1
    Next
    sN.Cells(1, 1) = j
    End Sub

    Place the following code in the Workbook_Open procedure:

    Private Sub Workbook_Open()
    ‘…
    Call DefineNames
    If ThisWorkbook.Names.Count sN.Cells(1, 1) Then
    i = 2
    Do While sN.Cells(i, 1) “”
    ThisWorkbook.Names.Add Name:=sN.Cells(i, 1), RefersToR1C1:=Mid(sN.Cells(i, 2), 2)
    i = i + 1
    Loop
    End If
    ‘…
    End Sub

  22. Dave de Says:

    Hi,

    Was having the same issue but in 2010.

    working in compatibility mode.

    save as .xls

    close

    re-open

    bang: ‘data may have been lost’

    Lost over 200 defined names – no pattern but most were sheet level. The issue for me was with workbook protection. When i saved without workbook protection the names weren’t lost.

    hmmm so distribute my workbooks without protection or distribute worthless protected ones….

  23. Lacey Martini Says:

    A big thank you for your article post. Cool.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: