[Solved] [Calc] Problem with OptimalWidth

[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

[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

[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

[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