[Solved] Sorting sheets alphabetically minus the first 3

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
Lehoi
Posts: 6
Joined: Thu Jan 07, 2016 5:16 pm

[Solved] Sorting sheets alphabetically minus the first 3

Post by Lehoi »

I have a need to sort alphabetically the calc workbook sheets minus the first 3.
I found this macro that works ok in excel, but obviously it is not made for OO :cry:

Code: Select all

Sub SortTabs()
For s = 4 To Sheets.Count - 1
For ss = s + 1 To Sheets.Count
If Sheets(ss).Name < Sheets(s).Name Then
    Sheets(ss).Move Before:=Sheets(s)
End If
Next ss
Next s
End Sub
Indeed I test this OO macro and sort alphabetically ok, but I don´t know how exclude the first 3 sheets.

Code: Select all

REM  *****  BASIC  *****
Sub Ordena_Hojas
If (Not GlobalScope.BasicLibraries.isLibraryLoaded("Tools")) Then
   GlobalScope.BasicLibraries.LoadLibrary("Tools")
End If
Sheets = ThisComponent.Sheets
Names = Sheets.ElementNames
SortedList = BubbleSortList(Names) 'BubbleSortList is a macro in the Tools Library
for i = 0 to UBound(SortedList)
   Sheets.MoveByName(SortedList(i), i)
next i
End Sub
Please can somebody help me to solve this problem? I tried many things but none works (I am a total newby :crazy: ).
Thanks!
Last edited by Lehoi on Sat Jan 09, 2016 2:08 am, edited 1 time in total.
OpenOffice 4.12 Windows 7
User avatar
karolus
Volunteer
Posts: 1158
Joined: Sat Jul 02, 2011 9:47 am

Re: Sorting sheets alphabetically minus the first 3

Post by karolus »

Hallo

Nobody need this Basic-shit ... Solution in python:

Code: Select all

def sort_sheets_except_first_three(*_):
    doc = XSCRIPTCONTEXT.getDocument()
    sheets = doc.Sheets
    sortednames = sorted( sheets.ElementNames[3:] )
    for n, name in enumerate( sortednames, 3 ):
        sheets.moveByName( name, n )
Karolus
AOO4, Libreoffice 6.1 on Rasbian OS (on ARM)
Libreoffice 7.4 on Debian 12 (Bookworm) (on RaspberryPI4)
Libreoffice 7.6 flatpak on Debian 12 (Bookworm) (on RaspberryPI4)
Lehoi
Posts: 6
Joined: Thu Jan 07, 2016 5:16 pm

Re: Sorting sheets alphabetically minus the first 3

Post by Lehoi »

Hi Karolus

When I try to run Python gives me this error (sorry for spanish):
Image

and I check my JRE and is updated :shock: I don't know what is wrong
Image

Anyway thank you very much for your interest and thanks for the reply.
OpenOffice 4.12 Windows 7
User avatar
karolus
Volunteer
Posts: 1158
Joined: Sat Jul 02, 2011 9:47 am

Re: Sorting sheets alphabetically minus the first 3

Post by karolus »

Hallo

It seems thats an old false Bug from Office-GUI -- Python don't need Java to run in Oo
AOO4, Libreoffice 6.1 on Rasbian OS (on ARM)
Libreoffice 7.4 on Debian 12 (Bookworm) (on RaspberryPI4)
Libreoffice 7.6 flatpak on Debian 12 (Bookworm) (on RaspberryPI4)
UnklDonald418
Volunteer
Posts: 1544
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Sorting sheets alphabetically minus the first 3

Post by UnklDonald418 »

Try this version of your oO macro

Code: Select all

Sub Ordena_Hojas

Dim Names() as string, Names1() as string
Dim i as integer, SavedNum as integer

SavedNum = 3      'number of sheets to exclude from sort

If (Not GlobalScope.BasicLibraries.isLibraryLoaded("Tools")) Then
   GlobalScope.BasicLibraries.LoadLibrary("Tools")
End If
Sheets = ThisComponent.Sheets
Names = Sheets.ElementNames
ReDim Names1(Ubound(Names)-SavedNum)
for i = SavedNum to Ubound(Names)
  Names1(i-SavedNum) = Names(i)
next i    
SortedList = BubbleSortList(Names1) 'BubbleSortList is a macro in the Tools Library
for i = 0 to UBound(SortedList)
   Sheets.MoveByName(SortedList(i), i+SavedNum)
next i
End Sub
Maybe this will work for you
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
Lehoi
Posts: 6
Joined: Thu Jan 07, 2016 5:16 pm

Re: Sorting sheets alphabetically minus the first 3

Post by Lehoi »

Hi UnklDonald418, thanks for the reply

The macro works perfect!, only one question please:
I have a A,B and C sheets (the first 3 fixed sheets) then I have 7 more sheets (Sheet4 to sheet10) (this Sheet10 always go to the first position, any ideas to solve this?, thank you in advance!
OpenOffice 4.12 Windows 7
User avatar
karolus
Volunteer
Posts: 1158
Joined: Sat Jul 02, 2011 9:47 am

Re: Sorting sheets alphabetically minus the first 3

Post by karolus »

Hallo

oh -- some Extras:

Code: Select all

def sort_sheets_except_first_three_and_last_to_front(*_):
    doc = XSCRIPTCONTEXT.getDocument()
    sheets = doc.Sheets
    sortednames = sorted( sheets.ElementNames[3:-1] )
    for n, name in enumerate( sortednames, 3 ):
        sheets.moveByName( name, n )
    sheets.moveByName(sheets.ElementNames[-1],0) 
AOO4, Libreoffice 6.1 on Rasbian OS (on ARM)
Libreoffice 7.4 on Debian 12 (Bookworm) (on RaspberryPI4)
Libreoffice 7.6 flatpak on Debian 12 (Bookworm) (on RaspberryPI4)
Lehoi
Posts: 6
Joined: Thu Jan 07, 2016 5:16 pm

Re: [Solved] Sorting sheets alphabetically minus the first 3

Post by Lehoi »

Hello Karolus

I tried your macros (works very well, thanks) , the last one is very curious ;)

Thanks all for your help!
OpenOffice 4.12 Windows 7
UnklDonald418
Volunteer
Posts: 1544
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: [Solved] Sorting sheets alphabetically minus the first 3

Post by UnklDonald418 »

The problem with Sheet10 has to do with the fact that the BubbleSort function does a string comparison left to right. Since the first character after “Sheet” is 1 it is placed ahead of Sheet4. If you had a Sheet1000 it would also be placed before Sheet4.
If your other Sheets were named in the format “Sheet0x” then the sort function would put Sheet10 in the last position.
Since you marked the thread as [Solved] I won't pursue another solution unless you ask for one.
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
Lehoi
Posts: 6
Joined: Thu Jan 07, 2016 5:16 pm

Re: [Solved] Sorting sheets alphabetically minus the first 3

Post by Lehoi »

Hi UnklDonald418
I do not want to abuse the forum and patience of its members.
My sheets are named alphabetically but some incorporate numbers in their names and I organized them manually.
Please if you have any solutions to this problem I appreciate it.
regards
OpenOffice 4.12 Windows 7
User avatar
RoryOF
Moderator
Posts: 34586
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: [Solved] Sorting sheets alphabetically minus the first 3

Post by RoryOF »

Systematic naming is the best solution. Recollect that ASCII ordering is 0-9 and then A-Z; if numbers are involved running into the tens or higher it is best to use 00, 01... 09, 10 ...
If into the hundreds use 000, 001 etc
Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS
UnklDonald418
Volunteer
Posts: 1544
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: [Solved] Sorting sheets alphabetically minus the first 3

Post by UnklDonald418 »

The issue with Sheet10 isn't limited to Base. It can also be seen if you have alpha-numeric file names in Windows and use “Sort by File Name” in Windows File Explorer.

As RoryOF suggested, when sorting alpha-numeric strings some care is required in assigning the numeric part of the string.

To be on the safe side I usually use at least 2 leading 0's. It's a lot easier to enter them from the beginning rather than possibly having to renumber them all at a later date.

So I would recommend Sheet001,Sheet002 … Sheet010, Sheet011 etc.

I forgot to mention that the macro I posted was designed to allow you to see what was going on by stepping through the macro in the debugger. In retrospect it may have been more confusing than helpful. If you intend to use this macro multiple times then I would suggest you use this version , which is a little more compact.

Code: Select all

Sub Ordena_Hojas

Dim Names() as string, SortedList() as string
Dim i as integer, SavedNum as integer

SavedNum = 3

If (Not GlobalScope.BasicLibraries.isLibraryLoaded("Tools")) Then
   GlobalScope.BasicLibraries.LoadLibrary("Tools")
End If
Sheets = ThisComponent.Sheets
Names = Sheets.ElementNames
for i = 0 to Ubound(Names)- SavedNum
  Names(i) = Names(i+SavedNum)
next i
ReDim Preserve Names(i-1)    
SortedList = BubbleSortList(Names) 'BubbleSortList is a macro in the Tools Library
for i = 0 to UBound(SortedList)
   Sheets.MoveByName(SortedList(i), i+SavedNum)
next i
End Sub
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
Lehoi
Posts: 6
Joined: Thu Jan 07, 2016 5:16 pm

Re: [Solved] Sorting sheets alphabetically minus the first 3

Post by Lehoi »

It is definitely a good idea to rename the sheets using zeros. I will rename all the sheets in this way.
I have learned many useful things with this topic, I am very satisfied with the help received by this forum, thanks to all friends!
OpenOffice 4.12 Windows 7
Arun P. Pai
Posts: 15
Joined: Thu Apr 26, 2018 7:53 am

Re: [Solved] Sorting sheets alphabetically minus the first 3

Post by Arun P. Pai »

Hi to everybody.

I am just a beginner to OOo. Did a lot of VB in Excel. But OOo is slightly different. But that is not a problem.

I just converted an .xlsx file into .ods, as I cannot afford to have MicroSoft licensed version at home.
Without a licensed version Excel is becoming very erratic.
Just learning the ropes of OOo.

I think this macro steps thro all the sheets in the work book, but not very sure.
Now wondering how to select the cell "A1" in each sheet.
Will scout around to see if there already a macro to select a particular cell in each cell.

Must read the basics, I have got this book downloaded by Andrew Pitonyak "Macros Explained", just stared to read.

Thanks for the code, it works but I must understand the steps.
Never knew "Bubble Sort" can be called to sort, this is new to me.
In VB Excel I wrote the sort code with help from some of the books written by Mr. John Walkenbach.

Thank you.
Arun
OS - Windows 8.1
Open Office 4.1.5
UnklDonald418
Volunteer
Posts: 1544
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: [Solved] Sorting sheets alphabetically minus the first 3

Post by UnklDonald418 »

Welcome to the OpenOffice Forum.

Calc spreadsheet documents contain sheets. When you create a new Calc spreadsheet it has 3 sheets whose names appear on the tabs along the bottom of the document. Internally Calc uses index numbers rather than names to refer to the sheets so originally Sheet1 is assigned an index of 0, Sheet2 is 1 and Sheet3 is 2. When more sheets are added they get the next available index number. Sheets also have names (Sheet1 etc.) and can be renamed, but they retain the index they were assigned when they were created. Each time the spreadsheet is opened the sheets are displayed in index order. Sometimes there can be quite a few sheets that have been renamed and it can be a little difficult to locate them by those names because they can appear in somewhat random order. The macro above sorts the sheets by name and reassigns the index numbers so that they will appear in alphabetical order.

The BubbleSortList macro code is part of the Tools macro library supplied with Base.
You can look at the code by selecting Tools>Macros>Organize Macros>OpenOffice Basic to open the OpenOffice Basic Macros dialog (or use the shortcut key combination <Alt>F11).
In the left panel of the dialog navigate to OpenOffice Macros>Tools>Strings. BubbleSortList is towards the bottom of the list.
The Bubble sort (sometimes referred to Exchange Selection sorting) algorithm is easy to understand and relatively easy to implement. It works ok on short lists like the sheet names on a spreadsheet, but it is the least efficient method of sorting so you wouldn't want to use it on a list with thousands of records.

Cells too have indexes for so for instance

Code: Select all

oCell = oSheet.GetCellbyPosition( 0, 0 )
loads a cell object into the variable oCell found in cell A1 (0,0) of a previously obtained sheet object oSheet.
To actually see all the Properties and available Methods in the oCell object an object inspection tool is indispensable.
[Tutorial] Introduction into object inspection with MRI

For more information see Chapter 15 of Andrew Pitonyak's book which is devoted to spreadsheets. He also has another document "Useful Macro Information" that contains a number of examples of Calc macros.

Programmed macros should be a last resort. Before you dive too deeply into macro programming, your time may be better spent leaning to use the built in spreadsheet options and functions because you can often accomplish your task without the brain damage of writing a macro.
Calc also has a Macro Recorder (Tools>Macros>Record Macro) which can sometimes accomplish the task without programming.

As a point of etiquette, rather than tacking a question onto a thread that has been marked [Solved], it would be better to start a new thread with your question.
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
Arun P. Pai
Posts: 15
Joined: Thu Apr 26, 2018 7:53 am

Re: [Solved] Sorting sheets alphabetically minus the first 3

Post by Arun P. Pai »

Dear UnklDonald418,

Regret posting my question in a Solved Thread, will not repeat in future.

Will check out on Chapter 15 of Andrew Pitonyak's Book.

Thank you very much for your reply.

Regards
Arun
OS - Windows 8.1
Open Office 4.1.5
Post Reply