Please anyone help me?
Solution already available in this forum. But it has taken to much of time complete it.
Yes, it took so much time with 100+ tabs.
1 Like
Function SheetNames()
SheetNames = ThisComponent.Sheets.getElementNames()
End Function
When called as an array function (finished with Ctrl+Shift+Enter) =SHEETNAMES() returns a horizontal list. For a vertical list call =TRANSPOSE(SHEETNAMES()).
'' List Sheets By Names = Sub ListPlanByNames
Sub ListPlanByNames
Dim name As String, cont As Integer
For cont = 0 To ThisComponent.Sheets.getCount() - 1
name = name & ThisComponent.Sheets(cont).Name &Chr(10)
Next
msgBox name, 0, "Existing sheets:"
End Sub
I only had 20ish tabs to pull together so this was a quick and easy solution. I can see that if I had a magnitude more that this could be cumbersome and the query solutions listed below would be more beneficial. But for a quick shot… this was the answer!
There are basically two ways:
- Make the UDF
-UserDefinedFunction-contained in the attached example document (or an equivalent one) available and apply it. - Use the CELL() fuinction with the first argument “ADDRESS”, and a reference to a cell in the sheet you want to see the name of as the second argument.
Remember: Spreadsheet documents with so many sheets are a basically bad idea.
The second way requires to “click” into a cell of the wanted sheet, and therefore saves some time (for typing names) and can create updated results if names were changed, but isn’t exactly “automatic”.
A standard function returning sheet names based on sheet numbers (or similar) does not exist.
disask87230SheetNames.ods (14.4 KB)
Extremely reduced UDF:
Function singleSheetName(pZ)
singleSheetName = ThisComponent.Sheets(pZ - 1).Name
End Function
so the question is »get all tabnames in a sheet«
→→3. list all Sheet-names below current selected cell
def list_sheetnames_below_selection(*_):
doc = XSCRIPTCONTEXT.getDocument()
sel = doc.CurrentSelection
sheet = sel.Spreadsheet
cursor = sheet.createCursorByRange(sel)
out = tuple(zip(doc.Sheets.ElementNames))
cursor.collapseToSize(len(out[0]), len(out))
cursor.DataArray = out
its python… and the organizer for python-code is apso.oxt from here
1 Like
Thanks @karolus, that worked perfectly.
I am using Ubuntu, and installing apso.oxt I got a Failed to register package for vnd.sun.star.expand error, the same as this one. Installing libreoffice-script-provider-python (also mentioned on jmzambon/apso) and restarting Calc fixed it, and I could install the extension.
After reading Python Guide - Introduction - The Document Foundation Wiki I placed your code in ~/.config/libreoffice/4/user/Scripts/python/list-all-sheets.py
I could then run the macro in Calc from Tools → Macros → Run macro → My Macros → list-all-sheets, and it output all the sheets in a list. Beautiful!
Here is a StarBasic version of the subroutine:
Sub ListTabNames()
Dim oDoc, oSheets, oSheet, oTargetSheet, oTargetCell, oCell as object
Dim i, iShCount, iRow, iCol as integer
oDoc = ThisComponent
oSheets = oDoc.getSheets
iShCount = oSheets.Count
oTargetSheet= oDoc.getcurrentcontroller.activesheet
oTargetCell = oDoc.getCurrentSelection()
iRow = oTargetCell.GetCellAddress.Row
iCol = oTargetCell.GetCellAddress.Column
For i = 0 to iShCount-1
oCell = oTargetSheet.getCellByPosition(iCol, iRow+i)
oSheet = oSheets.getbyIndex(i)
oCell.String = oSheet.name
next i
End Sub
Hi Zizi64
Going to use your solution. First time with Calc. What are the steps to load the code, How do I activate to run, button?
Can it be run when a new tab is created.
Thankyou
Charles Harris
Hi
Thankyou
Charles