Apache OpenOffice Community Forum

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 Indeed I test this OO macro and sort alphabetically ok, but I don´t know how exclude the first 3 sheets. Please can somebody help me to solve this problem? I tried many things but none works (I am a total newby ).
Thanks!

Hallo

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

Hi Karolus

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


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


Anyway thank you very much for your interest and thanks for the reply.

Hallo

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

Try this version of your oO macro Maybe this will work for you

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!

Hallo

oh -- some Extras:

Hello Karolus

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

Thanks all for your help!

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.

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

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

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.

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!

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.

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 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.

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