[Solved] Selecting multiple rows in a macro

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
modern man
Posts: 9
Joined: Sat Dec 24, 2016 8:01 pm

[Solved] Selecting multiple rows in a macro

Post by modern man »

Hi there,

I'm trying to "write a macro" that will allow me to hide several rows of a spreadsheet, based on which value is selected in a given cell that contains a selection list.
Actually I've copy-and-pasted my way up to hiding a single row by pressing my macrobutton. One (me) might expect that from here it's not very to toggling multiple rows, but... I am struggling with all the API reference stuff and decided to place my lame question here instead.

Here's what I've got so far:

Code: Select all

Sub Main

oSheets = ThisComponent.getSheets() ' this seems to be the reference to the open spreadsheet document
oSheet1 = oSheets.getByName("zeiten") ' that is my actual sheet
oRows = oSheet1.getRows ' these are, afaics all rows in that sheet
oChoice = oSheet1.getCellRangeByName("G1").String ' this is the cell that defines which rows shall be visible or not
oRow = oRows.getByIndex(3) ' this is row number 4. Here's the point I'm struggling

If  oChoice = "Berlin" Then
      oRow.isVisible = False
   Else
      oRows.isVisible = True
   End If
End Sub
So, questions:

- How do I fill "oRow" with multiple values. I tried comma separated, didn't work. Would I need to define "oRow" as an array or a list beforehand?
- what's it with the lowercase characters prefixed to the variable names. I come across this in many examples and copy-pasted it right away, but is this something necessary or just a community convention?

Cheers,

Stephan

##

solved:
So. I admit I find it hard to google my way to effective results. Nonetheless, right after sending my post I skimmed some older posts here and came up with a solution that works. I am absolutely sure that it's not very elegant in terms of programming logic and so on. But just for the record (maybe someone will face the same lame problem that I did).

I initialized a variable as an array, put the lines in absolute numbers into that array, and have a for loop go over the content of that array later -> works.
Here's how it looks like:

Code: Select all

REM  *****  BASIC  *****

Sub Main
berl() = Array(4,6,10,11,12,13,15,21,23,24)
muni() = Array(5,7,8,9,14,16,17,18,19,20,22,25,26,27)

oSheets = ThisComponent.getSheets()
oSheet1 = oSheets.getByName("zeiten")
oRows = oSheet1.getRows
oChoice = oSheet1.getCellRangeByName("G1").String

oRow = oRows.getByIndex(3)



If  oChoice = "Munich" Then
	for each n in berl()
		oRow = oRows.getByIndex(n)
      	oRow.isVisible = False
	next

	for each n in muni()
		oRow = oRows.getByIndex(n)
      	oRow.isVisible = True
	next
	
ElseIf oChoice = "Berlin" Then
	for each n in muni()
		oRow = oRows.getByIndex(n)
      	oRow.isVisible = False
	next

	for each n in berl()
		oRow = oRows.getByIndex(n)
      	oRow.isVisible = True
	next



Else
   	for each n in berl()
		oRow = oRows.getByIndex(n)
      	oRow.isVisible = True
	next
	for each n in muni()
		oRow = oRows.getByIndex(n)
      	oRow.isVisible = True
	next
End If


End Sub
I'm pretty sure I'll return with other equally lame questions soon (right now I'm trying to figure out how to fill the arrays automatically by determining rows on cell content... well).

Nonetheless, if one spots any severe mistakes or has hints, feel free to still answer. I'll be happy for everything.
Cheers

Stephan
Last edited by modern man on Sat Dec 24, 2016 9:00 pm, edited 1 time in total.
Libreoffice 5.2.3.3 on Gentoo Linux
FJCC
Moderator
Posts: 9248
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Selecting multiple rows in a macro

Post by FJCC »

To affect multiple rows you have to loop through them as shown below.

Code: Select all

oSheets = ThisComponent.getSheets() ' this seems to be the reference to the open spreadsheet document
oSheet1 = oSheets.getByName("Sheet1") ' that is my actual sheet
oRows = oSheet1.getRows ' these are, afaics all rows in that sheet
oChoice = oSheet1.getCellRangeByName("G1").String ' this is the cell that defines which rows shall be visible or not
aRowsToHide = Array(3,4,5,6)
'oRow = oRows.getByIndex(3) ' this is row number 4. Here's the point I'm struggling

If  oChoice = "Berlin" Then
      'oRow.isVisible = False
      for each RowIndex in aRowsToHide
      	oRow = oRows.getByIndex(RowIndex)
      	oRow.isVisible = False
      next RowIndex
Else
'oRows.isVisible = True
  for each RowIndex in aRowsToHide
   	oRow = oRows.getByIndex(RowIndex)
   	oRow.isVisible = True
  next RowIndex
End If
The lower case prefixes on variable names is just a convention. o = object, a= array. It can help you remember what a variable refers to.
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.
modern man
Posts: 9
Joined: Sat Dec 24, 2016 8:01 pm

Re: [solved] Selecting multiple rows in a macro

Post by modern man »

Hi FJCC, thanks for your fast reply! Seems I wasn't that far off :-).
Cheers and merry christmas to you (christmas emoticon here)

Stephan
Libreoffice 5.2.3.3 on Gentoo Linux
Post Reply