I have a VB program that uses Automation to create a worksheet and
then dimension a range as large as an array Dim'ed in the VB
program... I then just set the range values equal to the array
variable... i.e. -

Set rngData = oExcel.Application.ActiveCell.Resize(UBound(arr, 1) + 1,
UBound(arr, 2) + 1)
rngData = arr

And it dumps the array into the range.

Problem: a text string from an array element like "0003334" is
interpreted as a number by excel when placed into the cell. the zeros
are considered "insignifigant" and they are removed, Just as if I had
typed the string into the cell myself.

I was thinking maybe concatenating a chr(39) to the front of the
string would cause excel to change the data type of the cell to a
string type, and left justify it in the cell, etc...

Is it o.k. to do this?

It's your program! Whether or not prepending a CHR(39), whatever that
is, is acceptable or not is up to you -- and your customer(s).

You may want to try -- and I don't know if it will work -- to format
the range as 'Text' before transfering the data to it.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article , Mbtch@.
says...
I have a VB program that uses Automation to create a worksheet and
then dimension a range as large as an array Dim'ed in the VB
program... I then just set the range values equal to the array
variable... i.e. -

Set rngData = oExcel.Application.ActiveCell.Resize(UBound(arr, 1) + 1,
UBound(arr, 2) + 1)
rngData = arr

And it dumps the array into the range.

Problem: a text string from an array element like "0003334" is
interpreted as a number by excel when placed into the cell. the zeros
are considered "insignifigant" and they are removed, Just as if I had
typed the string into the cell myself.

I was thinking maybe concatenating a chr(39) to the front of the
string would cause excel to change the data type of the cell to a
string type, and left justify it in the cell, etc...

Is it o.k. to do this?

Yes, prepending a chr(39) (the single quote) should do what you want I would
think

This worked fine for me inside Excel:

Sub aabb()
ReDim v(1 To 5, 1 To 1)
For i = 1 To 5
v(i, 1) = "'00000" & i ^ 2
Next
With Worksheets("Sheet2")
.Range("B9").Resize(5, 1).Value = v
End With
End Sub

--
Regards,
Tom Ogilvy


<Mbtch@. wrote in message
...
I have a VB program that uses Automation to create a worksheet and
then dimension a range as large as an array Dim'ed in the VB
program... I then just set the range values equal to the array
variable... i.e. -

Set rngData = oExcel.Application.ActiveCell.Resize(UBound(arr, 1) + 1,
UBound(arr, 2) + 1)
rngData = arr

And it dumps the array into the range.

Problem: a text string from an array element like "0003334" is
interpreted as a number by excel when placed into the cell. the zeros
are considered "insignifigant" and they are removed, Just as if I had
typed the string into the cell myself.

I was thinking maybe concatenating a chr(39) to the front of the
string would cause excel to change the data type of the cell to a
string type, and left justify it in the cell, etc...

Is it o.k. to do this?