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.
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.
The recorder gave me this:
sub optimalcol
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 = "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.