Array loses data type when placed into range
 

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?

Array loses data type when placed into range
 

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?

Array loses data type when placed into range
 

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?