[Solved] Macro For Row Height

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
alkaline5
Posts: 9
Joined: Sun Nov 13, 2016 11:02 am

[Solved] Macro For Row Height

Post by alkaline5 »

I need a macro that will change row height based on character counts in cells. Say cell b
3 and various other cells in column b have less than 20 characters in them, then they would be formated to .2 inches high.
Last edited by alkaline5 on Tue Nov 15, 2016 2:59 am, edited 1 time in total.
LibreOffice 4.2 on Linux
FJCC
Moderator
Posts: 9248
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Macro For Row Height

Post by FJCC »

Here is a simplistic macro that sets the height of row 2 to 0.2 inches if B2 has fewer than 20 characters and to 0.4 inches otherwise.

Code: Select all

oSheets = ThisComponent.getSheets()
oSheet = oSheets.getByName("Sheet1")
oB2 = oSheet.getCellrangeByName("B2")
StrLeng = LEN(oB2.String)
oRow = oB2.getRows()
If StrLeng < 20 Then
	oRow.Height = 0.2 * 25.4 * 100 'measured in 1/100 mm
Else
	oRow.Height = 0.4 * 25.4 * 100
End if
Do you really need a macro? You can set the cell text to wrap automatically, which I find to be more useful.
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.
alkaline5
Posts: 9
Joined: Sun Nov 13, 2016 11:02 am

Re: Macro For Row Height

Post by alkaline5 »

Well, thanks for what looks like a partial answer. But the situation would be more involved I think, with the macro finding not one row but many amongst about 5000 rows.
LibreOffice 4.2 on Linux
FJCC
Moderator
Posts: 9248
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Macro For Row Height

Post by FJCC »

Of course it is a partial answer, you have given a very sketchy description of what needs to be done. For the cells to affect, your original posts just mentions "cell b3 and various other cells in column b" and the only action mentioned is that < 20 characters produces a row height of 0.2". I invented setting the row height to 0.4 just to do something should the number of characters be >= 20. You need to define which cells need to be inspected and what the relationship should be between the number of characters and the row height.

I guessed that you intended to look at a few specific cells, so I used the getCellRangeByName() method but now I suspect getCellByPosition(nColumn, nRow) would be better, if the range of cells is continuous. Row and columns are indexed from 0, so column B has an index of 1. All you have to do is substitute getCellByPosition() for getCellrangeByName(), code the height/character length relationship and use a loop to step through the cells.

Again, why won't setting the cells to wrap text automatically work?
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.
alkaline5
Posts: 9
Joined: Sun Nov 13, 2016 11:02 am

Re: Macro For Row Height

Post by alkaline5 »

Well, in VBA, I think it looks something like this:

Code: Select all

Sub RowHeight ()
     For i = 1 To 5000 Step 1
          If Len(Cells(i, 1)) > 20 Then
          Rows(i).RowHeight = 20
          End If
     Next if
End Sub
LibreOffice 4.2 on Linux
FJCC
Moderator
Posts: 9248
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Macro For Row Height

Post by FJCC »

And in OpenOffice your VBA code would be something like

Code: Select all

oSheets = ThisComponent.getSheets()
oSheet = oSheets.getByName("Sheet1")
For i = 0 to 4999  'row 1 to row 5000
    oCell = oSheet.getCellByPosition(1,i)
    StrLeng = LEN(oCell.String)
    If StrLeng < 20 Then
       oRow = oCell.getRows()
       oRow.Height = 0.2 * 25.4 * 100 'measured in 1/100 mm
    End if
next i
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.
alkaline5
Posts: 9
Joined: Sun Nov 13, 2016 11:02 am

Re: Macro For Row Height

Post by alkaline5 »

I gave that a try, but seemed to get a syntax error where the "for" statement begins.
LibreOffice 4.2 on Linux
FJCC
Moderator
Posts: 9248
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Macro For Row Height

Post by FJCC »

It runs for me. What exactly is the error?
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.
alkaline5
Posts: 9
Joined: Sun Nov 13, 2016 11:02 am

Re: Macro For Row Height

Post by alkaline5 »

I tried again and it did work. So, thank you very much for your time and effort.
LibreOffice 4.2 on Linux
Post Reply