Click here to Skip to main content
15,849,829 members
Please Sign up or sign in to vote.
4.00/5 (1 vote)
See more:
I'm opening a .csv file, copying the contents, then pasting into an Excel .xlsb worksheet.
In the .xlsb file, I've preformatted column A for text using the "@" option.

The first column in the .csv file is text with entries like "004", 008", "012", etc.
When I paste the .csv contents into the .xlsb worksheet, the leading zeros are stripped off. The column still stills shows as Text formatted and the data are left registered as text would be.

For copy, I'm using:
crng = xl.Range(xl.Cells(2, 1), xl.Cells(chlrow, chlcol))

For paste, I'm using:
prng = xl.Range(xl.Cells(plrow + 3, 1), xl.Cells(plrow + 3, 1))
prng.PasteSpecial(XlPasteType.xlPasteValues, XlPasteSpecialOperation.xlPasteSpecialOperationNone, False, False)

I thought "PasteValues" would transfer the data without modification but apparently I thought wrongly.

Any suggestions,

Updated 12-Sep-12 5:38am
Kenneth Haugland 12-Sep-12 10:42am    
Depends on the formats of the cell I guess, I think you would have to set the cell.Format as String if you are going to preserve the text exactly.
Coderington 12-Sep-12 10:50am    
Would formatting the column using the "@" option not be correct for text?
Kenneth Haugland 12-Sep-12 10:59am    
I though that was a C# only attributte, and if you stic ' before and after each item that is pasted you should get the text formating automatically.
Coderington 12-Sep-12 11:39am    
I used
xl.Range("A:A").NumberFormat = "@"
and that formats the column to text with no errors.

I could, as you suggested, use the single quote modification but that would entail addressing each cell individually, which is something I was trying to avoid in the interest of run time.
Kenneth Haugland 12-Sep-12 11:56am    
But excel still recognize the content as a number an auto correct the 011 to 11 ?

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900