How do I link to an external named range without DDE in LibreOffice Calc

Question

I wish to bring over a total from one workbook to another without using DDE as that is deprecated and is causing me problems. The total is in a single cell with a range name. Both workbooks are in the same directory.

If I try Insert | Link or dragging the range name from the source sheet using the navigator, it inserts the contents of the cell, character for character. This is a formulae, which gives the required result in the source sheet but is utterly meaningless in the destination.

If I type "=" in the cell contents toolbar then click on the cell in the source document, this works fine, but it inserts the cell reference raw rather than with its range name='file:///home/simon/Home/Files/Accounts/Peracs14-15.ods'#$Journal.F412. If I try to substitute the cell reference with the range name='file:///home/simon/Home/Files/Accounts/Peracs14-15.ods'#Journal.Number20, it produces an error#NAME. If I enter the range name on its own ='file:///home/simon/Home/Files/Accounts/Peracs14-15.ods'#Number20 pressing [Enter] causes the cell contents to change to ='file:///home/simon/Home/Files/Accounts/Peracs14-15.ods'#file:///home/simon/Home/Files/Accounts/Peracs14-15.ods, which is bizarre. The original path is still there but the range name has been substituted for the path to the file without the range name such that the range name has disappeared. This happens whether "autocomplete" is on or off. Is my poor old computer still haunted by Bill Gates' paper clip?

As I need to create a new sheet at each period end, it would be a lot of work to re-insert all these references with the correct cell references. I know I am missing something obvious, can someone help me?

Platform - Linux Mint 17.3 with xfce, based on Ubuntu 14.04. GUI - xfce V4.10 Application - LibreOffice Version: 5.0.3.2

Answer 1

From https://wiki.openoffice.org/wiki/Documentation/OOo3_User_Guides/Calc_Guide/Referencing_other_documents:

=’file:///Path & File Name’#$SheetName.CellName.

I infer from this that there is no way to include a named range as part of the reference. However, there are various things that can be done with macros. One idea I had uses the following code:

Sub GetExternalNamedRanges
    Dim rangeNames As New Collection
    Dim namedRanges As New Collection
    oSheet = ThisComponent.Sheets.getByName("External Ranges")
    column = 1
    Do
        filepath = oSheet.GetCellByPosition(column,0).getString()
        If filepath = "" Then
            Exit Do
        End If
        otherComponent = StarDesktop.loadComponentFromUrl( _
            filepath, "_default", 0, Array())
        oRanges = otherComponent.NamedRanges
        namedRanges.Add(oRanges)
        For Each oRange In oRanges
            rangeName = oRange.getName()
            If Not Contains(rangeNames, rangeName) Then
                'rangeNames.Add(oRange.getContent(), oRange.getContent())
                rangeNames.Add(rangeName, rangeName)
            End If
        Next
        column = column + 1
    Loop
    row = 1
    For Each rangeName In rangeNames
        column = 0  'column A
        oCell = oSheet.GetCellByPosition(column,row)
        oCell.setString(rangeName)
        'oCell.setString(oRange.getName())
        For Each namedRange In namedRanges
            column = column + 1
            If namedRange.hasByName(rangeName) Then
                oRange = namedRange.getByName(rangeName)
                oCell = oSheet.GetCellByPosition(column,row)
                oCell.setString(oRange.getContent())
            End If
        Next
        row = row + 1
    Next
End Sub

' Returns True if the collection contains the key, otherwise False.
Function Contains(coll As Collection, key As Variant)
    On Error Goto ErrorHandler
    coll.Item(key)
    Contains = True
    Exit Function
ErrorHandler:
    If Err <> 5 Then
         MsgBox "Error " & Err & ": " & Error$ & " (line : " & Erl & ")"
    End If
    Contains = False
End Function

To use this, create a sheet in the main document called "External Ranges". In cell A1 type "filename". In cell B1, enter the path to the first file that needs to be referenced, starting with "file:///". In cell C1 enter the second file if any, and D1 etc if there are more files.

Now run the macro, which will pull in the locations of all the named ranges in those documents. The result looks like this:

Then use these ranges with VLOOKUP:

=INDIRECT("'" & VLOOKUP("filename", NamedRangesList, 2) & "'#" & VLOOKUP("animal", NamedRangesList, 2))

That will pull in the value of the range named "animal" (which is located at $Sheet1.$C$2 in this example) from the first file.

This formula using INDIRECT gets rather complicated, but it could be shortened by creating a Basic function. The function could just take the file number and range name as arguments, and it would perform the INDIRECT and VLOOKUP work.