Excel VBA won't paste leading zeros

2
votes

The following code is dropping leading zeros when they are included in a column. I thought that the PasteSpecial would take care of this. Is there a way to assure that any leading zeros are retained when this sub populates the CSV?

Sub PasteStufff()

Dim myRange As Range
Dim outFile As String

outFile = "mypath\path\file.csv"

Set myRange = Sheets("base").Range("A1:G1")
Set myRange = Range(myRange , myRange .End(xlDown))
Sheets("base").Select
myRange.Select
Selection.Copy
    Workbooks.Add
    ActiveSheet.Cells(1, 1).PasteSpecial xlPasteValues
    ActiveWorkbook.SaveAs Filename:= _
    outFile _
    , FileFormat:=xlCSV, CreateBackup:=False
    Application.DisplayAlerts = False
    ActiveWorkbook.Close
    Application.DisplayAlerts = True

End Sub
2
excelvba
How are the leading zeroes kept initially? Is an apostrophe used or a Custom Format for the cells, or the cells are formatted as Text? Are the leading zeroes lost when pasting, or after having saved and re-opened the csv (in Excel)? - Andy G
I'm pretty sure your going to want to paste special the formats as well as the values. - Stepan1010
Format the range as text. - Sorceri

2 Answers

3
votes

I don't know in which version it was introduced, but in Excel 2010, you can use

Paste:=xlPasteFormulasAndNumberFormats

instead of

Paste := xlPasteValues

and on the basis of very short testing, this seems to work (assuming your original inputs are numbers in the first place).

3
votes

Excel is very obnoxious when it comes to leading zeros. The only way that I have found to make this work is to have all the cells that will receive the pasted data pre-formatted as 'Text'. Not 'General' or anything else, just 'Text'. You have to format the cells as 'Text' before pasting the data. If you try to do it after the data is pasted, the leading zeros will already be lost.

If you do it that way, then you don't even need the PasteSpecial.

PasteSpecial will have no effect if the destination cells are not formatted correctly. Excel will always try to interpret anything that looks like a number, as a number.

This is absolutely not ideal, because it may require you to count all your data and then format just that many cells on the worksheet, but it is the only method I have found that is foolproof.