PDA

View Full Version : How to get all texts from clipboard?



Erdin? E. Ka
12-21-2006, 10:51 AM
:hi: Hi everyone,

I am trying to get a list of text of clib board. But i couldn't do it. :dunno

I need a code like below: (this code not a true code; only for an example to be more understanable.)


Sub ListOfClipBoard()
Dim i As Integer
Dim DtObj As New DataObject
DtObj.GetFromClipboard
For i = 1 To DtObj.GetText(i).Count
Cells(i, 1) = DtObj.GetText(i)
Next
End Sub


Thanks in advande.:friends:

asingh
12-21-2006, 07:42 PM
This might help...



Sub Paste_data_frm_ClpBrd()

Dim mydata As DataObject
Dim TxtData As String

Set mydata = New DataObject

mydata.GetFromClipboard

If mydata.GetFormat(1) = False Then
MsgBox "No Data in ClipBoard..!", vbInformation
Else
TxtData = mydata.GetText(1)
Cells(1, 1) = Trim(TxtData)
End If

Set mydata = Nothing

End Sub

tstom
12-21-2006, 10:11 PM
An over-simplified example of moving a list from the clipboard to range a1 of the activesheet...

Test by copying the following list of ten items and running your procedure.

Line 1
Line 2
Line 3
Line 4
Line 5
Line 6
Line 7
Line 8
Line 9
Line 10


Sub SingleColumnListFromClipBoard()
Dim i As Integer, Temp() As String
Dim DtObj As New DataObject
DtObj.GetFromClipboard
Temp() = Split(DtObj.GetText, vbCrLf)
Cells(1).Resize(UBound(Temp)) = Application.Transpose(Temp())
End Sub

stanl
12-22-2006, 02:21 AM
If the data is in the Clipboard as a list why not.



Range("A1").Select
ActiveSheet.Paste

Stan

malik641
12-22-2006, 06:24 AM
I think what Erdin? is asking for is when, in Excel, you press Ctrl+C twice (quickly), you will see the office clipboard come up (xl2003, not sure about earlier versions) that holds everything that was placed into the clipboard, and you can choose which item you want to paste, or choose to "Paste All". I'm pretty sure Erdin? wants to show all items from the clipboard, not just the most recent Copied block of text.

I tried recording a macro to 'Paste All', and it came up with a bunch of "ActiveSheet.Paste" 's, I tried to run the macro again, but it only pasted the most recent copied item

Sorry Erdin?, but I'm not sure how to do this.


By the way, is there a difference between the Office Clipboard and Windows' clipboard? I don't think so, but it just seems that way......I'm not familiar with the Clipboard.

malik641
12-22-2006, 06:30 AM
By the way, is there a difference between the Office Clipboard and Windows' clipboard? I don't think so, but it just seems that way......I'm not familiar with the Clipboard.
Nevermind, I found this in Help:



The Office Clipboard is related to the system Clipboard in the following ways:

When you copy multiple items to the Office Clipboard, the last item you copy is always copied to the system Clipboard.
When you clear the Office Clipboard, the system Clipboard is also cleared.
When you use the Paste command, the Paste button, or the shortcut keys (CTRL+V), you paste the contents of the system Clipboard, not the Office Clipboard.

malik641
12-22-2006, 07:05 AM
I don't have too much time to look at this, but maybe these links will help:

http://msdn2.microsoft.com/en-us/library/aa189708(office.10).aspx

http://msdn2.microsoft.com/en-us/library/ms674551.aspx

:)

Brandtrock
12-23-2006, 01:35 AM
Here is a link to a discussion (http://www.vbaexpress.com/forum/showthread.php?t=4955) about the two clipboards. Unfortunately, the article by Tony is not in the articles section any more. I thought I copied it, but can't seem to find it.

Not anything about getting the contents, but still is interesting in getting an understanding of what is going on in the background.

Regards,

Ivan F Moala
12-23-2006, 04:46 AM
By the way, is there a difference between the Office Clipboard and Windows' clipboard? I don't think so, but it just seems that way......I'm not familiar with the Clipboard.

Using office gives the user 2 Clipboards
1) OS (Operating Sytem) clipboard
2) Office Clipboard (All office applications eg Excel)

Excel 2000 onwards has it's own Clipboard object (Toolbars - clipboard) where it stores up to
12 items Xl2000 and 24 for XL2003 (Xl2002 unsure). If the number of items copied exceeds this number then
the last item copied gets replaced and each subsequent item copied is replaced working backwards.

The OS Clipboard is where data is stored when you select "Edit > Copy", press CTRL-C,
or "Right Click" and select "Copy" from the pop up context menu. When you "Cut" you're
ALSO putting that data on the OS Clipboard AND the Office clipboard. The
difference is that the data is also deleted from the source document (If you paste). It is in
effect a "Copy" and "Delete". The OS Clipboard can only have one chuck of data at a tiime
and can handle many more diff data types then Excel eg Files from Explorer UI. Copying
something else overwrites it. You can infact create your own clipboard format using the
API RegisterClipboardFormat from the user32.dll.
Note, the data is stored in RAM so copying large amounts of data will take up more RAM.
The same holds true for Excel which is why you get a prompt to save the data or clear it
when you do large amounts of copy and paste without clearing.

That is the difference between the OS clipboard and Office in that Office handles more items
via its own Clipboard toolbar AND also uses BOTH.

Setting CutCopyMode = False clears the last copied item indicated by the "marching ants"
and clears the "Paste" commandbutton AS WELL AS clearing the OS Clipboard, to which
that API Call clears also. It does NOT, however clear the Office Clipboard (Toolbars - Clipboard)
and is the strength of the office clipboard as it holds the data between MS Office applications
eg Word, Excel, Powerpoint, Outlook etc.

So the Application.cutcopymode=false will CLEAR the clipboard (operating system) BUT leave the items
in the Clipboard toolbar and clear the "Paste" commandbutton indicator to show it is empty.

The API will also clear it (OS clipboard) AND leaves the item in the Clipboard tooolbar, so yes
cutcopy mode will do the same as the API.

stanl
12-23-2006, 07:46 AM
I think what Erdin? is asking for is when, in Excel, you press Ctrl+C twice (quickly), you will see the office clipboard

I apologize, I thought the code he posted used DataObjects and my assumption was they placed data in the Windows Clipboard. I don't think you can automate the Office Clipboard; and I am probably among the minority who find it an 'annoyance' [we had it disabled in the registry for Office 2000]. Since the Office Clipboard stores values, were you to say have a task which required you to paste several selected range values from Excel into Word, you could simulate an Office Clipboard by persisting the ranges as either XML or ByteArrays, storing them in a fabricated recordset, then placing into Word as needed.. just .02 Stan

Carl A
12-23-2006, 10:08 AM
I am probably among the minority who find it an 'annoyance

No Stanl you are not a minority. Here is a nice little utility for extending the clipboard. Comes with source code, that is if you know C.:thumb

http://www.nakka.com/soft/clcl/index_eng.html

Erdin? E. Ka
12-23-2006, 11:17 AM
Hi and Marry cristmas to Asingh, Tom, Stan, malik641, Brandtrock, Ivan F Moala, Carl A and all other VBAX members, :hi:

Thank you for kindly helps.

I solved via Nakashima Tomoaki's dll example at http://www.nakka.com/.

But actually i want write a Function for this.

For example Office Clip Board have 10 records.
My (if it possible) UDF should be like below:
=FROM_OFFICE_CLIP_BOARD(1)
Then UDF will return first record of Clip Board to cell.
I tried to make it but i couldn't do. :dunno

Any ideas?

stanl
12-24-2006, 11:45 AM
Then UDF will return first record of Clip Board to cell.
I tried to make it but i couldn't do. :dunno
Any ideas?

Is the dll ActiveX; can you set a reference to it? Alternatives like ADO Streams and Dictionary Objects seem more viable since they already interact with Excel/VBA and especially if all you want to do is place text in a cell. Anyway, if you can set a reference to the dll you can use the Object Browser to figure out how to use it in VBA. Thank you for your thoughts for the holidays... best wishes to you and your family. Stan

Cyberdude
12-24-2006, 04:49 PM
Are there any conditions under which VBA will go to the clipboard for it's next statement(s) to execute?

This is a trivial example, but illustrates what I'm wondering about:

Just before a macro exits, it places the statement:
Workbooks.Open Filename:="C:\Excel Documents\ETF Trusts.xls"
on the clipboard.
Next it closes the currently active workbook, which terminates the macro.
Then VBA looks at the clipboard to see if there is a valid statement there, retrieves the "Open" statement, and executes it.

I suppose this is an unlikely scenario since there is a brief interval when no workbook is open, hence I imagine VBA is also closed. Oh, well ... just a thought.

stanl
12-25-2006, 10:39 AM
Just before a macro exits, it places the statement:
Workbooks.Open Filename:="C:\Excel Documents\ETF Trusts.xls"


I was unaware that VBA had an execute() function which would parse and interpret a valid statement/command? Stan

Erdin? E. Ka
12-25-2006, 04:26 PM
....Thank you for your thoughts for the holidays... best wishes to you and your family. Stan

Hi Stan,

Thank you very much to kindly wishes.:friends:

By the way, i think that i should the dll ActiveX of nakka. It's useful.:yes

stanl
12-26-2006, 06:02 AM
By the way, i think that i should the dll ActiveX of nakka. It's useful.:yes

Good luck with it. The clclhook.dll does not appear to be ActiveX (at least it cannot be registered) which implies it has limited usefulness with VBA. You might be able to write VBA code to perform shell operations and SendKeys, but it addition to memory issues with multiple Office Clipboard items that seems a little 'much' given alternatives available. Stan