CODEWITHSUNDEEP
excelvba
Jeffrey Kramer
Jeffrey Kramer

Reputation: 1345

Excel VBA won't paste leading zeros

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

Upvotes: 2

Views: 3791

Answers (2)

Bram
Bram

Reputation: 628

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).

Upvotes: 3

Stewbob
Stewbob

Reputation: 16899

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.

Upvotes: 4

Related Questions