How to Extract a list of sheet names in calc HELP

[King_ZZ]

Ideally I want to have some kind of formula that will list all my sheets.

If possible to be dynamic so when I add / rename / move a sheet to a different location the list would update.



I have found these two things one is using VBA and the other is without.

I cannot get either to work as for the VBA I do not know how calc references sheets,

in th VBA code it is workbook.



These are both for MS Office and I do not know how to convert them



Extract a list of sheet names in your workbook without using a macro

Go to FORMULAS tab and click on Define Name. Fill in the box as shown below:



The function used is “=replace(get.workbook(1),1,find("]",get.workbook(1))," ")”

Then wherever you want the list of sheet names, simply type in this formula:
=INDEX(sheetlist,ROW())

It will show you names of the sheets in each cell. Drag the formula down to complete the list of sheets in the workbook.



This is the VBA way

You can do this only with VBA code. There is no worksheet function to get sheet names. (although there is I found one listed above)



Sub ListSheetNames()

Dim R As Range

Dim WS As Worksheet

Set R = ActiveCell

For Each WS In ThisWorkbook.Worksheets

R.Value = WS.Name

Set R = R(2, 1)

Next WS

End Sub



Select the cell where the list should start and run the code above.


I also found this VBA



Sub ListWorkSheetNamesNewWs()

'Updateby20140624

Dim xWs As Worksheet

On Error Resume Next

Application.DisplayAlerts = False

xTitleId = “KutoolsforExcel”

Application.Sheets(xTitleId).Delete

Application.Sheets.Add Application.Sheets(1)

Set xWs = Application.ActiveSheet

xWs.Name = xTitleId

For i = 2 To Application.Sheets.Count

xWs.Range(“A” & (i - 1)) = Application.Sheets(i).Name

Next

Application.DisplayAlerts = True

End Sub

[King_ZZ]

I can get sheet location with SHEET and how many sheets in my doc with SHEETS but I cannot see anyway of using that information yet

[Lupp]

Refer my answer here.

[karolus]

Hallo

First - All this stuff is useless use of coding, because there are smarter ways to navigate and handle with sheets.

• →Navigator
• the Sheettabs

Last not least, the messy VBA-shit above can be simply replaced by UNO-Api-methods:

# python example to list Sheetnames below current selected cell

def sheetnames():
    doc = XSCRIPTCONTEXT.getDocument()
    sel = doc.CurrentSelection
    sheet = sel.Spreadsheet
    c = sheet.createCursorByRange( sel )
    snames = tuple( zip( doc.Sheets.ElementNames ) )
    c.collapseToSize( 1, len( snames ) )
    c.setFormulaArray( snames )

[Lupp]

Did you notice that the code posted in my answer to another question linked in here not is useless but concise and effective and actually offering a solution to the task described in the subject of this thread?
There are advantages in not using an array for the purpose.

[karolus]

@Lupp
I notice your Answer actuallly after posting my answer.
I mean “useless” generally in terms of “cost-benefit ratio” here also for the python-snippet above
I mean “useless” because its annoying to see any other day some VBA -code which, -surprise,surprise- don’t work.

[Lupp]

@karolus: Ack