Page 1 of 1

[Solved] Sorting sheets alphabetically minus the first 3

PostPosted: Thu Jan 07, 2016 11:04 pm
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   Expand viewCollapse view
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   Expand viewCollapse view
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!

Re: Sorting sheets alphabetically minus the first 3

PostPosted: Fri Jan 08, 2016 12:56 am
by karolus
Hallo

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

Code: Select all   Expand viewCollapse view
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

Re: Sorting sheets alphabetically minus the first 3

PostPosted: Fri Jan 08, 2016 1:21 am
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.

Re: Sorting sheets alphabetically minus the first 3

PostPosted: Fri Jan 08, 2016 8:56 am
by karolus
Hallo

It seems thats an old false Bug from Office-GUI -- Python don't need Java to run in Oo

Re: Sorting sheets alphabetically minus the first 3

PostPosted: Sat Jan 09, 2016 12:20 am
by UnklDonald418
Try this version of your oO macro
Code: Select all   Expand viewCollapse view
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

Re: Sorting sheets alphabetically minus the first 3

PostPosted: Sat Jan 09, 2016 12:29 am
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!

Re: Sorting sheets alphabetically minus the first 3

PostPosted: Sat Jan 09, 2016 1:44 am
by karolus
Hallo

oh -- some Extras:

Code: Select all   Expand viewCollapse view
def sort_sheets_except_first_three_and_last_to_front(*_):
    
doc XSCRIPTCONTEXT.getDocument()
    
sheets doc.Sheets
    sortednames 
sortedsheets.ElementNames[3:-1] )
    for 
nname in enumeratesortednames):
        
sheets.moveByNamename)
    
sheets.moveByName(sheets.ElementNames[-1],0

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

PostPosted: Sat Jan 09, 2016 2:09 am
by Lehoi
Hello Karolus

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

Thanks all for your help!

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

PostPosted: Sat Jan 09, 2016 6:58 am
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.

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

PostPosted: Sat Jan 09, 2016 11:31 am
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

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

PostPosted: Sat Jan 09, 2016 11:47 am
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

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

PostPosted: Sat Jan 09, 2016 6:01 pm
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   Expand viewCollapse view
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

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

PostPosted: Sat Jan 09, 2016 8:46 pm
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!

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

PostPosted: Sat Jun 16, 2018 4:22 pm
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.

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

PostPosted: Sun Jun 17, 2018 12:04 am
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   Expand viewCollapse view
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.

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

PostPosted: Sun Jun 17, 2018 2:28 pm
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