Preserving leading zeros when saveing as a csv macro?

[tonywatsonhelp]

Hi Everyone,

I need to create a csv and in column D i need to preserve it as text or stop the leading zero from being removed?
Any ideas how i can do this?

I currently use this macro to save the document as a csv

Sub Create_CSV()
Application.ScreenUpdating = False
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    Dim fName As String
    fName = welcome.Range("D12") & "\" & welcome.Range("D14") & " Report - saved " & Format(Now, "dd-mmm-yyyy") & ".csv"
        
    Output.Copy

    ActiveWorkbook.SaveAs Filename:=fName, _
        FileFormat:=xlCSVUTF8, CreateBackup:=False
    
'    ActiveWorkbook.Close True

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Application.ScreenUpdating = True

End Sub

This works great but I lose the leading zeros in column D

Coulmn D is set to text and the zeros are there until you close it down when the csv looses all formating.

please help if you can

Thanks

Tony

 

[Joe4]

This works great but I lose the leading zeros in column D

Do you really?

When you save/export a file to a CSV file, it SHOULD maintain the formatting.
Many times, people think it does not when it actually does because they try to view the CSV in Excel, which is a mistake, because Excel does its own automatics conversion (like dropping leading zeroes) when it opens the file.

For this reason, you should NEVER use Excel to view the contents of a CSV file, it you truly want to see the data as it exists in the CSV file!
Use a Text Editor like NotePad or WordPad to view it. If you do that, do you see the leading zeroes?
If you do, then they are really in the CSV file.

 

[tonywatsonhelp]

Ok,
That sound like my problem is solved
thanks for your help
Tony

 

[Joe4]

You are welcome.

That is one of my biggest "pet peeves" of Office, that they decided that Excel is the default program to use to open CSV files, because of this issue.
Because of that, one of the first things to do whenever I get a new computer is to change my default program to open CSV files to some Text Editor.