justifying column width in spreadsheet [closed]

I see something called 'VertJustify' and 'VertJustifyMethod' in 'com.sun.star.sheet.XSpreadsheet'. I assume this is akin to 'Columns("A:A").EntireColumn.AutoFit' in VBA for Excel which justifies column A.
I can't find how to use either of these to justify a column. The recorder produces nothing for this operation. Any help would be appreciated.

edit retag reopen merge delete

Closed for the following reason question is not relevant or outdated by Alex Kemp close date 2015-11-16 18:20:35.228565

Sorry I was using the shortcut way of auto adjusting column widths. Now that I have used 'Format'>'Column'>'Optimal Width'>'OK" the recorder gives me something I can work with. I will post an answer in a little bit once I work it out.

( 2013-12-18 21:24:01 +0100 )edit

Sort by » oldest newest most voted

The recorder gave me this:

sub optimalcol
rem ----------------------------------------------------------------------
rem define variables
dim document   as object
dim dispatcher as object
rem ----------------------------------------------------------------------
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 = "aExtraWidth"
args1(0).Value = 254
dispatcher.executeDispatch(document, ".uno:SetOptimalColumnWidth", "", 0, args1())
end sub

With no column identified this obviously does not work.
Poking around I got this to work. It does an optimal width on a cell which of course affects the whole column. I pass the cell column and row to the subroutine

sub autoadjustcol(cellcol as string, cellrow as integer)
dim document   as object
dim dispatcher as object
document = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
dim args1(0) as new com.sun.star.beans.PropertyValue
dim args2(0) as new com.sun.star.beans.PropertyValue
'the next two lines moves the focus to a cell
args1(0).Name = "ToPoint"
args1(0).Value = "$” & shcol & “$” & Shrow ‘this could be passed to the subroutine
'the next two lines set the variables that are in the dialog for Optimal Width
args2(0).Name = "aExtraWidth"
args2(0).Value = 254
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args1())
dispatcher.executeDispatch(document, ".uno:SetOptimalColumnWidth", "", 0, args2())

end sub

So far I have not determined how to change “ToPoint” to something pointing to a column. I can find no list of things related to com.sun.star.beans.PropertyValue that even includes “ToPoint” I did a work around where I find the cell with the max string length in column A and the cell for column B as I dump the array into the spreadsheet. When I set the longest cell to Optimal I have in affect set the column. It works so I am not going to spend any more effort on this. Maybe somebody can point us in the right direction

I was pointed to this solution by 'newOOo3'.

Sub UnoOptimalCol()
Dim oFrame as Object
Dim oDispatcher as Object
Dim oProp(0) as new com.sun.star.beans.PropertyValue
oFrame = ThisComponent.Frame
oDispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
oProp(0).Name = "ToPoint"
oProp(0).Value = "A:A"
oDispatcher.executeDispatch(oFrame,  ".uno:GoToCell", "", 0, oProp())
oProp(0).Name = "aExtraWidth"
oProp(0).Value = 254
oDispatcher.executeDispatch(oFrame,  ".uno:SetOptimalColumnWidth", "", 0, oProp())
End Sub

This works correctly.

more