Hi,
I have a Calc spreadsheet with numerous sheets which all have the same structure. I would like to get a Table of Sheets (like a table of content).
The following Excel spreadsheet from Eurostat has a first sheet called Sommaire which produces what I look for: ISOC_CI_IFP_IU__custom_5059701611825890155.xlsx
Is this possible ?
TIA
ftr
Yes, of course it possible - to accomplish this task, you need a not very complex macro.
First get object oSheets - all sheets of current spreadsheet
oSheets = ThisComponent.getSheets()
Then remove old TOS (Table of Sheets) if it present
If oSheets.hasByName(TOS_SHEETNAME) Then oSheets.removeByName(TOS_SHEETNAME)
Now get a list of the names of all sheets of the spreadsheet
oElementNames = oSheets.getElementNames()
Loop over all the elements of this array, get each of the sheets of the book, collect the necessary data from it and save it in the array.
For i = 0 To UBound(oElementNames)
oSheet = oSheets.getByName(oElementNames(i))
...
Finally, insert a table of contents sheet and fill it with the accumulated data
oSheets.insertNewByName(TOS_SHEETNAME, 0)
To create hyperlinks you can use the HYPERLINK() function