I’m using ThisComponent.NamedRanges but it only returns global scope named ranges.
oDoc = ThisComponent
oRanges = oDoc.NamedRanges
For i = 1 To oRanges.Count
rangeName = oRanges(i-1).Name
' do something with rangeName
NextDo you mean something like this?
Sub test
Dim oRes As Variant, i As Long
oRes = getAllNamedRanges(ThisComponent)
For i = LBound(oRes) To UBound(oRes)
Print (i+1) & ") " & oRes(i)(0) & " - " & oRes(i)(1)
Next i
End Sub
Function getAllNamedRanges(oDoc As Variant) As Variant
Dim oNamedRanges As Variant
Dim oElementNames As Variant
Dim oSheets As Variant
Dim oSheet As Variant
Dim aResult As Variant
Dim sName As String
Dim i As Long, j As Long
aResult = Array()
oElementNames = ThisComponent.NamedRanges.getElementNames()
sName = "Global"
For j = LBound(oElementNames) To UBound(oElementNames)
AppendToArray(aResult, Array(sName, oElementNames(j)))
Next j
oSheets = ThisComponent.getSheets()
For i = 0 To oSheets.getCount()-1
oSheet = oSheets.getByIndex(i)
sName = oSheet.getName()
oElementNames = oSheet.NamedRanges.getElementNames()
For j = LBound(oElementNames) To UBound(oElementNames)
AppendToArray(aResult, Array(sName, oElementNames(j)))
Next j
Next i
getAllNamedRanges = aResult
End Function
Sub AppendToArray(oData As Variant, ByVal x As Variant)
Dim iUB As Long, iLB As Integer
iLB = LBound(oData())
iUB = UBound(oData()) + 1
ReDim Preserve oData(iLB To iUB)
oData(iUB) = x
End SubThat’s exactly what i need. Thx
Hi, i can’t get this to work. I’ve used breakpoints to try and workout where it fails. The macro code falls over on the lines where ubound and lbound are used.
I had almost all my named ranges disappear from an important ods for no known reason, and am trying to find a way to copy them from a backup copy of the ods to the ods where they’ve all disappeared from. I’d hoped this code may help me list the named ranges at least… names and cell ranges, so i can more easily copy them over… Any help greatly appreciated, jb
This is not a helpful description for someone trying to diagnose your problem. Please edit your comment to include the actual error message you receive.
1 Like
Hello John!
Which LBound and UBound does the macro fall on? These functions occur four times in the code.
Can you show samples of two files - without named ranges and the same one that stores the names? (We may need to change the macro code to solve your problem)
By the way, what format are your files in? I hope you didn’t use xls(x)?
1 Like
Hi Robleyd and JohnSUN, sorry there wasn’t a lot of info in my post. I’m using complex .ods files with lots of financial info and calculations, and some vlookups etc.
Version: 7.3.3.2 (x64) / LibreOffice Community
Build ID: d1d0ea68f081ee2800a922cac8f79445e4603348
CPU threads: 12; OS: Windows 10.0 Build 19043; UI render: Skia/Vulkan; VCL: win
Locale: en-AU (en_AU); UI: en-GB
Calc: threaded
There is no actual error message, code execution simply stops. Error handling doesn’t go off either so I can’t use that to msgbox myself error information. The issue happens with any line of code with ubound or lbound relating to namedrange objects/variants.
It turns out this approach isn’t going to help much unfortunately regardless. Even when I define the named ranges in the file again, every cell that uses them still has the #NAME? placeholder. So I’d have to go and fix each compromised cell too… there are very many of them.
So unless one of you lovely people can kindly suggest a solution, I’m going through and manually copying cell contents over bit by bit, avoiding the cells with the compromised formulas, and recreating the formulas after copying the data… it’s painful. Oh i also stumbled upon Named ranges disappear in Calc - #3 by joshua4 and have tried editing the content.xml file, carefully cutting/pasting the named ranges in from a working .ods to the .ods that lost the named ranges, but that didn’t work at all either.
Sigh… sorry to have bothered you both with this, but please let me know if you think of anything possible? I very much appreciate your taking the time to have read and replied above.
Very best regards to you both, jb
Sorry but quick update… more carefully handling the contents.xml file got a result, ie changing calc sheet from .ods file extension to .zip, extracting the content.xml file, extremely carefully copying and pasting the named range section of the xml file from another sheet into this extracted content.xml file, 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 the cell references were still damaged, referencing #NAME? where each named range was. By using FileCompare, EditMenu → TrackChanges → CompareDocument, and comparing to another file, 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. !!!
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.
Anyhoo, a very very big thank you for taking the time to answer my earlier query about this. I have a working fix at least, that doesn’t require rebuilding everything from near scratch.
Very best to you JohnSUN Robleyd and everyone, jb