Ask Your Question
0

Get sheet names as a list [closed]

asked 2014-10-09 14:07:47 +0200

Javeal gravatar image

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

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2016-03-04 20:08:55.605205

2 Answers

Sort by » oldest newest most voted
3

answered 2014-10-09 15:45:02 +0200

Lupp gravatar image

updated 2014-10-10 08:49:13 +0200

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

@ROSt52 (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.

edit flag offensive delete link more

Comments

Thanks, this is great! Saw pretty much the same function here, just a little bit extended: https://forum.openoffice.org/en/forum... My question is: How can I set a real error instead adding a text value?

EinsamerBaumimWald gravatar imageEinsamerBaumimWald ( 2016-02-10 21:33:20 +0200 )edit

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).

Lupp gravatar imageLupp ( 2016-02-15 13:16:45 +0200 )edit

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.

samuelzen gravatar imagesamuelzen ( 2017-11-18 06:53:16 +0200 )edit

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?

Lupp gravatar imageLupp ( 2017-11-18 23:12:49 +0200 )edit

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

hhaddow gravatar imagehhaddow ( 2018-03-05 10:34:39 +0200 )edit

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

Lupp gravatar imageLupp ( 2018-03-05 13:10:35 +0200 )edit

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.

hhaddow gravatar imagehhaddow ( 2018-03-06 22:23:31 +0200 )edit
0

answered 2014-10-10 04:42:11 +0200

ROSt52 gravatar image

updated 2014-10-10 04:42:52 +0200

Could the functions SHEET or ADDRESS be also a solution?

edit flag offensive delete link more

Comments

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!

Lupp gravatar imageLupp ( 2016-02-15 13:22:53 +0200 )edit

Question Tools

1 follower

Stats

Asked: 2014-10-09 14:07:47 +0200

Seen: 5,477 times

Last updated: Oct 10 '14