I'm creating an Excel spreadsheet from a DataTable. I've got the code all
working, but the data that I'm writing out for one particular column is
"001". Excel is truncating that value to be a number, so it only displays "1".

How can I set the datatype of the cell to Text, so it will preserve the
leading zeroes?

Thanks
Chris

ActiveCell.NumberFormat = "0000"
--
jb


"CCusson15" wrote:

I'm creating an Excel spreadsheet from a DataTable. I've got the code all
working, but the data that I'm writing out for one particular column is
"001". Excel is truncating that value to be a number, so it only displays "1".

How can I set the datatype of the cell to Text, so it will preserve the
leading zeroes?

Thanks
Chris

Thanks John!

One followup, I know the datatype of the column that I'm reading from in the
DataTable (say DateTime, int, string, etc.)

Is there any way I can translate those types to Excel, other that setting
the NumberFormat to those zeros, etc.?

Thanks,
Chris

"john" wrote:

ActiveCell.NumberFormat = "0000"
--
jb


"CCusson15" wrote:

I'm creating an Excel spreadsheet from a DataTable. I've got the code all
working, but the data that I'm writing out for one particular column is
"001". Excel is truncating that value to be a number, so it only displays "1".

How can I set the datatype of the cell to Text, so it will preserve the
leading zeroes?

Thanks
Chris

do you mean something like:

NumberFormat = "dd/mm/yyyy h:mm" ?

suggest you have a read VBA helpfile under NumberFormat for further guidance.
--
jb


"CCusson15" wrote:

Thanks John!

One followup, I know the datatype of the column that I'm reading from in the
DataTable (say DateTime, int, string, etc.)

Is there any way I can translate those types to Excel, other that setting
the NumberFormat to those zeros, etc.?

Thanks,
Chris

"john" wrote:

ActiveCell.NumberFormat = "0000"
--
jb


"CCusson15" wrote:

I'm creating an Excel spreadsheet from a DataTable. I've got the code all
working, but the data that I'm writing out for one particular column is
"001". Excel is truncating that value to be a number, so it only displays "1".

How can I set the datatype of the cell to Text, so it will preserve the
leading zeroes?

Thanks
Chris

John,

I don't necessarily know if there are leading zeros, and if there are any,
whether to keep them or not. Is it possible within numberformat to set the
type as Text, like in Excel itself?

"john" wrote:

do you mean something like:

NumberFormat = "dd/mm/yyyy h:mm" ?

suggest you have a read VBA helpfile under NumberFormat for further guidance.
--
jb


"CCusson15" wrote:

Thanks John!

One followup, I know the datatype of the column that I'm reading from in the
DataTable (say DateTime, int, string, etc.)

Is there any way I can translate those types to Excel, other that setting
the NumberFormat to those zeros, etc.?

Thanks,
Chris

"john" wrote:

ActiveCell.NumberFormat = "0000"
--
jb


"CCusson15" wrote:

I'm creating an Excel spreadsheet from a DataTable. I've got the code all
working, but the data that I'm writing out for one particular column is
"001". Excel is truncating that value to be a number, so it only displays "1".

How can I set the datatype of the cell to Text, so it will preserve the
leading zeroes?

Thanks
Chris

Something like this:

Columns("D:D").NumberFormat = "@"

and then copy the data will preserve any leading zeroes.