Copy Data into Excel

[legend99]

Hi,
When I am copying data from ASP to Excel via an OCX, I am including & CHR(9) to tab the data and & CHR(10) to line feed the data.
When I take in the input string like this, copy it to the clipboard and then paste it into CellA1 it correctly tabs the data into B1, C1, D1 before hitting the CHR(10) to wrap it to a new line.


However, if I try and insert the data directly into cell A1 without using the clipboard it attempts to insert all the data into A1 only and I see little circles where the Tabs and Line Feeds were...does anyone know how to make Excel recognise the tabs without me having to use the clipboard?

 

[PHV]

Have a look at the TextToColumns method of the Range object.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[legend99]

So i would have to insert all the data into the first column and then get it split?
2 things, first off, does this method allow you to get onto new lines.

Second, I think I might have a bigger problem in any case because these reports could have say 30,000 rows with say 6 columns, and when i do the paste into the first cell with that amount of data it falls over trying to do the insert.
So it would not even get as far as me converting the columns into seperate ones?

But I'd still be interesting to know how it does the wrap to new line with your method?

 

[PHV]

I am including & CHR(9) to tab the data and & CHR(10) to line feed the data
Have you tried Chr(13) & Chr(10) as line separator ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[xlbo]

or even vbcrlf (carriage refturn and line feed in 1)

myString = "1st line" & vbcrlf & "2nd line"

Rgds, Geoff

A file that big? It might be very useful. But now it is gone

Please read FAQ222-2244 before you ask a question