VBA to copy and save to clipboard

ABabeNChrist
SilverLounger
Posts: 1868
Joined: 25 Jan 2010, 14:00
Location: Conroe, Texas

VBA to copy and save to clipboard

Post by ABabeNChrist »

I am trying use code to save text in cell A1 to clipboard so I can paste as needed in other target locations
Here is what I have so far

Code: Select all

    With ActiveSheet
        .Range("A1").Select
        .Copy
        .PutInClipboard
    End With
   Range("A2").Select

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

Re: VBA to copy and save to clipboard

Post by HansV »

Do you have a userform in your workbook? If not, see the steps at the end of this reply.
Use code like this:

Code: Select all

    Dim objData As MSForms.DataObject
    Set objData = New MSForms.DataObject
    objData.SetText Range("A1").Text
    objData.PutInClipboard
If your workbook does not contain a userform, activate the Visual Basic Editor.
Select Insert > UserForm.
Right-clicl the userform in the Project Explorer pane on the left hand side, under Forms.
Select "Remove UserForm1" from the context menu.
Answer No to the question if you want to export the userform.
Although you have removed the userform, the workbook still has a reference to the Microsoft Forms 2.0 Object Library. This is needed to be able to use DataObject in the code listed above.
Best wishes,
Hans

ABabeNChrist
SilverLounger
Posts: 1868
Joined: 25 Jan 2010, 14:00
Location: Conroe, Texas

Re: VBA to copy and save to clipboard

Post by ABabeNChrist »

That worked perfectly thank you

snb
4StarLounger
Posts: 547
Joined: 14 Nov 2012, 16:06

Re: VBA to copy and save to clipboard

Post by snb »

If you use the method .copy, the text will be stored into the clipboard.
In that case you don't need .Putinclipboard at all.
Only when a .copy method is lacking you can use .PutinClipboard instead.
You only need .getfromclipboard to retrieve it.

If you use the Userform's CLSID (Class Identification), loading a reference isn't necessary.

Code: Select all

Sub M_snb()
  cells(1).copy
  With GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
    .GetFromClipboard
    msgbox .GetText
  End With
End Sub
If method .copy isn't available:

Code: Select all

Sub M_snb()
  With GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
     .SetText "This is what I mean"
     .PutInClipboard
    
    .GetFromClipboard
    msgbox .GetText
  End With
End Sub

ABabeNChrist
SilverLounger
Posts: 1868
Joined: 25 Jan 2010, 14:00
Location: Conroe, Texas

Re: VBA to copy and save to clipboard

Post by ABabeNChrist »

Thank you snb

User avatar
DocAElstein
4StarLounger
Posts: 545
Joined: 18 Jan 2022, 15:59
Location: Re-routing rivers, in Hof, Beautiful Bavaria

Re: VBA to copy and save to clipboard

Post by DocAElstein »

Hi,

My first thoughts were like what snb said.
But then, when I saw what HansV did, I thought … Ahh – HansV probably did it like he did to ensure that the .Text, ( that is to say what you actually see ) goes into the clipboard.

So my second thoughts were then that probably the Excel .Copy puts the .Value or .Value2 of the cell into the clipboard, ( .Value or .Value2 may not always be what you see, depending on the formatting you have applied to the Excel Cell.) But that second thought was wrong. - When I just checked, I do see that indeed, what snb did does give the .Text, that is to say what you actually see in the cell. In other words, the Excel .Copy puts the .Text of the cell into the clipboard. I was a bit surprised by that, as I thought it might take the more "low level"/ findamental version, which is usually regarded as the .Value2
Interesting.


(One thing to note is that we are playing around here with the text that is held in the Windows Clipboard, I think, at least when we do stuff via the DataObject thing
On the other hand, the Excel .Copy will do a few things, including
_ putting text in the windows clipboard,
_ somehow setting a reference for the Office and Excel clipboards to know where to get format information from,
_ ..... and only god knows what else. - No one fully understands the spaghetti mess of Microsoft Clipboards anymore, as far as I can tell, and since about 10 years they have bugs in them that I doubt will ever be solved.)

Alan
I seriously don’t ever try to annoy. Maybe I am just the kid that missed being told about the King’s new magic suit, :(

User avatar
SpeakEasy
4StarLounger
Posts: 536
Joined: 27 Jun 2021, 10:46

Re: VBA to copy and save to clipboard

Post by SpeakEasy »

>and only god knows what else

For a range Excel puts about 30 different formats of the data on the clipboard in the following order

excelclipboardformats.png
it puts then in in order of what it thinks we might call 'popularity'. It is then up to the consuming app to iterate through the list and decide which format(s) it can paste.

>a reference for the Office and Excel clipboards ...

No such thing. The 'Office clipboard' and 'Excel clipboard' are just limited viewers of the Windows clipboard. As indicated above, the source application has the responsibility of putting all the formats it deems necessary onto the clipboard. The consuming app iterates that list of formats to see which ones it supports.

>... to know where to get format information from

the data is on the clipboard
You do not have the required permissions to view the files attached to this post.

User avatar
DocAElstein
4StarLounger
Posts: 545
Joined: 18 Jan 2022, 15:59
Location: Re-routing rivers, in Hof, Beautiful Bavaria

Re: VBA to copy and save to clipboard

Post by DocAElstein »

Thanks SpeakEasy for that extra input, interesting, I have never been able to find much documentation on this.

Here 3 Bits of response to that
_._______

_ Bit 1)
SpeakEasy wrote:
13 Nov 2022, 12:45
....
For a range Excel puts about 30 different formats of the data on the clipboard in the following order....
Which Clipboard? ( I think we have a Windows Clipboard, an Office Clipboard and an Excel Clipboard, and possibly some others I don’t know about yet? )



_.________

_ Bit 2)
SpeakEasy wrote:
13 Nov 2022, 12:45
...
>a reference for the Office and Excel clipboards ...

No such thing
.
That was a bit of an uneducated Guess on my behalf based on two things:
_ Some smarter people told me that, (but they never seemed too sure)
_ The following phenomena suggested to me that it might be true: Open two excel files. One file should have some ranges with pretty formatting, colours etc. Copy a pretty range and paste it in the other workbook. What you get will look typically very similar to what you copied. Also have a look at the Paste options that you have:
PasteOptions.JPG
As you might expect you have a few options

Now, repeat that all, but after you copy the pretty range, close the workbook from which you copied the pretty range. If you now paste in the remaining open workbook, you appear to only get a simple text value paste, and in the options available, quite a few are missing, ( greyed out )
PasteOptionsCopyfromWorkbookClosed.JPG
So this last phenomena suggested to me that the formats could only be found if the workbook was still open, which suggested to me that the format was not in a clipboard, but possibly a reference to where to find them in the workbook, but as the workbook is closed we cannot reference formatting on a closed workbook

_._______________________

_ Bit 3) ( related a bit to _ Bit 2)
SpeakEasy wrote:
13 Nov 2022, 12:45
the data is on the clipboard
Which Clipboiard, and how do I get at the information in it so as to paste in the full pretty formatted version which I copied before I closed the workbook…



Alan
You do not have the required permissions to view the files attached to this post.
I seriously don’t ever try to annoy. Maybe I am just the kid that missed being told about the King’s new magic suit, :(

User avatar
SpeakEasy
4StarLounger
Posts: 536
Joined: 27 Jun 2021, 10:46

Re: VBA to copy and save to clipboard

Post by SpeakEasy »

>I think we have a Windows Clipboard, an Office Clipboard and an Excel Clipboard

No, just the Windows clipboard. The Office clipboard is just a specific view

>The following phenomena
This is all to do with how the Windows clipboard works. Without going into all the nitty gritty, the clipboard is owned at any particular point in time by the window (or task) that last placed data in the clipboard. In conjunction with this, an application can request that data it is putting on the clipboard use something called 'deferred render' mode - this means that the data is not actually put on the clipboard at that time, the intended purpose being related to performance - it is only provided when a request to paste the data is made. Data marked for deferred render is removed from the clipboard when the owning window is closed. Excel seems to mark quite a lot of data for deferred rendering ...

User avatar
DocAElstein
4StarLounger
Posts: 545
Joined: 18 Jan 2022, 15:59
Location: Re-routing rivers, in Hof, Beautiful Bavaria

Re: VBA to copy and save to clipboard

Post by DocAElstein »

I find that information very interesting, thanks for sharing that. (I find it interesting as having a slightly better idea of what actually is going on and happening has often helped me solve what were sometimes considered by most people as unsolvable problems… )
( I wonder if no one at Microsoft knows what you are suggesting is happening, Lol, and explains why they no longer can solve some of the problems and bugs )

I suppose in some ways, my original suggestion… ( somehow setting a reference for the Office and Excel clipboards to know where to get format information from ) was not so far off.
The information never gets put in the clipboard when the window/ task/ controlling application is closed, but it would have been. My suggestion that it was holding references in a clipboard so as to know where to get the information was wrong. But something somewhere is telling the clipboard when ( and presumably where from ) to get the information in certain cases of time offset deferred render to the Paste time , such as in the case of Excel having done the copy action.

_.________________________________
SpeakEasy wrote:
14 Nov 2022, 00:20
Alan >I think we have a Windows Clipboard, an Office Clipboard and an Excel Clipboard
SpeakEasy >No, just the Windows clipboard. The Office clipboard is just a specific view
I am glad you said that and not me, Lol. Watch your back, though. If I ever say things like that then either I am banned on the spot and all my posts deleted, or more often, on a deferred render' type mode, I am put on a list of marked men to be killed off subtly later when no one is watching anymore

_.________________________________________


So what is this then, and what is it telling me:
_____Clipboard.JPG
__Clipboard.JPG
Is it … (i) The viewable bit of the text string part ( version ) of the Clipboard, or a small part of a picture version*** that is always put in the clipboard no matter what the window/ task/ controlling application is
(ii) It is not, (as Microsoft and most everyone else thinks), the Office clipboard. It is just a convenient list of the various Microsoft window/ task/ controlling application that have either put in the clipboard, (or will possibly later in the case of the Application having the deferred entry mode)

______________________________________________________________

*** Interesting observation that supports some of your suggestions of what is actually going on. I went on to copy some screenshots using snipping tool.
pics can still be pasted.JPG
I closed snipping tool, and the pics are still shown in that list and they can be pasted. So that suggests that snipping tool is not on the deferred entry mode, and so the pics were put straight away in the clipboard
You do not have the required permissions to view the files attached to this post.
I seriously don’t ever try to annoy. Maybe I am just the kid that missed being told about the King’s new magic suit, :(

User avatar
SpeakEasy
4StarLounger
Posts: 536
Joined: 27 Jun 2021, 10:46

Re: VBA to copy and save to clipboard

Post by SpeakEasy »

>So what is this then, and what is it telling me:

Ok, so any application can monitor (and display to the best of their ability) the contents of the clipboard*. This used to be by inserting a clipboard viewer into the clipboard viewer chain - but this is not as robust as it might be, and is the source of some of the 'bugs' that you refer to (e,g. if applications fail to maintain the clipboard viewer chain properly or if a window in the clipboard viewer chain stops responding to messages). The current best practice method is to create a Clipboard Format Listener, which then receives notifications every time the clipboard changes. Applications are free to display the current contents of the clipboard that they understand in order to show the user what is available to paste. And that's what the Office clipboard is: simply a bespoke view of the Windows clipboard, not an actual separate clipboard. There IS a minor wrinkle to this - the Office clipboard viewer maintains a history of clipboard entries (the clipboard itself can only ever hold one entry). Again without going into the nitty gritty, this history consists of data formats that the Office applications know how to render. This can cause some minor inconsistencies ...

>But something somewhere is telling the clipboard
Indeed. Specifically, the WM_RENDERFORMAT message. Let's get really technical: https://learn.microsoft.com/en-us/windo ... nderformat

* this, by the way, is why you can find a plethora of applets that purport to show the contents of the clipboard such as FreeClipView and ClipDiary (back in the day - XP - Microsoft had their own proper clipboard viewer, but they got rid of it; I suspect because it used the slightly unstable clipboard viewer chain mentioned above; the replacement in Windows 10, which only works if you turn on clipboard history - yes they added a history capability to the OS - lacks features ...)

User avatar
DocAElstein
4StarLounger
Posts: 545
Joined: 18 Jan 2022, 15:59
Location: Re-routing rivers, in Hof, Beautiful Bavaria

Re: VBA to copy and save to clipboard

Post by DocAElstein »

I like to have these little snippets of “insider type” information, Thx. ( Or maybe its not insider information, but just information most everyone has forgot ? )
I don’t always understand everything exactly , or not yet, but having it all in my head when I am later thinking about things clipboard or solving issues with them later could be useful I think. ***

_.___

I suppose these two things..
_ the Office clipboard is: simply a bespoke view of the Windows clipboard
_... a minor wrinkle … the Office clipboard viewer maintains a history of clipboard entries …., this history consists of data formats that the Office applications know how to render

in conjunction with the fact that what ever the thing is, we have to call it something, is excuse enough to call it the Office Clipboard? After all, if this thing/ entity is responsible for getting those pictures to paste in one after the other, even after I long since copied them before and other things “on top of them” I have copied since, then its doing a better job as a Clipboard then the real Clipboard is, if the real Clipboard itself can only ever hold one entry

Your explanation of …. a Clipboard Format Listener, which then receives notifications every time the clipboard changes. Applications are free to display the current contents of the clipboard that they understand in order to show the user what is available to paste …. is telling me I suppose that when I was using snipping tool, then at that time, for some reason or another, although I did not do it consciously, the windows clipboard , or probably what we should call the clipboard was temporarily used, and the office coding that listens for format, and changes in the (windows) clipboard, (what we might call a sort of (office) clipboard, caught it, and ended up with enough information to do a pretty good imitation of a multi entry holding clipboard.


( *** Armed with this new knowledge, when I have time later this winter, I will re visit a Thread I hijacked a few years back https://stackoverflow.com/questions/250 ... 7#54960767 . I don’t know why that post of mine has not been deleted yet, so maybe I need to add something to achieve that there this Winter: (usually my few posts at stackoverflow get a Gold award for high views ( curiosity I expect ), shortly before then some psycho moderator deletes them.)
I can’t remember what I was on about there, it may have been to do with figuring out what and where the Data Object is involved with. That could be very useful, I might be able to figure out how to use the ?????? Clipboard to do even more interesting things with manipulating Excel Ranges.
I assumed up until now that the Data Object was interacting with the (windows) clipboard. Maybe it is. But I am not sure now. I will have to think about that again when I have a lot more time )

_.__________________________


So we have established that the so called “Office Clipboard” is not a clipboard, but more like a sort of a (Office) Clipboard, what we might call or name , for example, the Office Clipboard. So that is nicely clear now. (Seriously it/ that makes sense to me, even if it sounds strange)

_.....
I expect we should be able to say something now about the so called Excel Clipboard. I think I will sleep on that one and tell us tomorrow, if you don’t set me straight before.
Last edited by DocAElstein on 16 Nov 2022, 11:23, edited 1 time in total.
I seriously don’t ever try to annoy. Maybe I am just the kid that missed being told about the King’s new magic suit, :(

User avatar
DocAElstein
4StarLounger
Posts: 545
Joined: 18 Jan 2022, 15:59
Location: Re-routing rivers, in Hof, Beautiful Bavaria

(MS Windows) Clipboard. Wot it is, (and wot aint, (a clipboard)): You heard it first at Eileen’s Lounge!

Post by DocAElstein »

So how about…. there is no such thing as an Excel Clipboard, and not even anything that might be considered as similar.

This thing, Let Application.CutCopyMode = False , is often said to clear the Excel Clipboard.
How about: It takes it out of the (Windows) Clipboard. So if you do a .Paste nothing will happen, as the (Windows) Clipboard is now empty, and the stupid thing is not as clever as that “Office thing that is similar to a clipboard” (that thing what we might call, for want of a better name, the Office Clipboard) , so it wont remember what was copied before. I note that Let Application.CutCopyMode = False makes the clipboard symbol go from orange to white. So maybe that is an indication of if the (Windows) Clipboard is occupied or not.

_.____________________________

That Office thing that is similar to a clipboard” (that thing what we might call, for want of a better name, the Office Clipboard) , seems to hold a copied range including the format, long after other things have been copied , even if Let Application.CutCopyMode = False was used a few times, and even if the workbook from which it was copied has long since been closed. In fact, close Excel, go into Word, take a peek into the Office thing that is similar to a clipboard” (that thing what we might call, for want of a better name, the Office Clipboard) there, and you can also pick that copied Excel range out, and paste that copied Excel range into word. But now things are getting a bit inconsistent and quirky: I am finding that sometimes it will give me the formats and sometimes not, - Oh dear, Microsoft got their clipboard viewer chain in a tangle I expect.

_.____________________


I have often regarded the .Paste as using the Windows Clipboard, whereas I regarded the Excel .PasteSpecial function as using the Excel Clipboard.

Maybe .Paste is using the (Windows) Clipboard whereas the Excel .PasteSpecial function is just some coding to help you choose which version of the copy in the the (Windows) Clipboard that you use.

_.________________

It could be very useful to programmatically with VBA get at all the things at will in the Office thing that is similar to a clipboard” (that thing what we might call, for want of a better name, the Office Clipboard). Maybe doing that at a very low level with other software is what all those plethora of applets that purport to show the contents of the clipboard are doing?
I always thought the DataObject thing was playing around with the Windows Clipboard. Maybe its not. Maybe it is playing around with the Office thing that is similar to a clipboard” (that thing what we might call, for want of a better name, the Office Clipboard). Or maybe it is doing something similar to the Office thing that is similar to a clipboard” (that thing what we might call, for want of a better name, the Office Clipboard) , but in a more limited form, mostly concerning itself with simple text…. That could go some way to explaining whatever fantasies I was dreaming about there
I seriously don’t ever try to annoy. Maybe I am just the kid that missed being told about the King’s new magic suit, :(

User avatar
SpeakEasy
4StarLounger
Posts: 536
Joined: 27 Jun 2021, 10:46

Re: VBA to copy and save to clipboard

Post by SpeakEasy »


User avatar
DocAElstein
4StarLounger
Posts: 545
Joined: 18 Jan 2022, 15:59
Location: Re-routing rivers, in Hof, Beautiful Bavaria

"office clipboard"

Post by DocAElstein »

Thx SpeakEasy for that link, its interesting. It is one of those useful links that sadly you can never find anymore as it would be swamped in a search by the overfull internet :(
Sadly these links towards the start don’t work anymore, :(
http://support.microsoft.com/default.aspx/kb/221190
http://downloads.techrepublic.com.com/abstract.aspx?docid=355943

I expect then they are lost to mankind, unless anyone has an idea how to find them??
(Web archive org does not help me this time. Although that archive is often very useful to find a lot of stuff, it seems to not find much old Microsoft stuff. Strange that)

The Guy says, he wanted to figure out the Office Clipboard, ( note, for want of a better word he calls it a clipboard, the office clipboard, but he does go on to say about the sort of stuff you mentioned….Office attaches itself to the Clipboard viewer chain …. etc, so its listening and can make its own interpretation / copy of the stuff being copied to the (windows) clipboard.)
So that is very helpful to get some confirmation, or at least another opinion, in writing, of what is actually going on.


After the first few paragraphs I am totally lost, - he’s talking about binaries and dlls which I have learnt are filler words used by smart people, and can mean many different things in different contexts and so helps make an article not understandable to anyone except those that already know what he is talking about.
The article suggest that he is manipulating the office clipboard, which I thought could be a very useful thing to do, but I am bugged if I can make any sense out of how or what he is doing there.
Possibly its one of these academic type articles that shows the evidence that the guy has done something useful and figured it out, but doesn’t give any secrets away about how or what he is doing , done or what can be done with what ever it is he has done or is talking about.
Or it’s just aimed at the level of a highly qualified computer professional, which I am not.
My usual ignorance I suppose. :(

Thx anyway, it is very useful to have these things for when I finally try to figure it all out, perhaps later when I have a lot more time.


Alan
I seriously don’t ever try to annoy. Maybe I am just the kid that missed being told about the King’s new magic suit, :(

User avatar
DocAElstein
4StarLounger
Posts: 545
Joined: 18 Jan 2022, 15:59
Location: Re-routing rivers, in Hof, Beautiful Bavaria

Re: VBA to copy and save to clipboard

Post by DocAElstein »

HansV wrote:
10 Nov 2022, 19:44
...
Use code like this:

Code: Select all

    Dim objData As MSForms.DataObject
    Set objData = New MSForms.DataObject
    objData.SetText Range("A1").Text
    objData.PutInClipboard
snb wrote:
11 Nov 2022, 08:59
...use the method .Copy, the text will be stored into the clipboard.....
Hello,
I just tracked down the cause an annoying problem, and thought it could be relevant and useful to mention it here.
Those two quotes from the start of this thread are basically saying that we can use either,
_ the .Copy method as shown by snb
or
_ some coding of the DataObject way as shown by Hans
Both do what the OP originally wanted… which was … put the text in cell A1 to clipboard so I can paste as needed in other target locations
All well and good, but I just tripped up doing something similar using the .Copy way.
There is a very small but subtle difference in the two methods. Its one of those things you mostly would never notice, but then your ignorance catches you out some time later.
The first method mentioned by Hans does pretty well it. The .Copy mentioned by snb has the extra feature of tacking on an extra 2 characters at the end, vbCr & vbLf. ( It does that as standard I guess for data from any row in Excel as then if you are copying more than one row, the vbCr & vbLf, (which is the typical conventional line separator in computing), is conveniently used by Excel to separate rows. Makes sense I suppose: Rows are lines by another name.)
The majority of things have some tolerance one way or another if you paste in text with an extra vbCr & vbLf on the end. But not everything. I was copying from a list in excel and pasting it somewhere. That somewhere did not like the extra vbCr & vbLf
(Not a problem now I know about it: I can just add a button to copy using the DataObject way or better still let an event code run off a cell selection on the column I am copying from, and the coding that is set off will use the DataObject way)

Here is a quick demo just to illustrate what I am talking about:
The uploaded file has some simple text in the first cell
abc

Run the macro below. It analyses the text held in the clipboard from the two different ways discussed of putting it in there.
It gives a few outputs , that show the difference.
DataObjectWayOrCopyWayForACell.JPG

Code: Select all

Sub WotsInTHEClipboard()
Dim objDataObject As Object
Set objDataObject = GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}") '    http://web.archive.org/web/20200124185244/http://excelmatters.com/2013/10/04/late-bound-msforms-dataobject/
Dim StringBack As String ' This has the entire text held for the range 
ThisWorkbook.Worksheets.Item(1).Range("A1").Copy '  Or as alternative copy manually - it usually has the same effect, copies from the Excel worksheet, I think
 objDataObject.GetFromClipboard
 Let StringBack = objDataObject.GetText()
Call WtchaGot_Unic_NotMuchIfYaChoppedItOff(StringBack)
'Stop  '  From  VB Editor hit  Ctrl+g  to get the Immediate window. You should see   "abc" & vbCr & vbLf
''
' objDataObject.Clear
 objDataObject.settext ThisWorkbook.Worksheets.Item(1).Range("A1").Value2
 objDataObject.putinclipboard
 objDataObject.GetFromClipboard
 Let StringBack = objDataObject.GetText()
Call WtchaGot_Unic_NotMuchIfYaChoppedItOff(StringBack)
'   In the Immediate window you should see      "abc"
End Sub



Alan


_.________________________________

Edit: This is the simple coding to get me out of my problem. It copies the text from a selected cell in column C to the clipboard without any trainling vbCr & vbLf. ( Code goes in the worksheet object code module of the worksheet that has a list of text that I want to copy in column C . ( I am only intersted in copying text from one cell at a time) )

Code: Select all

Private Sub Worksheet_SelectionChange(ByVal Target As Range) '  https://eileenslounge.com/viewtopic.php?p=303007#p303007
    If Target.Cells.Count <> 1 Or Target.Column <> 3 Then Exit Sub ' I am only intersted in copying the text from a single cell in column C
Dim objDataObject As Object
Set objDataObject = GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}") '    http://web.archive.org/web/20200124185244/http://excelmatters.com/2013/10/04/late-bound-msforms-dataobject/
 objDataObject.SetText Target.Value2
 objDataObject.putinclipboard
End Sub
You do not have the required permissions to view the files attached to this post.
Last edited by DocAElstein on 08 Jan 2023, 15:49, edited 3 times in total.
I seriously don’t ever try to annoy. Maybe I am just the kid that missed being told about the King’s new magic suit, :(

snb
4StarLounger
Posts: 547
Joined: 14 Nov 2012, 16:06

Re: VBA to copy and save to clipboard

Post by snb »

@Doc

Thanks !

To illustrate the same findings:

Code: Select all

Sub M_snb()
  With GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
     Cells(1).Copy
     .GetFromClipboard
     MsgBox "A1 " & vbTab & Cells(1) & vbTab & Len(Cells(1)) & vbLf & "clipboard " & vbTab & .GetText & vbTab & Len(.GetText)

     .settext ""
     .PutInClipboard
     MsgBox "clipboard " & vbTab & .GetText & vbTab & Len(.GetText)
   End With
End Sub

User avatar
SpeakEasy
4StarLounger
Posts: 536
Joined: 27 Jun 2021, 10:46

Re: VBA to copy and save to clipboard

Post by SpeakEasy »

>Both do what the OP originally wanted… which was … put the text in cell A1 to clipboard so I can paste as needed in other target locations

No, no they don't. At least, not quite.

The .Copy method puts a copy of the object you are referencing onto the clipboard, in this case a Range.- and then synthesizes all the supported formats. including the Text format (which it does as the synthesized CF_TEXT format, which always ends CR LF [in fact it is even more clever than that, in that it actually separates Rows with CR LF and columns by Tab])

The DataObject's .SetText method exposed to VBA, on the other hand, expects a straightforward text string

(Oh, and one other thing: .PutInClipboard can perform ... unexpectedly ... on occasion if Windows file explorer is open.)

User avatar
DocAElstein
4StarLounger
Posts: 545
Joined: 18 Jan 2022, 15:59
Location: Re-routing rivers, in Hof, Beautiful Bavaria

Re: VBA to copy and save to clipboard

Post by DocAElstein »

Hi
Thanks SpeakEasy for correcting my impreciseness. It’s very helpful and appreciated – I think I am slowly getting to understand what The ( Microsoft Windows ) Clipboard is all about.

So… to try and correct myself …
.Copy
The .Copy is going to tell the Clipboard that it …. ( it being VBA I think in this case, or possibly Excel as the controlling/ "owning thing" ) …. this it is in charge of the clipboard for the time being. This it is a deferred render entry thing, but if and when it does actually put stuff in The ( Microsoft Windows ) Clipboard, then in the case of a range it is gonna be quite a few formats, ( maybe about 30 ) or maybe not?: I mean, as its a deferred render entry thing it might decide to put different things in The ( Microsoft Windows ) Clipboard depending on what is done later to trigger the thing to do what it is deferring doing when it’s the controlling/ "owning thing" ?
So that is maybe something close to what the .Copy is doing.

.SetText and .PutInClipboard
( I am not sure if this is a deferred entry thing or not ? ).
This can’t do anything much other than put text somewhere since that is all you give it: You give it a simple text string. That’s it.

_.___________________________________________

I am not 100% sure what you are saying by your reference synthesized CF_TEXT format.
This is my guess as to what you are saying. :

If I use the .GetFromClipboard and .GetText() from within VBA, then what I get will depend on what was the controlling "owning" thing of the The ( Microsoft Windows ) Clipboard at the time ….
Let me consider the 2 cases relevant here…
_ case1
If .Copy was the owning controlling thing of The ( Microsoft Windows ) Clipboard, then, when it sees the request of .GetFromClipboard from within VBA, it may not quite have the format requested. ….I am trying to understand that link you gave ….. I will take a guess that what is going on in this case1 is this:
It takes one of the formats on the first column from that link you gave which it does have* (*or will have – its deferred entry ....) . It than either makes one of the formats on the second column from that link, or maybe makes one that is not listed there. I don’t know. I do know, and agree with this bit that you said…. it actually separates Rows with CR LF and columns by Tab…..
I have seen this many times and we have played around with this when coming up with some neat simple alternative ways to manipulate ranges and text files…
Here is a Simple example we have seen many times at Eileen’s lounge.. , just to help demo what I am talking about ..
Take a simple square 2x2 range in A3:B4, ( https://i.postimg.cc/kGKH9wmv/abcdeedum-In-A3-B4.jpg )
a b
c deedum


Now run this ( in the uploaded file)

Code: Select all

 Sub WotGetGotGiven_abcdeedumA3B4()    '   https://eileenslounge.com/viewtopic.php?p=303015#p303015
Dim objDataObject As Object
Set objDataObject = GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}") '    http://web.archive.org/web/20200124185244/http://excelmatters.com/2013/10/04/late-bound-msforms-dataobject/
Dim StringBack As String '
ThisWorkbook.Worksheets.Item(1).Range("A3:B4").Copy '  Or as alternative copy manually
 objDataObject.GetFromClipboard
 Let StringBack = objDataObject.GetText()
Call WtchaGot_Unic_NotMuchIfYaChoppedItOff(StringBack)
'   Result example    "a" & vbTab & "b" & vbCr & vbLf & "c " & vbTab & "deedum" & vbCr & vbLf
End Sub 
Example output will be something like
"a" & vbTab & "b" & vbCr & vbLf & "c " & vbTab & "deedum" & vbCr & vbLf
( https://i.postimg.cc/P5yVyPyK/Wot-Get-G ... -A3-B4.jpg )


What is not clear to me yet, is what/which/ if the listed CF_ things in the link you gave are being used in that demo coding above.
Clarity on that if you could give it would be very enlightening and very welcome

_.__

_ case2
If the DataObject is the owning controlling thing of The ( Microsoft Windows ) Clipboard, ( in other words you did the .SetText and .PutInClipboard thing to put text in The ( Microsoft Windows ) Clipboard) , then …..you put a text in and you will get with the .GetFromClipboard and .GetText() the same text back. Simple as that. ( I am still not sure what of the CF_ things are used though in this case either )

_._____________________________________________________________________________


< ….. .PutInClipboard can perform ... unexpectedly ... on occasion if Windows file explorer is open ………>
Yeah, it must be getting on for almost 10 years now since that/those Bug/s came in. ( A work around that seems often to be used is API things to do stuff with the clipboard as an alternative to the DataObject, I have a few links I can’t find just now, I will edit and add them if I stumble across them later. )



_.___

Thanks again for the extra input, SpeakEasy, ( and snb also )
Alan
You do not have the required permissions to view the files attached to this post.
I seriously don’t ever try to annoy. Maybe I am just the kid that missed being told about the King’s new magic suit, :(