[Solved] Run macro across all worksheets within a workbook

Creating a macro - Writing a Script - Using the API

[Solved] Run macro across all worksheets within a workbook

Postby Kalysta » Sun Oct 27, 2019 1:49 pm

Hello,

I am trying to get a macro to run on any selected worksheet within my workbook.

FJCC kindly provided me with a routine to solve my last issue (Buttons.ods) but it only works on sheet1 specified in the macro.
I need load a new worksheet every week and have the macro work regardless of the sheet name.

Thanks in advance - much appreciated.

I have attached the basic working file.

Windows 10.
OO 4.1.7
Attachments
20200101_Trent.ods
(40.69 KiB) Downloaded 9 times
Last edited by Kalysta on Sat Nov 02, 2019 2:50 pm, edited 1 time in total.
Windows 10
Open Office 4.1.7
Kalysta
 
Posts: 4
Joined: Fri Oct 18, 2019 2:03 pm

Re: Using a macro across all worksheets within a workbook.

Postby JeJe » Sun Oct 27, 2019 2:37 pm

Openoffice 4.1.2
Windows 8
JeJe
Volunteer
 
Posts: 624
Joined: Wed Mar 09, 2016 2:40 pm

Re: Using a macro across all worksheets within a workbook.

Postby DiGro » Sun Oct 27, 2019 3:29 pm

In FJCC's solution Buttons.ods the macro is hardcoded to use Sheet1

Let it use the ActiveSheet instead

Code: Select all   Expand viewCollapse view
Sub Button(oEv)
Lab = oEv.Source.Model.Label
Col = oEv.Source.Model.BackgroundColor

CurrSelec = ThisComponent.CurrentSelection(0)

IF Not CurrSelec.supportsService("com.sun.star.sheet.SheetCell")Then       
  msgbox("Inappropriate Action")
  exit sub
End If

IF CurrSelec.CellAddress.Column <> 1 Then       
  msgbox("Inappropriate Action")
  exit sub
End If

rem oSheet = ThisComponent.Sheets.getByName("Sheet1")    'originally coded by FJCC

oSheet = ThisComponent.getCurrentController.getActiveSheet()

CurrSelec.String = Lab
oRow = oSheet.getCellrangeByPosition(1, CurrSelec.CellAddress.Row, 17, CurrSelec.CellAddress.Row)
oRow.CellBackColor = Col
oNextCell = oSheet.getCellByPosition(1, CurrSelec.CellAddress.Row + 1)
ThisComponent.CurrentController.select(oNextCell)
End Sub
Attachments
Buttons_activesheet.ods
(12.99 KiB) Downloaded 9 times
____________
DiGro

AOO 4.1.7 on Windows 10. Scanned with ZIGGO Safe Online (F-Secure)
User avatar
DiGro
 
Posts: 82
Joined: Mon Oct 08, 2007 1:31 am
Location: Hoorn NH, The Netherlands

Re: Using a macro across all worksheets within a workbook.

Postby Lupp » Sun Oct 27, 2019 10:56 pm

I would dissuade from the one-spreadsheet-per-week (OSSPW) design.

Why not collect and maintain the raw data with low formatting, and create presentations and reports for formatted prettyprint containing one week (or one month or any range of dates) from a respective selection in specialised sheets?

Ideally you then need one data sheet and one sheet for presentation, charting/visualising and whatever... (probably a few sheets optimised under different aspects). This is partly how a database design would do it, and if there are additional requirements a database solution should anyway be preferred.

In addition you may create reports not youst including a number of subsequent days, but also something like "all the sundays in my given range" from the same single data sheet.

In many cases the needed selection will be supported by the filtering tools AOO and LibO come with. If needed very often a bit of programming can rather easily enhance the filtering and reduce manual handling. One day, if your data scaled up too much, you also can easily export them to a real database.

With OSSPW you never can avoid this fundamental problem:
If need arises to change parts of the functionality or the arrangements of cells containing some of the data, you will have lots of trouble to do that for all the weekly sheets. Even if you are ready to expend the time, the reworking will need lots of manual interaction, and will be very error-prone therefore. What if you need to add a source, to exchange an old one for a new one, or just to rectify the spelling of one?
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: 2564
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Using a macro across all worksheets within a workbook.

Postby Kalysta » Tue Oct 29, 2019 4:43 pm

I'm indebted to you who replied so promptly. I am working another project at the moment... (Grandkid's Christmas)

My initial view of the solution is that it looks good for my application. I'll apply it tomorrow and close the thread.

Thanks again.

K
Windows 10
Open Office 4.1.7
Kalysta
 
Posts: 4
Joined: Fri Oct 18, 2019 2:03 pm


Return to Macros and UNO API

Who is online

Users browsing this forum: No registered users and 8 guests