[Solved] Finding the last sheet number with a macro for Calc

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
Robitron
Posts: 105
Joined: Thu Nov 15, 2012 5:27 pm

[Solved] Finding the last sheet number with a macro for Calc

Post by Robitron »

I'm writing macros that need to find all sheets and if each sheet is not of a specified name (provided by each macro), then it will automatically hide it. The purpose of this is to hide all irrelevant sheets for the tasks I'm doing at a given time. For instance, if I am working on the expense journal sheet, then the other sheets, not related to expenses would be hidden and those sheets that are related to expenses would be shown.

However, I can not figure out from the basic programmer's guide how to find the number of the last sheet.

This is a macro for managing a chicken farm with mulltiple flocks. One macro adds new sheets everytime a new flock is added. Therefore, the number of sheets is always changing, so manually counting the sheets to get the total number is not the answer.

I'm sure there is a very simple answer but I just can not find it. :crazy:
Last edited by Robitron on Tue Dec 30, 2014 5:04 pm, edited 1 time in total.
OpenOffice 4.1.12 on Windows 10

If I had to, I'd put Tabasco on everything!
FJCC
Moderator
Posts: 9248
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Finding the last sheet number with a macro for calc

Post by FJCC »

The Sheets container has a Count property

Code: Select all

oSheets = ThisComponent.Sheets
NumSheets = oSheets.Count
The index of the last sheet would be NumSheets - 1.
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
Robitron
Posts: 105
Joined: Thu Nov 15, 2012 5:27 pm

Re: Finding the last sheet number with a macro for calc :Sal

Post by Robitron »

Sweet! I knew it was something simple that I was overlooking! Thanks so much!!!
OpenOffice 4.1.12 on Windows 10

If I had to, I'd put Tabasco on everything!
UncleCrust
Posts: 1
Joined: Fri Mar 02, 2018 12:42 am

Re: [Solved] Finding the last sheet number with a macro for

Post by UncleCrust »

Sorry. I've never made/used a macro before. I've read about recording macros, but how do you stick code into a macro? How does one put a macro into the formula for a cell? I wanted to use the number of sheets value to do calculations from collected data from various sheets.
LibreOffice 5.4.1.2 on Windows 10
User avatar
Lupp
Volunteer
Posts: 3542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: [Solved] Finding the last sheet number with a macro for

Post by Lupp »

UncleCrust wrote:How does one put a macro into the formula for a cell? I wanted to use the number of sheets value to do calculations from collected data from various sheets.
To calculate something depending on values contained or precalculated in cells of one or more sheets the appropriate means is to reference the respectiv cells in formulas either one by one, by ranges, or even by cuboids depending on the functions to apply. This is basically the way to use spreadsheets. For what resons do you think to need "macros"?
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] Finding the last sheet number with a macro for

Post by Villeroy »

Code: Select all

Function SheetNames()
  SheetNames = ThisComponent.Sheets.getElementNames()
End Function
[Tutorial] How to install a code snippet
Save this in some module in "My Macros", library "Standard".

Now =SHEETNAMES() (entered as an array formula) returns a list of this document's sheet names.
=INDEX(SHEETNAMES();SHEET()) returns this sheet's name.
=COUNTA(SHEETNAMES()) returns the count of sheets.
=ISNUMBER(MATCH(A1;SHEETNAMES();0)) returns if a sheet name already exists.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Post Reply