Autofit/wraptext on vbLf problem

[KenThompson]

Hello,
I have a little formatting issue I am hoping one of you can help me with. I
have a vbs script that is creating and populating a spreadsheet that will
then be used to select some options and loaded into an API. One of the cells
is a list that needs to appear as a list with each list item on it's own line
within the cell.

I use this code to populate the cell:

'create vblf delimited list
strCyclist = Replace(WScript.Arguments.Item(6),",",vbLf)
'put it in the cell
objSheet.Cells(intDBRow,9) = strCyclist

I use this code to format:
With objSheet
.Columns("A:I").autofit
.Columns("B:B").Columnwidth = 45
.Columns("A:J").wraptext = True
End With
For Each intRow In objsheet.usedrange
intRow.EntireRow.AutoFit
Next


With my master plan here being that the autofit/wraptext would wrap that
cell on the vbLf. But it turns out that the autofit/autowrap is wrapping on
the first space in the text.

My idea kinda works in that if you enlarge the column width larger than a
single item in the list, then it wraps at the vbLf. So, I guess the wraptext
is working as expected but the autofit is sizing the column off the first
available space.

Any Ideas on how to get this to work as desired are welcome. Thanks in
advance.

 

[OssieMac]

Hi Ken,

You have basically answered your own question. "if you enlarge the column
width larger than a single item in the list, then it wraps at the vbLf".
Simply set the column width larger than what can be expected and then apply
AutoFit.

Even in the interactive mode you have to widen the column and then apply
AutoFit or it adjusts to the widest existing data in the cells and tends to
ignore linefeeds. Auto column width adjusts to the max width of the data in
the cells as it exists prior to the AutoFit.

A little constructive criticism on your other code. Why loop trough the used
range to adjust the height. You can simply apply Autofit to the entire used
range.

objSheet.UsedRange.Rows.AutoFit

'or following

With objSheet.UsedRange
.Rows.AutoFit
.Columns.AutoFit '(If required.)
End With

 

[KenThompson]

Hi OssieMac,
Thanks for your response. I was afraid that was the answer. The problem is
I don't know what the size is going to be. But it is what it is, I'm
thinking I'll just write it to set the columnwidth based on the size of the
string of one list item.
As far as the row height code, yeah I know it's ugly... it's really
obviously ugly if Excel is visible while it's doing it. It's that way
because I could never find the right syntax to do it all at once, everything
I tried (this was some time ago, as I'm reusing code) resulted in some error
except the way that's shown.
So! Thank you for the code, I'll use it!
Thanks for your help, OssieMac.

Ken