Excel - text width

[Marc Vanzegbroeck]

Hi,

Is there a function to know the with of a text in cell of excel in pixels?

My program is creating an excel file and ith that file, some fields are merged.
The problem is that excel don't do an autofit of merged cells. I don't know the width of the text, cinse I get this information of a database

This is the result:


And sould be something like


If I know the width of the text in pixels, I can set the width via my program.

[anserkk]

Di you try

Code: Select all

oSheet:Cells:EntireColumn:AutoFit

The above code should AutoFit All Columns on the Worksheet

[Marc Vanzegbroeck]

Di you try

Code: Select all

oSheet:Cells:EntireColumn:AutoFit

The above code should AutoFit All Columns on the Worksheet

Hi,

Yes, I did this. This is working very nice, but if you have merged cells, it doen't work on that cell. It works well or the cells below and above that cell

[anserkk]

What about wrapping the text of the merged cell ?

[Marc Vanzegbroeck]

I don't want to wrap the text.

What I was think to do is:
1 Fill all the text that will me merged
2 Do a autofit
3 Than I can check the width of the columns, and save it into an array
4 Then merge the cells
5 Fill the rest of the rows.
6 Do the autofit again, and check that the colums are big enough according my array and wake them bigger if needed

[anserkk]

I do not know whether this will serve your purpose or not. I haven' tested this. You may have to tweak the code as per your requirement. Just give a try.

Code: Select all

Function AutoFitAll()

  AutoFitMergedCells(Range("B4:K4"), oSheet)
  AutoFitMergedCells(Range("B5:K5"), oSheet)
  AutoFitMergedCells(Range("B6:K6"), oSheet)

Return

Function AutoFitMergedCells(oRange, oSheet)
  Local nHeight,i,nOldWidth,nOldZZWidth,nNewWidth,nNewHeight

  With oSheet
    nOldWidth := 0
    For i := 1 To oRange:Columns:Count
      nOldWidth := nOldWidth + :Cells(1, oRange:Column + i - 1):ColumnWidth
    Next
    nOldWidth := :Cells(1, oRange:Column):ColumnWidth + :Cells(1, oRange:Column + 1):ColumnWidth
    oRange:MergeCells := .F.
    nNewWidth := Len(:Cells(oRange:Row, oRange:Column):Value)
    nOldZZWidth := :Range("ZZ1"):ColumnWidth
    :Range("ZZ1") := Left(:Cells(oRange:Row, oRange:Column):Value, nNewWidth)
    :Range("ZZ1"):WrapText := .T.
    :Columns("ZZ"):ColumnWidth := nOldWidth
    :Rows("1"):EntireRow:AutoFit
    nNewHeight := :Rows("1"):RowHeight / oRange:Rows:Count
    // Plase check the next line, you may have to properly trim the contents before concatenating
    :Rows( Str(oRange:Row) + ":" + Str(oRange:Row + oRange:Rows:Count - 1) ):RowHeight := nNewHeight
    oRange:MergeCells = .T.
    oRange:WrapText = .T.
    :Range("ZZ1"):ClearContents
    :Range("ZZ1"):ColumnWidth := nOldZZWidth
  End
Return

[Marc Vanzegbroeck]

Thank you,

I will try it.

I do not know whether this will serve your purpose or not. I haven' tested this. You may have to tweak the code as per your requirement. Just give a try.

Code: Select all

Function AutoFitAll()

  AutoFitMergedCells(Range("B4:K4"), oSheet)
  AutoFitMergedCells(Range("B5:K5"), oSheet)
  AutoFitMergedCells(Range("B6:K6"), oSheet)

Return

Function AutoFitMergedCells(oRange, oSheet)
  Local nHeight,i,nOldWidth,nOldZZWidth,nNewWidth,nNewHeight

  With oSheet
    nOldWidth := 0
    For i := 1 To oRange:Columns:Count
      nOldWidth := nOldWidth + :Cells(1, oRange:Column + i - 1):ColumnWidth
    Next
    nOldWidth := :Cells(1, oRange:Column):ColumnWidth + :Cells(1, oRange:Column + 1):ColumnWidth
    oRange:MergeCells := .F.
    nNewWidth := Len(:Cells(oRange:Row, oRange:Column):Value)
    nOldZZWidth := :Range("ZZ1"):ColumnWidth
    :Range("ZZ1") := Left(:Cells(oRange:Row, oRange:Column):Value, nNewWidth)
    :Range("ZZ1"):WrapText := .T.
    :Columns("ZZ"):ColumnWidth := nOldWidth
    :Rows("1"):EntireRow:AutoFit
    nNewHeight := :Rows("1"):RowHeight / oRange:Rows:Count
    // Plase check the next line, you may have to properly trim the contents before concatenating
    :Rows( Str(oRange:Row) + ":" + Str(oRange:Row + oRange:Rows:Count - 1) ):RowHeight := nNewHeight
    oRange:MergeCells = .T.
    oRange:WrapText = .T.
    :Range("ZZ1"):ClearContents
    :Range("ZZ1"):ColumnWidth := nOldZZWidth
  End
Return