Preserve leading zero's

[Teaser]

Hi All,

 

Basically I have a vbscript that exports data from a SQL server 2000 into Excel. There is a db field "Supplier_Code" held as a varchar, 7 digits long with leading zeros. When the data is pumped to excel the leading zeros are dropped. Below is the line of code that pumps this specific column to excel.

 

objSheet.cells(intRow,2).value = Trim(rsBatchResults.Fields("Supplier_Code").value)

 

I need to preserver the leading zero's in the excel output. I have tried

 

Right("0000000" & rsBatchResults.Fields("Supplier_Code").value),7) which does not give me an error but doesn't add the leading zeros either

[localzuk]

The problem is, Excel treats leading zeros as unnecessary as the field will see them as numbers. The only way I know of to keep the zeros is to use a custom format on a field:

 

Using a custom number format to display leading zeros

 

However I don't know how you'd do that in vbscript.

[srochford]

Try:

 

objSheet.cells(intRow,2).value = Trim(rsBatchResults.Fields("Supplier_Code").value) 
objSheet.cells(intRow,2)..numberformat = "0000000"