Ask Your Question
0

justifying column width in spreadsheet [closed]

asked 2013-12-18 20:45:17 +0100

RussR gravatar image

updated 2013-12-18 20:46:11 +0100

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 flag offensive 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

Comments

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.

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

1 Answer

Sort by » oldest newest most voted
0

answered 2013-12-20 01:02:48 +0100

RussR gravatar image

updated 2013-12-24 17:57:00 +0100

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.

edit flag offensive delete link more

Question Tools

1 follower

Stats

Asked: 2013-12-18 20:45:17 +0100

Seen: 826 times

Last updated: Dec 24 '13