Text string syntax in Windows clipboard, to Paste multi line text into Excel cells
. Hello
I think I got close to solving this as I was preparing a post to ask for help…..
But I am not totally sure if I have it completely correct.
So I thought it would do no harm to air my problem and what appears at first glance to be a possible solution.
I welcome any comments. As ever I am frustrated that I have to find out empirically how to do these sort of things, because of not being able to find any precise documentation… So my initial stab at the solution might not be the correct way or best way to do it.
The problem came up when adding text to a text string which was generated from the command prompt, and which I was wanting to paste into an excel column ( http://eileenslounge.com/viewtopic.php? ... 75#p261670" onclick="window.open(this.href);return false; , https://tinyurl.com/rwt2kx8" onclick="window.open(this.href);return false; )
Problem:
I wanted to add information to the text string before pasting it programmatically ( via VBA ) into an Excel worksheet. I wanted that information to go into one cell, but I wanted it to be in 2 lines within the cell, as in cell A2 or B2 here: https://imgur.com/9Ss7j66" onclick="window.open(this.href);return false; Sometime or other I have learnt that Excel recognises a single vbLf to split up lines of text within a cell. But it turns out to be bit more complicated than that if you want the text string in the Windows Clipboard to come out as you want it.
By trial and error, for something like column 2 in that last screen show, I found that I need something like this as the string to put in the clipboard before a Worksheets .Paste
"A" & vbCr & """" & "X" & vbLf & "Y" & """" & vbCr & vbLf & "C"
In other words, this is what is in the clipboard
A vbCr " X vbLf Y " vbCr vbLf C
Solution:
3 things seem to be needed to get split lines in a cell when pasting after putting things into the (Windows) Clipboard:
_ The vbLf .............. Edit: see 3rd post - this may not be important..
_ The complete cell text must be enclosed in a single quote pair
_ The typical preceding vbCr & vbLf pair must be reduced to a single one, either of them will do. ( You can optionally reduce that pair elsewhere to a single one and it won’t effect the pasted out text, but more typically a pair is seen. In other words , for normal text to come out in a line in each vertical cell, either vbCr , vbLf , or vbCr & vblf will do as the line/ row separator/ indicator. But for the case of wanting a cell to have split line text, then you strangely must have only one preceding. Otherwise you sometimes** get an extra blank cell inserted before the cell with the split lines. **Note: I don’t always see this need to reduce the preceding two things to one. I have not managed to track down yet why this phenomena only sometimes occurs…
I don’t know if my final solution is perfectly correct. So far it is getting me what I want.
( A sample of some of my investigations and measurements are here: https://tinyurl.com/ufv5dxo" onclick="window.open(this.href);return false; )
I have a feeling based on some of my other experiments that the Excel Worksheets .Paste actually gets its information from the windows clipboard. I think that might explain why the code line
____.Copy Destination:=____
works quite quickly. I think that is doing the same as the code lines
___.Copy
___.Paste
I think either of those somehow set up a link by reference to the clipboard which is similar to the link set up when you have Excel open but copy something manually from somewhere else, which then uses the Windows Clipboard…
But I am digressing into an area that can send you mad … trying to understand Microsoft clipboards and their interactions…..
I have “the urge” to make some detailed notes , possibly do a Blog one day to help go a small way to unravel the chaotic spaghetti of interactions in the Office, Window and Excel Clipboards. Some of the information from that will come out of some of the posts here at Eileen’s lounge. So I am keen to get any info / input form anyone here on the mysteries of the Microsoft clipboard. I get the feeling sometimes that Microsoft themselves long since gave up trying to understand it..
But my priority in this post is to know if I have got it right , the Solution to Text string syntax in Windows clipboard, to paste multi line text into Excel cells.
Thanks
Alan
