topic-38742

Posts: 1195

Joined: Mon Oct 17, 2005 05:41 AM

Excel - text width
Posted: Sun Apr 05, 2020 01:57 PM

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

Posts: 1335

Joined: Fri Jun 13, 2008 11:04 AM

Re: Excel - text width
Posted: Mon Apr 06, 2020 08:46 AM

Di you try

Code (fw): Select all Collapse

oSheet:Cells:EntireColumn:AutoFit

The above code should AutoFit All Columns on the Worksheet

Marc Vanzegbroeck

Posts: 1195

Joined: Mon Oct 17, 2005 05:41 AM

Re: Excel - text width
Posted: Mon Apr 06, 2020 08:53 AM

anserkk wrote:Di you try
Code (fw): Select all Collapse
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

Posts: 1335

Joined: Fri Jun 13, 2008 11:04 AM

Re: Excel - text width
Posted: Mon Apr 06, 2020 09:01 AM

What about wrapping the text of the merged cell ?

Marc Vanzegbroeck

Posts: 1195

Joined: Mon Oct 17, 2005 05:41 AM

Re: Excel - text width
Posted: Mon Apr 06, 2020 09:14 AM

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

Posts: 1335

Joined: Fri Jun 13, 2008 11:04 AM

Re: Excel - text width
Posted: Mon Apr 06, 2020 09:28 AM

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 (fw): Select all Collapse

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

Posts: 1195

Joined: Mon Oct 17, 2005 05:41 AM

Re: Excel - text width
Posted: Mon Apr 06, 2020 09:32 AM

Thank you,

I will try it.

anserkk wrote: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 (fw): Select all Collapse

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

FiveTech Support Forums

Continue the discussion