How to keep leading zeros in cvs file format

[voodoo]

I am using a macro to create a csv file (comma seperated text file). I have some entries which are numbers with leading zeros e.g 0001245

I want to keep these leading zeros and have them appear when the file is eventually opened in Excel.

I am able to capture them in the csv file by using double quotation marks and it appears like this in the cvs file

" 0101 "," 1792 ","0001245453"

But when opened in Excel the leading zeros are always dropped.

Is there any way to keep the zeros showing when opened in Excel?

Thanks

voodoo

 

[zacemmel]

Easy, import the file using the data import wizard and DO NOT skip the last step, you have to set the data import column format to text or else it will ALWAYS drop those leading zeros.

Even if you set the column format before doing the import, it will set it to general unless you specify and therefore drop the zeros.

 

[voodoo]

Thanks for the reply

Can you tell me exactly how to access the data file wizard you mention?

I know when I try and open a txt file in Excel it goes through a text file wwizard which gives these kind of options. But with csv file when I Press File then Open and select the file it juts opens automatically in Excel without giving any options.

Another complication is I am creating this file in a macro and at the end of the macro the file needs to be shown in an open state. So I would need to access the data file wizard via the macro using VBA if this is possible?

Thanks again for your help

 

[zacemmel]

You can either rename the csv file to txt or you can also import a csv file just as you would a txt file. Goto Data - > Get external data.

 

[voodoo]

Thanks for your help

I worked it out in the VBA as well. I was using workbooks.Open instead of Workbooks.OpenText

 

[voodoo]

Oh no upon checking again this is not working, it only works when my file is a .txt file. it doesnt work for .csv.

The thing is my users want a .csv not a .txt file

Another thing is my users all use Excel97 and there is no Inport Text File option from Data -> Get External Data. This appears to be new to Excel 2000. So this appear not to be an option as well.


Any other ideas?