using vba with excel 2003

[douglas]

I need to put char(5) into 'selected' columns in an excel 2003 worksheet
using VBA.
The problem is some of the data looks like '00123' and ends in the
spreadsheet looking like '123'. I am losing the leading zeroes. The only way
I know to make the selected range of columns is to make the selected columns
a 'text' format.
Thus using VBA can you tell me how to do the solve the problem above:
1. Do I set the selected columns to 'text' fomrat and then input the
data? If not,
2. Do I put data into the selected columns and then change the data to a
'text' format?

Thanks!

 

[Clif McIrvin]

It's been my experience that the cell needs to be formatted as text
*before* entering data or Excel "helpfully" classifies it as a number
(or date) for you.

You could also force it to be text by entering " '00123" instead of
"00123" (note the single quote).

 

[Steve Rindsberg]

I need to put char(5) into 'selected' columns in an excel 2003 worksheet
using VBA.
The problem is some of the data looks like '00123' and ends in the
spreadsheet looking like '123'. I am losing the leading zeroes. The only way
I know to make the selected range of columns is to make the selected columns
a 'text' format.

Format as CUSTOM, 00000 (one zero for each position you want to force to be
filled with a number or zero).

In VBA:

.NumberFormat = "00000"

 

[Sergio]

Douglas, You can use this solution for Excel. I use it all the time.

For example: "=""08075""" (Use all the quotes)

Put this value in a .csv file and you will keep the leading zeros. The
equal sign will not be displayed. The field will keep a "General" formatting.

Thank you,

Sergio Mundarain