[Calc] Set column widths and row heights

[inventorgeorge]

I made sure the JRE is installed.
I set the cursor at 1,1 position.
I select "record macro."
I then do a series of mouse clicks to set various column widths and row heights.
I select "stop macro."
I then "undo" all of steps to set all rows and columns back to default values.
I set the cursor to position 1,1 and run the macro I just recorded.

I get the first action I recorded, changing a column width, but all other actions do not happen. I've tried everything I can think of, but I cannot get a macro recorded and then run correctly. Can someone tell me what steps I am missing/ doing wrong?

Edit: Changed subject, was Macros do not run correctly  Make your post understandable by others  -- MrProgrammer, forum moderator

[RoryOF]

The macro recorder is not 100% effective; the best course for macros is to refer to the works of Andrew Pitonyak on the subject of Open-/Libre-Office macro writing. These may be downloaded from
www.pitonyak.org

[inventorgeorge]

So it's not something I am doing wrong, It is OO that is failing! Sort of makes me feel less like an idiot, but doesn't solve my problem. Thank you for the link, but I really do not want to become a coding expert, I just need to do a lot of repetitive steps automatically.
Is there any reasonable work-around for OO's failings?

[Zizi64]

Is there any reasonable work-around for OO's failings?

You can try:

• Use the Menu items during the recording macros. Mouse dragging of column width will not be recorded. Many of macros will work with this way (but not all of them)

• Hire somebody to WRITE your macros for you

• Learn to write the macros instead recording them

[JeJe]

Format menu/row/height records.

[JeJe]

Edit menu/undo gives me this:

Code: Select all

sub Undo
rem ----------------------------------------------------------------------
rem define variables
dim document   as object
dim dispatcher as object
rem ----------------------------------------------------------------------
rem get access to the document
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

rem ----------------------------------------------------------------------
rem dispatcher.executeDispatch(document, ".uno:Undo", "", 0, Array())
end sub

The code is there and it will work but in this case the recorder has added "rem" in front in this line which performs the undo
action.

Code: Select all

rem dispatcher.executeDispatch(document, ".uno:Undo", "", 0, Array())

Rem makes the line a comment - the recorder sometimes adds this. You need to remove the rem yourself by editing the code and it will run

Code: Select all

sub Undo
rem ----------------------------------------------------------------------
rem define variables
dim document   as object
dim dispatcher as object
rem ----------------------------------------------------------------------
rem get access to the document
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:Undo", "", 0, Array())
end sub

[inventorgeorge]

I am attaching the Calc file I am working with. I don't know if the macro is included in this file, so I also include a txt file of the macro itself.
This is really starting to drive me up a wall. I am not a programmer, however I do have some BASIC background. this macro does not look like anything I have encountered in the past as BASIC code. I'm looking now for a text on this language itself, to see if I can learn enough to be able to modify the code. I've tried making some changes, but they do not do anything that gets me closer to a working macro.
When I run this macro, it executes the first row height change I recorded, but it does it not where I wanted it, but modifies the row the cursor happens to be at. It stops there, never doing anything. As recorded, I changed 5 different row heights, one at a time.

[Zizi64]

There is not any information nor statement about "which rows you want to edit"...
(This information has not been recorded.)

Here is your modified sample file with the embedded macro:

Macro Test2.ods

(20.85 KiB) Downloaded 459 times

And here is your "recorded" macro code:

Code: Select all

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

rem Sub Main

rem End Sub


sub SetSpace1
rem ----------------------------------------------------------------------
rem define variables
dim document   as object
dim dispatcher as object
rem ----------------------------------------------------------------------
rem get access to the document
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

rem ----------------------------------------------------------------------
dim args1(0) as new com.sun.star.beans.PropertyValue
args1(0).Name = "RowHeight"
args1(0).Value = 1016

dispatcher.executeDispatch(document, ".uno:RowHeight", "", 0, args1())

rem ----------------------------------------------------------------------
dim args2(0) as new com.sun.star.beans.PropertyValue
args2(0).Name = "RowHeight"
args2(0).Value = 1016

dispatcher.executeDispatch(document, ".uno:RowHeight", "", 0, args2())

rem ----------------------------------------------------------------------
dim args3(0) as new com.sun.star.beans.PropertyValue
args3(0).Name = "RowHeight"
args3(0).Value = 1016

dispatcher.executeDispatch(document, ".uno:RowHeight", "", 0, args3())

rem ----------------------------------------------------------------------
dim args4(0) as new com.sun.star.beans.PropertyValue
args4(0).Name = "RowHeight"
args4(0).Value = 1016

dispatcher.executeDispatch(document, ".uno:RowHeight", "", 0, args4())

rem ----------------------------------------------------------------------
dim args5(0) as new com.sun.star.beans.PropertyValue
args5(0).Name = "RowHeight"
args5(0).Value = 1016

dispatcher.executeDispatch(document, ".uno:RowHeight", "", 0, args5())

rem ----------------------------------------------------------------------
dim args6(0) as new com.sun.star.beans.PropertyValue
args6(0).Name = "RowHeight"
args6(0).Value = 1016

dispatcher.executeDispatch(document, ".uno:RowHeight", "", 0, args6())


end sub

[inventorgeorge]

I should mention:
I am importing the data from an excel [ugh] file. The data changes with each sheet, but the positioning of the data must be precisely the same. I am printing on a pre-printed sheet. When I import the data, the rows and columns get reset to their default values, hence the need for a macro to set everything.

[inventorgeorge]

I opened your Macro Test2 file. I do not see anything different, either in result or in the macro text. I am getting an error, saying that the format is not supported. I am using version 4.1.14, so not sure what is going on.

[Zizi64]

I opened your Macro Test2 file. I do not see anything different, either in result or in the macro text. I am getting an error, saying that the format is not supported. I am using version 4.1.14, so not sure what is going on.

Sorry, I am using the LibreOffice 7.5.8 version. That saves into the ODF 1.3 Extended version. The AOO uses the ODF 1.2 Extended file format. I just embedded the macro code, but no launvhed it. And it can not modify anything, because there are missing informations about the rows position.


I suppose it - based on your "recorded macro code" that you want to modify the height of six rows (or six set of rows). But the sample .ods file has about 36 data rows (minus the empty rows). Which ones you want to modify???

[inventorgeorge]

I also copied your txt file directly into my macro, and ran that. I get the same thing - I executes one command at the position of the cursor and stops.
You mention that there is no positioning information in the macro. I agree. I do not understand why that is missing, and I do not know how to manually add that into the code. honestly, the code makes no sense to me. I do not see anything that indicates WHAT is to be done or WHERE it is to be done. It makes sense to me that I get no results, but unfortunately I do not know WHY this is happening, or HOW to manually fix it.

[inventorgeorge]

Actually, I will need to modify every row in the range where there is data, as well as changing each column in the same range. If I could see the code for changing a couple rows, and a couple columns, I should be able to copy/paste to cover the range I need.

You are VERY KIND to be helping me, I appreciate it!

[robleyd]

A quick web search returned a couple of results that might help you -
https://www.openoffice.org/documentatio ... Macros.pdf and
https://wiki.openoffice.org/wiki/MacroRecorder

I haven't looked closely at these or any of the other results.

[Zizi64]

Here is a RECORDED macro code (using the keyboard, mouse and the Menu) for modifying the height of the first two non empty rows. (The non empty status is determined by my eyes)

The activity what was recorded:

• Jump to Cell A1 by mouse click,

• GoDown to the first non empty row by the Down arrow key

• Modify the format of the row by mouse in the Main menu

• GoDown by the Arrow key

• Modify the format of the row by mouse in the Main menu

• GoDown by the Arrow key

• GoDown by the Arrow key (the cursor is in the next non empty row)

Now you can organise the recorded code from the third activity to the last activity into a For...next cycle to modify the next non empty rows.

The recorded code:

Code: Select all

sub SetSpace2
rem ----------------------------------------------------------------------
rem define variables
dim document   as object
dim dispatcher as object
rem ----------------------------------------------------------------------
rem get access to the document
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

rem ----------------------------------------------------------------------
dim args1(0) as new com.sun.star.beans.PropertyValue
args1(0).Name = "ToPoint"
args1(0).Value = "$A$1"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args1())

rem ----------------------------------------------------------------------
dim args2(1) as new com.sun.star.beans.PropertyValue
args2(0).Name = "By"
args2(0).Value = 1
args2(1).Name = "Sel"
args2(1).Value = false

dispatcher.executeDispatch(document, ".uno:GoDown", "", 0, args2())

rem ----------------------------------------------------------------------
dim args3(0) as new com.sun.star.beans.PropertyValue
args3(0).Name = "RowHeight"
args3(0).Value = 1016

dispatcher.executeDispatch(document, ".uno:RowHeight", "", 0, args3())

rem ----------------------------------------------------------------------
dim args4(1) as new com.sun.star.beans.PropertyValue
args4(0).Name = "By"
args4(0).Value = 1
args4(1).Name = "Sel"
args4(1).Value = false

dispatcher.executeDispatch(document, ".uno:GoDown", "", 0, args4())

rem ----------------------------------------------------------------------
dim args5(0) as new com.sun.star.beans.PropertyValue
args5(0).Name = "RowHeight"
args5(0).Value = 1016

dispatcher.executeDispatch(document, ".uno:RowHeight", "", 0, args5())

rem ----------------------------------------------------------------------
dim args6(1) as new com.sun.star.beans.PropertyValue
args6(0).Name = "By"
args6(0).Value = 1
args6(1).Name = "Sel"
args6(1).Value = false

dispatcher.executeDispatch(document, ".uno:GoDown", "", 0, args6())

rem ----------------------------------------------------------------------
dim args7(1) as new com.sun.star.beans.PropertyValue
args7(0).Name = "By"
args7(0).Value = 1
args7(1).Name = "Sel"
args7(1).Value = false

dispatcher.executeDispatch(document, ".uno:GoDown", "", 0, args7())


end sub

[inventorgeorge]

It will take me a bit, I will now sit down and study what you have presented, line by line. Again, thank you VERY much for your help!

[Zizi64]

And here is a macro code (based on the API) what give you same result result:

Code: Select all

Sub SetSpaceAPI

 aRowNumbersToModify = Array(1,2,4,5,7,8,10,11,13,14,15,17,19,20,22,23,25,26,28,29,31,32,34,35) Rem the numbering of the rows is zero based in the Basic and in the API

	oDoc = ThisComponent
	oSheet = oDoc.Sheets.Getbyname("Test Sheet")
	For i = 0 to 23 REM the numbering of the element of an array is zero based too.
		oRow = oSheet.Rows.GetbyIndex(aRowNumbersToModify(i))
		oRow.Height = 1016
	next i
End sub

[inventorgeorge]

I have been reading and testing, trying to get a relatively simple macro to work. My brain simply does not work like a programmer! zizi64 was kind enough to send me a sample code, but I just do not know enough about OO Basic to expand it to do what I need. I am throwing in the towel. Is there someone out in this forum whom I could hire to write the macro for me?
The macro is relatively simple; I need it to set the height of the first 40 rows on a worksheet. I have to be able to input a value for each row. Then It must set the width for each column from A to M. Again, I need to input a value for each column. I will rarely be changing these values, so I am not concerned about anything fancy, no GUI, just the ability to edit values into the macro [that much I can do]. That is it, just setting row heights and column widths within the range specified.
I do not know if it is within the rules to solicit someone for pay here, but if not, the I hope the moderator will just remove this, and I will look elsewhere for help.

[Zizi64]

If you can not write or modify (or use) a macro, (and you want not learn it), then it is better to use a prepared Template file for this task. You can set the desired properties (row heights, column widths, and others) in a new, empty Spreadsheet file, and save it as a Template.
Always use that Template for this task. Just open it, and paste the data into it as UNFORMATTED TEXT. Then the preformatted content of the foreign source will not modify the formatting properties of the Template.

[JeJe]

You should take the template suggestion if possible.
If that's not going to suffice, here's the first step with the macro recorder, selecting the first 40 rows and setting the height.

you just need to change 2463 in the line "args2(0).Value = 2464" to the height value you want

Code: Select all

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


sub changeRowheights
rem ----------------------------------------------------------------------
rem define variables
dim document   as object
dim dispatcher as object
rem ----------------------------------------------------------------------
rem get access to the document
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

rem ----------------------------------------------------------------------
dim args1(0) as new com.sun.star.beans.PropertyValue
args1(0).Name = "ToPoint"
args1(0).Value = "$A$1:$A$40"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args1())

rem ----------------------------------------------------------------------
dim args2(0) as new com.sun.star.beans.PropertyValue
args2(0).Name = "RowHeight"
args2(0).Value = 2464

dispatcher.executeDispatch(document, ".uno:RowHeight", "", 0, args2())


end sub