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

Creating a macro - Writing a Script - Using the API

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

Postby Robitron » Tue Dec 30, 2014 3:55 pm

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.5 on Windows 10

What am I doing here? What are you doing here? Wait, where are we?
Robitron
 
Posts: 91
Joined: Thu Nov 15, 2012 5:27 pm

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

Postby FJCC » Tue Dec 30, 2014 4:14 pm

The Sheets container has a Count property
Code: Select all   Expand viewCollapse view
oSheets = ThisComponent.Sheets
NumSheets = oSheets.Count

The index of the last sheet would be NumSheets - 1.
Windows 10 and Linux Mint, since 2017
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 7221
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

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

Postby Robitron » Tue Dec 30, 2014 4:21 pm

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

What am I doing here? What are you doing here? Wait, where are we?
Robitron
 
Posts: 91
Joined: Thu Nov 15, 2012 5:27 pm

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

Postby UncleCrust » Fri Mar 02, 2018 12:55 am

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
UncleCrust
 
Posts: 1
Joined: Fri Mar 02, 2018 12:42 am

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

Postby Lupp » Fri Mar 02, 2018 10:10 pm

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 6.2 and older versions, PortableOpenOffice 4.1.5 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 2522
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

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

Postby Villeroy » Fri Mar 02, 2018 10:28 pm

Code: Select all   Expand viewCollapse view
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, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26968
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to Macros and UNO API

Who is online

Users browsing this forum: No registered users and 2 guests