Putting Values with Leading Zeros Into CSV file

[cwojtak]

I have relatively simple macro that allows users to enter data in an excel sheet, they then run the macro and it transforms their data into an upload friendly csv file. Unfortunately some of the data that needs to be put into the csv file has leading 0's and I cannot figure out how to keep them.. I've tried many methods including setting to text format like below, formatting as "0000", etc. with no luck. Below is a dumbed down example of what I've been trying.


Sub MoveToCSVWithLeadingZeroes()
Dim csvWb As Workbook
Dim inputWb As Workbook
Dim csvSh As Worksheet
Dim inputSh As Worksheet
Set csvWb = Workbooks("csvWb.csv")
Set csvSh = csvWb.Worksheets("csvSh")
Set inputWb = Workbooks("inputWb.xlsm")
Set inputSh = csvWb.Worksheets("inputSh")
csvSh.Range("A1").NumberFormat = "@"
csvSh.Range("A1").Value = inputSh.Range("A1").Value
End Sub

[georgiboy]

You have an error in the part where you set the 'inputSh' where you call it by 'csvWb', could try it as below:

Sub MoveToCSVWithLeadingZeroes()
Dim csvWb As Workbook
Dim inputWb As Workbook
Dim csvSh As Worksheet
Dim inputSh As Worksheet

Set csvWb = Workbooks("csvWb.csv")
Set csvSh = csvWb.Worksheets("csvSh")

Set inputWb = Workbooks("inputWb.xlsm")
Set inputSh = inputWb.Worksheets("inputSh")

csvSh.Range("A1").NumberFormat = "@"
csvSh.Range("A1").Value = inputSh.Range("A1").Value
End Sub

It created the CSV file fine, when I opened it excel asked me if I want to remove the leading zero's - I clicked 'Don't convert'

[cwojtak]

Sorry typo in the original code. It looks like it works perfectly but when I don't save and close but then when I save the file it removes the leading zeroes. I've added the code to create the workbook and save it.


Sub MoveToCSVWithLeadingZeroes()

Workbooks.Add
ActiveWorkbook.SaveAs "C:\csvWb.csv", xlCSV
ActiveSheet.Name = "csvSh"

Dim csvWb As Workbook
Dim inputWb As Workbook
Dim csvSh As Worksheet
Dim inputSh As Worksheet

Set csvWb = Workbooks("csvWb.csv")
Set csvSh = csvWb.Worksheets("csvSh")

Set inputWb = Workbooks("inputWb.xlsm")
Set inputSh = inputWb.Worksheets("inputSh")

csvSh.Range("A1").NumberFormat = "@"
csvSh.Range("A1").Value = inputSh.Range("A1").Value

csvWb.Close SaveChanges:=True

End Sub

[Paul_Hossler]

Just a thought. Try .Text




csvSh.Range("A1").Value = inputSh.Range("A1").Text

[georgiboy]

When I open the created CSV file I am greeted with the below message:
30302

If i click 'Don't Convert' then the leading zero's remain in the file, do you get to see the above dialogue?

Perhaps the "don't show again" box was ticked at some point/ perhaps our machines are different.

[p45cal]

Just a thought. Try .Text

csvSh.Range("A1").Value = inputSh.Range("A1").Text
Yes, that seems to work, however there is no .Text property for a range of cells so you'll need to iterate over the cells, perhaps a variation on something like this:
Sub MoveToCSVWithLeadingZeroes()

Workbooks.Add
ActiveWorkbook.SaveAs "C:\csvWb.csv", xlCSV
ActiveSheet.Name = "csvSh"

Dim csvWb As Workbook
Dim inputWb As Workbook
Dim csvSh As Worksheet
Dim inputSh As Worksheet
Dim rngToCopy As Range, cll As Range

Set csvWb = Workbooks("csvWb.csv")
Set csvSh = csvWb.Worksheets("csvSh")

Set inputWb = Workbooks("inputWb.xlsm")
Set inputSh = inputWb.Worksheets("inputSh")

Set rngToCopy = inputSh.Range("A1:A2")
For Each cll In rngToCopy.Cells
With csvSh.Range(cll.Address)
.NumberFormat = "@"
.Value = cll.Text
End With
Next cll
csvWb.Close SaveChanges:=True

End Sub

[snb]

Sub M_snb()
sn = Sheet1.Cells(1).CurrentRegion

For j = 1 To UBound(sn)
For jj = 1 To UBound(sn, 2)
c00 = c00 & Format(sn(j, jj), "0000") & ","
Next
c00 = c00 & vbCrLf
Next

CreateObject("scripting.filesystemobject").createtextfile("G:\OF\example.csv").write c00
End Sub

[cwojtak]

Marking this as solved as I made it work for the upload purposes. If you open the csv file in excel you will not see the leading zeroes anymore and if you save changes when you close the file it will remove them but if you open and close it without saving the leading zeroes still show. I simply added this after opening the workbook to put all the cells I was entering info with leading zeroes in into text format.


Range("C2:C1048576").NumberFormat = "@"