Syntax in Windows clipboard, to Paste multi lines in Excel

User avatar
Doc.AElstein
BronzeLounger
Posts: 1499
Joined: 28 Feb 2015, 13:11
Location: Hof, Bayern, Germany

Syntax in Windows clipboard, to Paste multi lines in Excel

Post by Doc.AElstein »

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;
2 lines in cell A2 or B2.JPG
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
You do not have the required permissions to view the files attached to this post.
Last edited by Doc.AElstein on 20 Dec 2019, 18:27, edited 3 times in total.
I am having difficulty logging in with this account just now.
You can find me at DocAElstein also

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Syntax in Windows clipboard, to Paste multi lines in Exc

Post by HansV »

Just one remark: instead of vbCr & vbLf, you can use vbCrLf or, alternatively, vbNewLine.
Best wishes,
Hans

User avatar
Doc.AElstein
BronzeLounger
Posts: 1499
Joined: 28 Feb 2015, 13:11
Location: Hof, Bayern, Germany

Re: Syntax in Windows clipboard, to Paste multi lines in Exc

Post by Doc.AElstein »

Thanks Hans
I have seen vbCrLf before.
I had forgoten about the vbNewLine. ( So I just added that to a function I have that analyses string contents.. ( https://tinyurl.com/v8qhtcr" onclick="window.open(this.href);return false; ,
Sub WtchaGot(ByVal strIn As String).txt
[/size] ) )


I notice that if either vbCrLf or vbNewLine are added to a string , then they are always seen as a two character string of vbCr & vbLf , that ist to say character 13 and character 10 .
So I am wondering if in fact they are not actually characters, but just some sort of function which if you use it converts to vbCr & vbLf

Or maybe that is just sometimes the case. I don’t know


I just had a quick look, and initially the use of vbCrLf or, vbNewLine seems not to have any effect on my issue, but whilst looking just now at that, I did notice that, in fact, it is those enclosing quote pairs which are important , rather than the use of vbLf, when using the windows clipboard to paste into cells

I also notice now that pasting into a cell for multi line text will accept vbCr , vbLf or vbCr & vbLf.
So I am not sure now where I ever got the idea that a cell recognises the vbLf as the line separator. I am sure I did get that idea. And I am sure there was a good reason for it. I can’t remember now what it was… :scratch:

The macro below always gives me the result I want in a cell. https://imgur.com/rYoKm0s" onclick="window.open(this.href);return false; It uses either vbLf or vbCr or vbCr & vbLf or vbNewLine or vbCrLf. The reults are all the same.
QuotePairDoesTheTrick.JPG
It seems to be the enclosing quote pair that does the trick. .. I am sure there was some importance of the vbLf for multi lines in a cell. When the penny drops , if it ever does, I will post back and say what that significance was... it seems not relevant for my current issue, ( I just made an Edit note in the first post ) But I will probably leave that vbLf as the sepearator within the qoute pair, if nothing else just as a convention to help me distinguisch from the line seperators for the rows, which I will leave as mostly vbCr & vbLf or sometime as vbCr

Code: Select all

Sub TestCarriageReturnLineFeed()
Dim strTest
 Let strTest = vbNewLine
 Call WtchaGot(strTest) '  ---  vbCr & vbLf
 Let strTest = vbCrLf
 Call WtchaGot(strTest) '  ---  vbCr & vbLf
 
Dim objDataObject As Object: Set objDataObject = GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
 ActiveSheet.Cells.Clear '
 objDataObject.SetText "A" & vbCr & vbLf & """" & "X" & vbLf & "Y" & """" & vbCr & vbLf & "C"
 objDataObject.PutInClipboard
 ActiveSheet.Paste Destination:=ActiveSheet.Range("A1")
 objDataObject.SetText "A" & vbCr & vbLf & """" & "X" & vbCrLf & "Y" & """" & vbCr & vbLf & "C"
 objDataObject.PutInClipboard
 ActiveSheet.Paste Destination:=ActiveSheet.Range("B1") '
 objDataObject.SetText "A" & vbCr & vbLf & """" & "X" & vbNewLine & "Y" & """" & vbCr & vbLf & "C"
 objDataObject.PutInClipboard
 ActiveSheet.Paste Destination:=ActiveSheet.Range("C1")
 objDataObject.SetText "A" & vbCr & vbLf & """" & "X" & vbCr & vbLf & "Y" & """" & vbCr & vbLf & "C"
 objDataObject.PutInClipboard
 ActiveSheet.Paste Destination:=ActiveSheet.Range("D1")

' I also notice now that pasting into a cell for multi line text will accept vbCr , vbLf  or vbCr & vbLf
 ActiveSheet.Range("F2").Value = "X" & vbCr & vbLf & "Y"
 'Call WtchaGot(Range("F2").Value) '  ---  "X" & vbCr & vbLf & "Y"
 ActiveSheet.Range("G2").Value = "X" & vbLf & "Y"
 'Call WtchaGot(Range("G2").Value) '  ---  "X" & vbLf & "Y"
 ActiveSheet.Range("H2").Value = "X" & vbCrLf & "Y"
 'Call WtchaGot(Range("H2").Value) '  ---  "X" & vbCr & vbLf & "Y"
 ActiveSheet.Range("I2").Value = "X" & vbNewLine & "Y"
 'Call WtchaGot(Range("I2").Value) '  ---  "X" & vbCr & vbLf & "Y"

 ActiveSheet.Range("J1").Value = "A"
 ActiveSheet.Range("J2").Value = "X" & vbCr & vbLf & "Y"
 ActiveSheet.Range("J3").Value = "C"
 ActiveSheet.Range("J1:J3").Copy
Dim strBack As String
 objDataObject.GetFromClipboard: Let strBack = objDataObject.GetText()
 Call WtchaGot(strBack) '   ---     "A" & vbCr & vbLf & """" & "X" & vbCr & vbLf & "Y" & """" & vbCr & vbLf & "C" & vbCr & vbLf

End Sub
You do not have the required permissions to view the files attached to this post.
I am having difficulty logging in with this account just now.
You can find me at DocAElstein also