How to retrieve the current document in a Calc formula

prrvchr · June 28, 2023, 11:18am

Hi all,

I believe it is possible to retrieve the Calc document in the method of a Calc formula.
If anyone has more information, thank you.

mikekaganski · June 28, 2023, 12:52pm

You do not clarify what “method of a Calc formula” is. Is it a macro (say, in Basic)? A VBA macro? An extension with code written in Python?

It could be possible for VBA (but only in-document), and it’s definitely possible for an extension.

JohnSUN · June 28, 2023, 11:30am

Do you mean “current document name”? Well, in principle you can (if the spreadsheet is already saved and has a path and a filename)

=CELL(“FILENAME”)

prrvchr · June 28, 2023, 11:40am

Hi JohnSUN,

I write a CalcAddIns and I would like to be able to have access to the Calc document in the body of the method called by the CalcAddIns formula.
I need to be notified when closing the Calc document.
I seem to have read that some Calc formulas access the current document?

JohnSUN · June 28, 2023, 11:46am

Ah, after all “to the document as to object”? Why ThisComponent is bad?

prrvchr · June 28, 2023, 11:54am

I need to register a close handler, and if I open multiple ods files at the same time, I’m not sure This.Component is enough? I must try…
But I still think that it is possible to find the document maybe as a parameter of the Calc formula…

JohnSUN · June 28, 2023, 11:59am

No. A function in a formula receives parameters as a number, string, or array. That’s it, there are no other options. The functions don’t work with objects, images, background or font colors… Pass the full URL of the file as a string, or try to identify ThisComponent in the code - passing it from outside will not work.

prrvchr · June 28, 2023, 12:01pm

Too bad, thank you for your confirmation…

sokol92 · June 28, 2023, 12:51pm

There are different ways.

Option VbaSupport 1

' Arg - any cell.
' Returns:
' Cell value
' Cell address
' Document name
' Document path
Function CellInfo(arg)
  Dim oRange, oDoc
  oRange=arg.CellRange
  oDoc=oRange.Spreadsheet.Drawpage.Forms.Parent  ' I don't know the author of this trick :)
  CellInfo="Cell value: " & arg.value & ";" & _
           " Cell address: " & oRange.AbsoluteName & ";" & _
           " Document name: " & oDoc.Title & ";" & _
           " Document path: " & ConvertFromURL(oDoc.URL) 
End Sub

Try in cell B1:

=CELLINFO(A1)

prrvchr · June 29, 2023, 8:15am

Thank you for your confirmation, but I found where I read that, it’s a post by Hubert Lambert on the OpenOffice forum.

In Hubert’s article we can actually read:

The com.sun.star.table.XCellRange type is used to obtain as an argument, not the values of the range, but the corresponding uno CellRange object, which in particular allows access to the properties of the referenced cells.
The com.sun.star.beans.XPropertySet type is used to obtain the “document” object containing the formula as an argument, thus providing access to its properties. This type, which can only be defined for a single argument per function, is also implicit: it is “invisible” to the user, Calc providing the argument when evaluating the formula (the extension Numbertext for example uses an argument of this type to access the default language of the interface).

Anyway, thanks for confirming that it was indeed possible…