[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Pasting all item on clipboard to an excel sheet using vba.

Posted on 2007-10-14
12
Medium Priority
?
3,156 Views
Last Modified: 2013-11-26
Hi Experts,
I need to try and Paste all items saved on the clipboard into excel using VBA.
I currently have a code that does half the job, i.e it only pastes the last copied item on the clipboard but I need it to paste all items on the clipboard.
Note: this code only works when I select Tools> references and checking the box for Microsoft Forms 2.0 Object library.

'--------------beginning of code
Public Function GetOffClipboard() As Variant
    Dim MyDataObj As New DataObject
    MyDataObj.GetFromClipboard
    GetOffClipboard = MyDataObj.GetText()
    ActiveSheet.Paste
End Function
'-----------end of code

Again my question is:  I need to paste all item on the clipboard to excel using VBA coding.
Thanks in advance.
0
Comment
Question by:tolgss
  • 6
  • 4
  • 2
12 Comments
 
LVL 23

Expert Comment

by:ahammar
ID: 20073929
Are you talking about the windows clipboard?
The windows clipboard can only hold 1 thing at a time so the last thing you copy is the only thing you can retrieve...it is the only thing on the clipboard.

If you are talking about the office clipboard, then I don't know that one...but I'll work on it a bit.  Someone else may have an answer before I get back...

:-)
ah
0
 
LVL 23

Expert Comment

by:ahammar
ID: 20073974
You probably don't need to care about this, but I should clarify myself...the windows clipboard can hold only 1 thing at a time of the same data format.  It can hold more than 1 thing if the data format is different for each one.
0
 

Author Comment

by:tolgss
ID: 20074669
Hi and thanks for your comment.

It's the office clipboard that I need to collect all copied items and then paste onto the excel sheet.
In excel if I select Edit > Office Clipboard > I will get the office clipboard task pane and anything I copy outside of excel will be in the clipboard ready for pasting into excel whether it be the last copied item or the first copied item. I think it can hold up to 25 items.

What I need is to beable to do is paste all items on the clipboard to the excel sheet whether it be in  one go or in several attempts as long as all items are pasted into an excel worksheet with VBA coding.

I can manage to do this by just selecting 'paste all' in excel on the clipboard task pane with my mouse, but I need to do this with VBA coding.

I know there are programs out there that may do the trick but I need this done with VBA.

Reason I need this:
My aim is to work outside of excel on an external program, copy about 20 items or so which the office clipboard stores in memory, then paste all copied items stored in the office clipboard onto an excel sheet which enables me to work on all the texts I've copied.




0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 23

Expert Comment

by:ahammar
ID: 20074730
Ok...I understand what you want now, and that's actually what I've been trying to do, but I have not found a way to do that yet. I'm gonna work on it more though still...

:-)
ah
0
 
LVL 23

Expert Comment

by:ahammar
ID: 20074857
Ok...I can't figure out how to do that, but I did come up with an alternative if it will work for you.   It will probably work best for only text or cell values though.  It's actually a way to design your own list of items sent to the clipboard and they get saved in a sheet.  What it does is check the windows clipboard every second (or whatever time you want to set it at) and if whatever is there is different than the last item on the list, it adds it.
What you end up with is a list of items that have been copied to the clipboard, just like in the office clipboard, except as a list of items on a sheet.  The advantage is that there is no limit to the number of items you can have, but the disadvanage is that you are limited to text or cell values.
But you can do whatever you want with the range that has your list in it like copy it somewhere else, or simply just have it list the items where you need them to start with.

If this won't work for you, then I would leave this question open for another day or 2, and if you don't get the answer you are looking for, delete it and repost another one.

Here's a working file:
https://filedb.experts-exchange.com/incoming/ee-stuff/5015-MultiClipBoard.zip


:-)
ah



0
 
LVL 65

Expert Comment

by:RobSampson
ID: 20074859
Hi guys.  The Office Clipboard is not exposed to access by code:  See here for a description:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2154702&SiteID=1

It is exposed in Office 200 via the CommandBar that exists with it, but any version later.

As far as I know, you can only use VBA to access the Windows clipboard in later versions:
http://www.cpearson.com/excel/clipboard.htm

Regards,

Rob.
0
 
LVL 65

Expert Comment

by:RobSampson
ID: 20074863
Ha, Office 200?  That must be an *old* version!  Obviously I meant "Office 2000"....
0
 
LVL 23

Accepted Solution

by:
ahammar earned 1000 total points
ID: 20074896
Ya...I can't quite remember Office 200...that must have been before my time...lol
I was thinking what Rob has mentioned to be true, but I didn't know for sure, so I didn't post my thoughts.
So, if that is really the case, then try to impliment my alternative.  It's fairly simple.  I forgot to mention that any new workbook you put this in, you have to add the "Microsoft forms 2.0 library" in references...(just like you said you did in your original question)  (I actually had to add a form, then remove it to even have that library available..don't know why that was..)

Also, it can be modified and enhanced to copy a value back to the clipboard by clicking on 1 of the items in the list if you desire.

:-)
ah
0
 

Author Comment

by:tolgss
ID: 20075835
Your alternate method  is a better solution than I expected.
This is great stuff. Thank you ahammar and thanks to rob for the info.
0
 
LVL 65

Expert Comment

by:RobSampson
ID: 20075882
No worries....oh, and very nice work ahammer, that is a great alternative....

Regards,

Rob.
0
 
LVL 23

Expert Comment

by:ahammar
ID: 20075945
tolgss:
Thanks for the points and the grade...I'm glad it worked for you as a good alternative.

Rob:
Thanks for the compliment.  Ps...I've been doing some studying on Office 200, and it appears that the last recorded use of that was when Moses used it in creating the 10 commandments...lol
I love funny mistakes...  I've made a few doosies myself...

:-)
ah
0
 
LVL 65

Expert Comment

by:RobSampson
ID: 20076139
LOL!  Nice....

Yeah, I bet he didn't have spellcheck though!

Most of my mistakes aren't really my fault though, I've got dyslexic fingers.....honest!

Oh, and there's a prime example that I missed before, it's not that I mispell words, most often I just leave out words entirely, such as:
>> It is exposed in Office 200 via the CommandBar that exists with it, but any version later.

which has two mistakes, one we've already researched (?), and the other is that it should have read "but NOT any version later."

So there you go.....dyslexic fingers.....well that's my excuse anyway!  As long as people can understand me (I try not to use Aussie slang), I'll be happy!

See ya (what was that about no Aussie slang??),

Rob.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question