Page 1 of 1
[Solved] [Calc] Problem with OptimalWidth
Posted: Mon Jun 07, 2010 11:05 am
by abhaysobti
Hi everyone,
I have a small problem. I am creating a sCalc sheet with 10 columns using code. After the creation, I want to Autosize the columns so that they adjust to the content with maximum width. However, I want only the 2nd columns onwards to autofit. The code I use is :
FOR mcounter = 1 TO last_field
oCalc.oCurrentSheet.Columns(mcounter).OptimalWidth=.T.
ENDFOR
The problem is, the moment the 2nd column's [i.e. Column(1)] OptimalWidth width is set to true, all columns automatically autofit.
Any help?
Abhay
Re: Problem with OptimalWidth
Posted: Mon Jun 07, 2010 12:55 pm
by rudolfo
I suppose there is something wrong with your loop. I used a test macro to only modify the 2. column in a spreadsheet with 8 or 9 columns:
Code: Select all
Sub FitColumnTwo()
Dim oSheets As Object, oSheet As Object
Dim oCols As Object, oCol As Object
oSheets = ThisComponent.getSheets()
oSheet = oSheets.getByIndex(0)
oCols = oSheet.getColumns()
oCol = oCols.getByIndex(1)
oCol.OptimalWidth = True
End Sub
And for my spreadsheet only the second column B changed in the width. I suggest you simplify your code to apply the autofitting only two a single column and see if you still get the same behaviour that all other columns change their width.
If the problems is not the loop the second candidate is
oCurrentSheet.Columns(mcounter). I usually try to avoid pseudo properties. They are only convenient until something stops working.
Edit: The OptimalWidth property can be set for the column collection as well:
oCurrentSheet.Columns.OptimalWidth = True
is executed as correct Basic code (and autofits all columns). Chances are that the mcounter in your code is ignored by the basic interpreter and it actually interprets:
oCurrentSheet.Columns().OptimalWidth = True |
Re: Problem with OptimalWidth
Posted: Mon Jun 07, 2010 4:29 pm
by B Marcelly
Hi,
A simpler solution (columns B to G of sheet named : xxx)
Code: Select all
Dim mySheet As Object, myColumns As Object
mySheet = ThisComponent.Sheets.getByName("xxx")
myColumns = mySheet.getCellRangeByName("B1:G1").Columns
' ** if column numbers are known at execution :
' myColumns = mySheet.getCellRangeByPosition(colStart, 0, colEnd, 0)
myColumns.OptimalWidth = True
[Solved] Problem with OptimalWidth
Posted: Mon Jun 07, 2010 4:41 pm
by abhaysobti
Thankx Guys,
Rudolfo's solution worked for me. Strangely, when I get a column by using getByIndex(1) rather than using Columns(1), the OptimalWidth works perfectly.
Abhay