Clipboard for multiple cells

[YasserKhalil]

Hello everyone
I am trying the following code

Code: Select all

Sub Test_Clipboard()
    Dim e
    
    With New DataObject
        For Each e In Array("A1", "B1")
            .SetText Range(e).Text
            .PutInClipboard
        Next e
    End With
End Sub

But when opening the clipboard I found the first cell contents only
Any idea how to use multiple cells using the clipboard?

[HansV]

How about

Range("A1:B1").Copy

[YasserKhalil]

First these are sample cells. The cells are not adjacent
Second I would use the copied from clipboard to another program

[HansV]

Another Office application such as Word, or a different application?

[YasserKhalil]

Different application ...

[HansV]

As far as I know, that is not possible.

[YasserKhalil]

Thanks a lot
I am thinking of concatenating those cells and use the copy for the last string output

[HansV]

That would be one option.
Another option would be to copy the cells to a contiguous range, then copy that range. After you're done with it, you can clear the temporary range.

[YasserKhalil]

That's perfect Mr. Hans
Thank you very much

[Doc.AElstein]

Hi,
Is something like this any use? ( I don’t really understand what it is doing or how it is doing it, but it seems to be a way to store Multiple Items In The Clipboard… )

Code: Select all

 Sub MultipleItemsInClipboard() ' http://www.cpearson.com/excel/clipboard.aspx
Rem 0 data object
Dim DataObj As Object          ' http://www.excelfox.com/forum/showthread.php/2240-VBA-referring-to-external-shared-Libraries-1)-Early-1-5)-Laterly-Early-and-2)-Late-Binding-Techniques?p=11017&viewfull=1#post11017
 Set DataObj = GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
Rem 1 put multiple items, ((non contiguous)cell values in this example ) into a single clipboard
Dim Ease As Variant
    For Each Ease In Array("A1", "B3", "D15")
    Dim EaseCnt As Long: Let EaseCnt = EaseCnt + 1
     DataObj.SetText Range(Ease).Value, "FmatId" & EaseCnt & ""
     DataObj.PutInClipboard
    Next Ease
Rem 2 retrieve individually, seperately, the seperaate items from the clipboard
Dim Cnt As Long
    For Cnt = 1 To EaseCnt
     MsgBox Prompt:="Item " & Cnt & " in clipboard is   " & DataObj.GetText("FmatId" & Cnt & "")
    Next Cnt
End Sub

Alan

ref
http://www.cpearson.com/excel/clipboard.aspx" onclick="window.open(this.href);return false;
http://www.eileenslounge.com/viewtopic. ... 38#p247681" onclick="window.open(this.href);return false;
https://stackoverflow.com/questions/250 ... 7#54960767" onclick="window.open(this.href);return false;

[YasserKhalil]

Thanks a lot Mr. Alan
The main purpose is to copy the cells contents to the clipboard and the code you posted doesn't do that
this worked fine for me

Code: Select all

Sub TestCopyClipboard()
    Dim e, i As Long

    For Each e In Array("I6", "J12")
        i = i + 1
        Cells(i, "Z").Value = Range(e).Value
    Next e

    Range("Z1:Z" & Cells(Rows.Count, "Z").End(xlUp).Row).Copy
End Sub

[Doc.AElstein]

...
The main purpose is to copy the cells contents to the clipboard and the code you posted doesn't do that

I think all the cell contents are all copied to the clipboard by the code I posted. They are all in the clipboard, and each can be individually retrieved, as that message box shows…

[YasserKhalil]

No the items are not in the clipboard as when opening the application and used Paste command, it doesn't paste anything.

[Leif]

No the items are not in the clipboard as when opening the application and used Paste command, it doesn't paste anything.

Do you have any Windows Explorer windows open when you run the code?
Plenty examples of this out in Googleland: Clipboard copy VBA code not working in Windows 10 | Chandoo.org Excel Forums - Become Awesome in Excel

[YasserKhalil]

Thanks a lot. I will have a look at the link

[Doc.AElstein]

No the items are not in the clipboard as when opening the application and used Paste command, it doesn't paste anything.

Hi Yasser,
With respect, I think you may be incorrect. I am not 100% sure… .. but...

When I try to paste, into Excel , for example , I get the error “Microsoft Office Excel can not paste the data”
Microsoft Office Excel can not paste the data.jpg : https://imgur.com/uGeAKFa" onclick="window.open(this.href);return false;

Microsoft Office Excel can not paste the data.jpg

_._______________________-

I think the items are in a clipboard. At least they appear to be in a clipboard in my systems and computers…
I think this code way that I posted, is using the Windows Clipboard. I think the items are in the Windows Clipboard. They may also be in the Office and / or Excel Clipboard. I am not sure. But if they are also in the Office and Excel clipboards, then they may not be in a form which Excel can recognise to paste.
You can retrieve the items from the (Windows) clipboard, using the code way I posted. - The code I posted demonstrates that in the Message Box section.. it retrieves the items from the clipboard. That coding works for me. – Did you try it? (Edit : I just edited and added some 'comments to the routine )
( ** There may be an additional problem and complication, as :Leif mentioned )
_._______

To explain and remind ourselves:
We have different clipboards: http://www.eileenslounge.com/viewtopic. ... 20#p246887" onclick="window.open(this.href);return false; , http://www.eileenslounge.com/viewtopic. ... 20#p246884" onclick="window.open(this.href);return false; . You have come cross this before, for example, in this thread of yours: http://www.eileenslounge.com/viewtopic.php?f=30&t=31849" onclick="window.open(this.href);return false;

_.___
As you know, ( For example: http://www.eileenslounge.com/viewtopic. ... 95#p242941" onclick="window.open(this.href);return false; , http://www.eileenslounge.com/viewtopic. ... 38#p247681" onclick="window.open(this.href);return false; , we can use the Data object to put data in the clipboard and then retrieve it with an Excel Paste action. This has been done a few times for you in Threads here by me and Hans.
In the code way I posted here in this Thread, I am using the Data Object and Windows Clipboard in a different way here to that which we all did in the past. – The key difference is that in the two code lines of …_
DataObj.SetText
DataObj.GetText
_... , I am now using the optional arguments.
In the ways we did in the past, without those optional arguments, a version was held in somewhere, somehow in some way, that then was recognised by Excel for a working Paste. ( I do not believe that anyone fully understands the processes going on that makes it work in such coding. But it seems to consistently work, ( apart from when the additional problems** may occur ) )
The way I am using the Windows Clipboard and data Object in this post is new to me. I have not seen it used very often. I do not have any experience with it. I have not been able to find much documentation on it. I do not really understand it.,
( I do not believe that anyone understands all the different clipboards and how they interact. I think it is a messed up set of interactions that everyone has long since given trying to make sense of. – In addition, as Leif mentioned** , there are problems with the clipboard currently which it seems that Microsoft have given up trying to solve. These problems could also play an extra role here, and complicate the issues further. )


I don’t know if this way of using the Windows Clipboard could be of any use to you. Possibly not. It is using a clipboard for multiple cells. And it is using a clipboard other than an Office clipboard. ( Note : you said you wanted to have a solution that did not use an Office application – ..?? I did not and do not quite understand what you meant there ?? )

The code way I posted may not be so helpful if you then want to go on using the Excel clipboard as you do with something such as paste. Your solution would be OK for that.
Or it may not be suitable for some other reason
But the routine I posted, Sub MultipleItemsInClipboard(), does fit your Thread title, “Clipboard for multiple cells” , and it appears to work in Vista, XP, Win 7 , Excel 2003 , 2007 , 2010. ( I do not have higher versions. Possibly someone else might check it in higher versions and let us know, out of interest, if it appears to work for them )
So it might be helpful for someone catching the thread in a search in the future.

Alan

**P.S. We also discussed the issue Leif referred to before in another Thread of yours, and there are some other links here:
http://www.eileenslounge.com/viewtopic. ... 38#p247809" onclick="window.open(this.href);return false;
( In most of the links, the solution was a work around using API calls as an alternative way to the simple data object methods to interact with the (Windows) clipboard).( RoryA , for example, says he does it a lot… to get his tool working… http://www.eileenslounge.com/viewtopic. ... 38#p247805" onclick="window.open(this.href);return false; . )

[YasserKhalil]

Thanks a lot Mr. Alan
Best Regards