Get sheet names as a list

Hey guys,

i am kinda stuck right now,… I got a workbook with lets say 10 sheets. These sheets have other names than sheet1, something like abc, hjk, wer etc…

And in my last sheet, i want to have a list of all sheets but the last 3, so i want to list the names of sheet 1 to 6. I also want to get values from these sheets in my summary, fe =abc.$M$59, =hjk.$M$59, wer.$M$59. (Which is the same cell in every sheet 1-6)

The reason why I don’t want to do this by hand is that I am going to add sheets over time (between 1 and 6, while the last 3 will always stay the same) That’s why I want them to be added automatically, the name and the value.

I hope you guys can understand what I am trying to do and can help me.

regards

You will need to get the names by the numbers of the sheets. I don’t know a way to do this by standard functions.

That is very strange in my opinion as there as well exist means to get the names (addresses) of cells by there column/row numbers as also to relatively reference them by OFFSET().

On the other hand, there are no means to reference a sheet without knowing its name.

There were threads in other forums about this, too, and even the wise man (f/m) there didn’t know a way.

A user defined function, on the other hand, can solve the problem in different ways and is really simple. One possibility:

Function SheetNameByNumber(Optional pNo As Long) 
REM There seems not to exist a StandardFunction for this purpose!
REM Note: A cell will NOT RECALCULATE DUE TO A CHANGE OF THE SheetName!
REM You will have to FORCE RECALCULATION "on any event".
REM This can be done by appending "+NOW()*0" to the parameter expression, e.g.
Dim oDoc As Object
Dim oSheet As Object
Dim nSheets As Long

IF IsMissing(pNo) THEN
	SheetNameByNumber = ".Parameter.Missing."
	Exit Function
ENDif 
oDoc = ThisComponent
nSheets = oDoc.Sheets.Count
IF (pNo > 0) AND (pNo <= nSheets) THEN
	oSheet = oDoc.Sheets(pNo-1)
	SheetNameByNumber = oSheet.GetName
ELSE SheetNameByNumber = ".No.Result."
ENDif
End Function REM SheetNameByNumber

Alas! There are fundamental disadventages of working with custom functions.

Example attached: SheetNames.ods

@ROSt53 (regarding the question published as an answer):

If you think so, please tell how that might work.

SHEET() will return the sequential number (starting with 1) of the sheet the referenced range or cell is placed in. No name!

ADDRESS() will return an address (notation modifyable) of a single cell on the base of row number and column number. IF the address shall explicitly point into a sheet, the NAME must be explicitly passed via a parameter.

There is one function returning a sheet name not already passed as a parameter: CELL() used with the controlling (first) parameter “address”. But: To get the name of a sheet that way we must provide a reference into the sheet as second parameter. The only function call I know returning a sheet name without basing on a reference is CELL(“filename”) without a second parameter. But: It only can return the name of the current sheet.

A list of sheet names can only be produced setting at least a reference into every sheet that shall occur.

My attached example does already demonstrate this approach in column A.

The most appropriate way to implement access to (other) sheets without knowing the name should be adding an optional parameter to OFFSET() passing the offset in sheet number.

Thanks, this is great! Saw pretty much the same function here, just a little bit extended: [Solved] How to get list of sheet names into a sheet (View topic) • Apache OpenOffice Community Forum My question is: How can I set a real error instead adding a text value?

This question should better be posted as an independent question tagged 'calc, ‘macros’ and ‘error-value’, ‘user-function’ or similar. I also once had it in mind, but did not resolve it (no actual need).

The above solution given in the downloadable example “SheetNames.ods” doesn’t appear to work with the current version of Libreoffice. The spreadsheet is full of errors–columns of “#values”, for example. Any ideas? Thanks.

The user function posted here still works flawlessly for me in LibO Calc V 5.4.3.
In fact I cannot imagine a reason for what it might fail. It uses only most basic features of BASIC and API.
Did you note that the function expects sheets numbered as in Calc (starting with 1).
Can you upload an example .ods showing the problem for you?

This was working perfectly for me but when I opened my file this morning I got an error saying “calc property or method not found Sheets”, please help

Did the error vanish when you forced a recalculation (Ctrl+Shift+F9) for the already opened document?

Figured it out, it was because it was referencing cells in a second file that was also using the macro to get sheet names for the output tables.

Could the functions SHEET or ADDRESS be also a solution?

SHEET is returning the number, not the name. ADDRESS is not suitable and CELL("address";Reference) embedded into parsing function calls can do it only for other sheets, and this only, if a reference into this sheet was created in advance - by a click, or for any sheet relying on an ADDRESS expression using the name . A catch-22 dilemma!