[Solved] Column Width macro

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
JayReeve
Posts: 8
Joined: Sun Jun 14, 2009 7:21 am

[Solved] Column Width macro

Post by JayReeve »

Hi

I'm trying to implement a macro for setting columns to specified widths in Calc. Here's what I've done:

Code: Select all

sub SetColWidth( col as integer, nchs as single, oSheet as object )
	Static oColumn As Object

	dim w as single
	w      = nchs * 2540
	' get column
	oColumn = oSheet.getColumns.getByIndex( col )
	' column width (in 100ths of mm)
	oColumn.setPropertyValue("Width", w)
end sub

Sub FormatColumns
	static oSheet as object, oDoc As Object
	oDoc   = ThisComponent
	oSheet = ThisComponent.getCurrentController.getActiveSheet	
	SetColWidth( 0, 1.5, oSheet )
	SetColWidth( 1, .33, oSheet )
	SetColWidth( 2, .33, oSheet )
	SetColWidth( 3, .75, oSheet )
	SetColWidth( 4, .75, oSheet )
	SetColWidth( 5, .75, oSheet )
	SetColWidth( 6, 2.75, oSheet )
End Sub
When I run the sub from the Macro organizer, it widens cols 0 and 6, but doesn't narrow cols 1-5. What am I missing? I'm a OO newbie on a Mac G4 running 10.4.11.

Jay
Last edited by Hagar Delest on Tue Jun 16, 2009 9:20 pm, edited 2 times in total.
Reason: tagged [Solved].
FJCC
Moderator
Posts: 9549
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Column Width macro

Post by FJCC »

This version of you code works

Code: Select all

Sub FormatColumns
static oSheet as object, oDoc As Object
oDoc = ThisComponent
oSheet = ThisComponent.getCurrentController.getActiveSheet
SetColWidth( 0, 1.5, oSheet )
SetColWidth( 1, .33, oSheet )
SetColWidth( 2, .33, oSheet )
SetColWidth( 3, .75, oSheet )
SetColWidth( 4, .75, oSheet )
SetColWidth( 5, .75, oSheet )
SetColWidth( 6, 2.75, oSheet )
End Sub

sub SetColWidth( col as integer, nchs as single, oSheet as object )
Static oColumn As Object

dim w as single
w = nchs * 2540
' get column
oColumn = oSheet.getColumns.getByIndex( col )
' column width (in 100ths of mm)
'oColumn.setPropertyValue("Width", w)
oColumn.Width = w

end sub
All I did is change the line that actually sets the column width. Glancing through what you did, I don't see why it wouldn't work. I'll look again later and hope I see something obvious.
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.
JayReeve
Posts: 8
Joined: Sun Jun 14, 2009 7:21 am

Re: Column Width macro

Post by JayReeve »

FJCC wrote:This version of you code works

oColumn.Width = w

All I did is change the line that actually sets the column width. Glancing through what you did, I don't see why it wouldn't work. I'll look again later and hope I see something obvious.
Works like a charm. Many thanks! The syntax I used was the only one I could dig up in all the docs. Where can object properties be found?

Still seems bizarre that it would work for widening, but not narrowing.

Jay
OOo 3.1.X on Mac OSx other
FJCC
Moderator
Posts: 9549
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Column Width macro

Post by FJCC »

Having an object inspection tool that lists the properties and methods of an object is essential for preserving your sanity when writing macros. Here are links to two tools

XRay

MRI
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.
JayReeve
Posts: 8
Joined: Sun Jun 14, 2009 7:21 am

Re: Column Width macro

Post by JayReeve »

FJCC wrote:Having an object inspection tool that lists the properties and methods of an object is essential for preserving your sanity when writing macros. Here are links to two tools

XRay

MRI
Thanks again! XRay looks like exactly what I need. Unfortunately, I haven't been able to make it work. On the first try, I missed the caveat about making sure it was saved to the right place, and doing so on the second try doesn't seem to have worked. Do I need to delete it and start over? Any advice appreciated.

Jay
FJCC
Moderator
Posts: 9549
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Column Width macro

Post by FJCC »

I'm not sure what you mean by "installed in the right place". If you look under Tools -> Macros -> Organize macros -> OOo Basic and then expand the My Macros list, do you see an Xray Tool folder? If so, you should have it installed correctly. If you don't see that, then I guess you need to run the install using the button in the Xray Tool52 document you downloaded. If it still doesn't work, please describe exactly what you did and what resulted.
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.
JayReeve
Posts: 8
Joined: Sun Jun 14, 2009 7:21 am

Re: Column Width macro

Post by JayReeve »

FJCC,

Here's what I see:
Clip.jpg
Clip.jpg (8.62 KiB) Viewed 12103 times
The XRay tools are obviously there, but not in MyMacros. When I run a macro with the line
"xray ThisComponent", it gags. "Sub procedure or function procedure not defined." I'm not familiar enough with this yet to know the next step.

Thanks again for your help.
Jay
FJCC
Moderator
Posts: 9549
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Column Width macro

Post by FJCC »

It looks to me like you need to run the install process. In the Xray Tool52 document there is an "Install Xray" button on page 4. Be sure to enable macros when asked upon opening the document. After you press that button, you should see XRay appear in the My Macros folder.
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.
JayReeve
Posts: 8
Joined: Sun Jun 14, 2009 7:21 am

Re: Column Width macro

Post by JayReeve »

FJCC,

I never got the dialog asking about enabling macros, and there is no Options—> Security under Tools as indicated in the doc, but I finally changed my security setting from the main options and then got the dialog.

It's working, and it's beautiful. I would never have guessed such a tool was available without your help. Thanks a million.

Jay
Post Reply