I have it exactly as @erAck wrote (#NAME? and then works again).
As @erAck mentioned, it is utterly important to have some repro steps - only that could give a developer (@erAck is our Calc wizard) a way to see - and then to fix the problem.
Not every sheet has NamedRanges.
But there is some sense in backuping NamedRanges. For example, to add Comment field to a record, like in Excel, and make a comment to the name.
Range Options
In Excel:
### eeigor: Maybe I lost the names from the formulas because the sheet was saved and re-opened after the names disappeared. Or maybe the names were lost before upgrading to a new version of LO? Either way, my formulas don’t get recompiled with names in them, once the original range names are added back.
If you want to exclude these sheets:
doc = XSCRIPTCONTEXT.getDocument()
out = []
for sheet in doc.Sheets:
if sheet.NamedRanges:
out.append((sheet.Name, [(entry.Name, entry.Content) for entry in sheet.NamedRanges]))
print(sorted(out))
1 Like
menu:Sheet>Names>Insert… button [Paste All] dumps all names and their formulas to the range at active cell.
Hi, maybe a workaround. I recently had this issue, am having this issue, where named ranges / database named ranges just disappeared, lost a stack of named ranges, but 4 stayed, don’t know why. Cell references change to #NAME? and don’t magically return to normal when the named ranges are added back.
Here’s what I did: More carefully handling the contents.xml file got a result for me, putting the named ranges back into my calc workbook. That is, changing calc sheet from .ods file extension to .zip, extracting the content.xml file, extremely carefully copying and pasting the named ranges section of the xml file from another sheet [that had most but not all the namedranges] into this extracted content.xml file [everything between the table:named-expressions/ and </table:database-ranges> tags, saving it, copying it back into the .zip package replacing the existing copy there, then renaming the extension back from .zip to .ods.
This put the named ranges back BUT as you all know the cell references were still damaged, referencing #NAME? where each named range was. By using FileCompare, EditMenu → TrackChanges → CompareDocument, and comparing to another earlier copy of the file [same one as i got the content.xml tag section from], it somehow found the changes to cell formula when the sheet lost the named ranges. Using the filter in the file comparison dialog to narrow to #NAME?, i was able to undo many of those changes, reverting back to correct formula for many, not all, of the unexpected screwing up of named ranges. !!! Remember to do this on copies of the .ods files… just in case.
Also, being slightly a noob[ish] to libreoffice calc’s features on this front, i had no idea calc has 2 very separate but confusingly similar ways of naming ranges… both called fairly similarly, in my mind, named ranges and named database ranges… this is a bit weird when coming from my background… it confused me, doh, exacerbated by panicking about the weird named ranges issue and my main sheets getting screwed up. Weirdly too, this named range issue just magically jumped files and occurred in another file too… an earlier saved version of the calc file with the issue.
Anyhoo, this approach may help a little hopefully, a kinda indirect working fix at least, that doesn’t require rebuilding everything from near scratch.
Also, sorry for my ignorance, how could we generate a list of all database named ranges too please? the code in this and other threads is for named ranges only…
Very best to everyone, jb
1 Like
John, this sounds like a great way to attempt a recovery, thank you for sharing the details!
I decided the best way to avoid this is to never again use named ranges in LibreOffice. If I need to reference cells that are not nearby or on another sheet, I’ll use a different product. I copied everything that still worked to a new file, and simply said goodbye to the now useless formulas I developed over several years. I might have tried your approach if I had seen it sooner, but I’ve moved on. It’s reassuring to see that I’m not the only one seeing this problem, but disappointing that such a valuable feature is not reliable. I would certainly never use LO for anything critical. For small personal sheets it’s still good, when combined with frequent backups.
1 Like
It is great that you shared the steps!
Note though, that the most important thing in this issue is not how to recover, but rather, how to fix the program to prevent this from happening at all. And to allow that, a problematic file before the recovery would be nice and maybe helpful to allow developers to see the immediate file structure damage; sometimes that’s already enough to find the code problem. And even greater would be exact steps to reproduce the problem - like “this happens for me on this file; if I open it, add that, change that this way, and save, it breaks”.
4 Likes
Sub ShowNamesOfAllDBRanges()
Dim i%, dbrg, s$
With ThisComponent.DatabaseRanges
For i = 0 To .Count - 1
dbrg = .getByIndex(i)
s = s & Chr$(10) & dbrg.Name
Next
End With
If Len(s) > 0 Then
MsgBox "All Database Range Names:" & Chr(10) & Mid(s, 2) _
, MB_ICONINFORMATION, Title:=ThisComponent.Title
Else
MsgBox "No database ranges found." _
, MB_ICONINFORMATION, Title:=ThisComponent.Title
End if
End Sub
I actively use them and can not imagine how you can effectively organize data without them. Your judgments are hasty. In addition, this problem is not widespread (it is not written about).
2 Likes
Thanks very much indeed! I’ve not been able to find object hierarchies that i can make sense of and code examples like the one you’ve kindly just given, thanks very much Eeigor!
That’s very unfortunate, but you’ve gotta be comfortable using the program/s. Very best to you MrDave, jb
Hi again MikeK! Ah yes, good point. Thank you for your good guidance again, sorry about that. The .ods files with the issues contain critically private data… even the named ranges and database ranges give away private details. So I’m not sure how i can expunge/change the data and namedranges and not get rid of the issue/corruption. It may have been some corruption to the file or parts of the content.xml file in particular? Hard to know without example files and reproducible steps at your end, sorry.
I have a flu at mo, so will see if i can strip private data et al and keep the issue, when i’m more recovered…
Just for interest’ sake though, After copy/pasting info from content.xml files to get nemedranges back, turns out they’re mosttly database named ranges, the issue still happened, i narrowed down one of the triggers, when i deleted a copy of one of the sheets/tabs (not the main data sheet, A Copy of it], then the named ranges would disappear again… even renaming that sheet/copy didn’t matter, deleting it killed the named ranges. Then after manually deleting a bulk of named ranges and named database ranges, i could delete the copy sheet and several other sheets/tabs without triggering the issue. Very interesting. I’ve manually readded ranges and recreated formula… all seems fine now…
Interesting. It sounds like when you delete a copy of a sheet, the same named ranges from original sheet get deleted also. I wonder if those named ranges (or database ranges) were global, or was the scope specific to the sheet. That might be what caused me to lose so many named ranges but I can’t be sure, that was weeks (months?) ago.
1 Like
No.
Created a Calc document. Entered data and named the range. The name is global (in the document). Created a copy of the sheet. Calc added the same name, but local (on the sheet). Deleted the copy of the sheet. In the Manage Names dialog, one global name remains. However, both names are still present in the drop-down list of the Name Box field. The field is not updated. There is another complaint about this field: it sorts names case-sensitive, while the Manage Names dialog is not case-sensitive.
A message appears when you try to select a removed range.
Forgot to mention, in case this helps anyone? the content.xml in the section for namedranges named databaseranges had additional named database ranges that do not appear in the calc gui dialogs for named database ranges:
<table:database-range table:name="__Anonymous_DB__1" table:target-range-address=“Transactions…
<table:database-range table:name=”__Anonymous_DB__2"…
<table:database-range table:name="__Anonymous_Sheet_DB__0"…
<table:database-range table:name="__Anonymous_Sheet_DB__1" …
So you’d only know they’re there by looking at the xml, they don’t show up in the output from Eeigor’s ShowNamesOfAllDBRanges() code either… They were still in the .ods i manually fixed too, until i removed them from the content.xml manually too.
Curiouser and curiouser cried Alice…