Excel copy/paste problem

Question

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:

VB

crng = xl.Range(xl.Cells(2, 1), xl.Cells(chlrow, chlcol))
crng.Copy()

For paste, I'm using:

VB

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,

thanks

Posted 12-Sep-12 4:36am

Coderington

Updated 12-Sep-12 4:38am

Kenneth Haugland

v2

Add a Solution

Comments

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 ?

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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