Basic setPropertyValue problem

[als50]

I am attempting to set the properties for all the rows and columns in a spreadsheet (using Basic). From reading the documentation, it would seem that the following should work, however, it does not:

Dim oDoc As Object, oSheet As Object, vValue As Variant

oDoc = ThisComponent	

oSheet = oDoc.Sheets.getByName("mySheet")

vValue = False

oSheet.Rows.setPropertyValue("OptimalHeight", vValue)

oSheet.Columns.setPropertyValue("OptimalWidth", vValue)

print "height: " & oSheet.Rows.getPropertyValue("OptimalHeight") & _
      "   width: " & oSheet.Columns.getPropertyValue("OptimalWidth")

When I run the above, the value returned via the Print is always True. I have changed the vValue to String and Boolean, and have used the Boolean value directly as follows:
oSheet.Rows.setPropertyValue(“OptimalHeight”, False)

Any ideas why this fails? Does one have to write to the Sheet for the property to take affect???

[mark_t]

Edit to place all the code inside code block.

[mark_t]

I think you need to set the property for every row, example

For i = 0 to n
    oSheet.Rows(i).OptimalHeight = False
Next i

Also oSheet.Rows.getPropertyValue("OptimalHeight") only returns the status for the first row. Again you need to iterate over each row to get the status of every row.

You might use :-

oSheet.Rows.Height = oSheet.Rows.Height

Which sets OptimalHeight = False for all rows but also sets the height of all rows the same as the first row.

[als50]

Thank you for the response. After more experimenting I discovered that you have to set the property AFTER you write the data. So,

oSheet.setDataArray(rows)

followed by:

oSheet.Rows.setPropertyValue(“OptimalHeight”, False)

oSheet.Columns.setPropertyValue(“OptimalWidth”, False)

does work. Apparently, whenever you add data to a sheet it does not automatically use the settings for the rows and columns. So even when the sheet is set for OptimalHeight/Width, it does not adjust the height/width when new data is added.