# in calc code using optimalWidth for columns is incorrect [closed]

I am having problems with OptimalWidth for columns in calc sheets. I found that the code Sub test() below does not work. As the column 0 gets longer the number of characters not displayed increases.

sub test()
dim colnum as integer
dim oColumns as object
dim col as object
colnum = 0
oColumns = FileList.Columns
Col = oColumns.getByIndex(colnum)
Col.OptimalWidth = True
end sub


However, if I select the longest cell ($A$0 for this example) in the column and execute the code sub autoadjustcol() below the cell and the column are the correct length

sub autoadjustcol()
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 = "$A$0
'the next two lines set the variables that are in the dialog for Optimal Width
args2(0).Name = "aExtraWidth"
args2(0).Value = 0
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args1())
dispatcher.executeDispatch(document, ".uno:SetOptimalColumnWidth", "", 0, args2())
end sub


Do I have a mistake in Sub test()?

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:32:00.346303

Sort by » oldest newest most voted

Hi,

I think that the "oColumns = FileList.Columns" is incorrect .
I can not understand "FileList".
I think it is Ok if you change following;
oColumns = FileList.Columns → oColumns = ThisComponent.getsheets().getByIndex(0)

You can see sample code following URL ;
URL : http://openoffice3.web.fc2.com/OOoBasic_Calc.html#OOoCCR07a

OptimalWidth　: 行高さ＆列幅の最適化(1) ⇔ Optimize hight of rows and width of columns(1)
.uno:SetOptimalColumnWidth　: 行高さ＆列幅の最適化(2) ⇔ Optimize hight of rows and width of columns(2)

-------- [ PS ] -------
＞My code ran as does yours and both give the same incorrect result.

I can not understand why both have the same incorrect result.
I think that Code " OptimalWidth　: 行高さ＆列幅の最適化(1) ⇔ Optimize hight of rows and width of columns(1)" to be optimized all height and width in the sheet without error.

＞・・・simpler code does not get the same result as the UNO code.

I think that following code give exactly the same result as the UNO code

Sub ColRowOptimaize()
Dim oDoc as Object, oSheet as Object
Dim oCol as Object, oRow as Object
oDoc = ThisComponent
oSheet = oDoc.getSheets().getByIndex(0)
oCol = oSheet.getColumns().getByIndex(0)
oCol.OptimalWidth = true
for i = 0 to 1
oRow = oSheet.getRows().getByIndex(i)
oRow.OptimalHeight = true
next i
msgbox "Success"
End Sub

more

I just constructed a column which has one cell that has a string 169 characters long. When I run your code (or my simple code) the column is adjusted to a width that is 8 characters short. What is displayed is not the entire string. When I run the UNO code the column is adjusted to exactly the correct width. All characters are displayed. Maybe my PC is different then yours. The font in the spreadsheet is the standard Arial 10.

( 2013-12-25 14:42:24 +0200 )edit

( 2013-12-26 13:56:20 +0200 )edit

First, I am sorry that I failed to fix ‘Filelist’ in my sample code. It was dimensioned outside this sub routine and is the equivalent of ‘ThisComponent’. My code ran as does yours and both give the same incorrect result. The column is not set to the optimal width. It is close but misses by a few characters. A few characters on the end are missing. As the column gets wider the number of characters missing increases.

On the other hand the code in the link you provided does work correctly. It is:

Sub UnoOptimalColRow()
Dim oDoc as Object, oCtrl as Object, oFrame as Object
Dim oDispatcher as Object
Dim oProp(0) as new com.sun.star.beans.PropertyValue
oFrame = ThisComponent.CurrentController.Frame
oDispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
'
oProp(0).Name = "ToPoint"
oProp(0).Value = "A:A"
oDispatcher.executeDispatch(oFrame,  ".uno:GoToCell", "", 0, oProp())
' 引数を省略すると最適な列幅Dialogが表示される。
oProp(0).Name = "aExtraWidth"
oProp(0).Value = 0
oDispatcher.executeDispatch(oFrame,  ".uno:SetOptimalColumnWidth", "", 0, oProp())
msgbox "Optimal Colwmn Width",0,"Optimize"
'
oProp(0).Name = "ToPoint"
oProp(0).Value = "1:2"
oDispatcher.executeDispatch(oFrame,  ".uno:GoToCell", "", 0, oProp())
oProp(0).Name = "aExtraHeight"
oProp(0).Value = 0
oDispatcher.executeDispatch(oFrame,  ".uno:SetOptimalRowHeight", "", 0, oProp())
msgbox "Optimal Row Height",0,"Optimize"
End Sub


It correctly sets the column width to exactly the right number of characters. I did not test the row height part but I suspect it also works correctly. Many thanks this solves my problem.
Maybe somebody knows why the simpler code does not get the same result as the UNO code.

more