I have a Macros Module that i use in some calc documents.
That Module have a function that returns the sheet name of a given sheet index:
'How do I access the current sheet name in formula (to use in INDIRECT) - #6 by mariosv
Function SheetName(Optional nSheet)
If IsMissing(nSheet) Then
SheetName = ThisComponent.getCurrentController().getActiveSheet().getName()
SheetName = ThisComponent.getSheets().getByIndex(nSheet - 1).getName()
The problem comes when i open a calc document that uses that function.
In each call it throws the error “Missing property or method ‘getSheets’”.
After that, it works properly, so it seems to be related to some initialization issues… Like the “ThisComponent” object isnt initialized when opening the calc document.
Here there is another alternative function that do the same, but has the same issue when opening the document:
'How do you get a sheet name to display in a cell? - #3 by Lupp
’ REM In Calc Sheets are numbered starting with 1
’ REM The API starts numbering with 0.
’ sheetName = ThisComponent.Sheets(pSheetNum - 1).Name