Where is Clipboard
 

Visual Basic has a Clipboard object, e.g.
http://support.microsoft.com/default...b;en-us;247412
How is the clipboard accessed from Excel VBA?
All I want to do is Clipboard.SetText "hello"!

Where is Clipboard
 

Dennis,

See http://www.cpearson.com/excel/clipboar.htm .


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com




"Dennis W. Bulgrien" <dbulgrien vcsd com wrote in message
...
Visual Basic has a Clipboard object, e.g.
http://support.microsoft.com/default...b;en-us;247412
How is the clipboard accessed from Excel VBA?
All I want to do is Clipboard.SetText "hello"!

Where is Clipboard
 

Splendid, may I copy the code?
© Copyright 1997-2005 Charles H. Pearson

"Chip Pearson" wrote in message
...
Dennis,

See http://www.cpearson.com/excel/clipboar.htm .

Where is Clipboard
 

Yes, you may copy the code and do with it what you wish. All the
code and formulas on my web site are explicitly donated to the
public domain. Only the accompanying explanatory text is covered
by copyright.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Dennis W. Bulgrien" <dbulgrien vcsd com wrote in message
...
Splendid, may I copy the code?
© Copyright 1997-2005 Charles H. Pearson

"Chip Pearson" wrote in message
...
Dennis,

See http://www.cpearson.com/excel/clipboar.htm .

Where is Clipboard, ClipBoard_SetData
 

http://support.microsoft.com/default...kb;en-us;96900 contains a method
using Windows APIs from Access. I tried it and had the following results:
* I had to put a space before the line continuation character of "_, or reformat
them onto a single line
* I had to rename references of "Kernel" to "Kernel32"
* I had to rename "GlobalUnLock" to "GlobalUnlock" per
http://msdn.microsoft.com/library/de...obalunlock.asp
* When I called ClipBoard_SetData "hello" kb_OpenClipBoard(0&) returned zero
thus "Could not open the Clipboard. Copy aborted."
Hmmm.

"Dennis W. Bulgrien" <dbulgrien vcsd com wrote in message
...
Visual Basic has a Clipboard object, e.g.
http://support.microsoft.com/default...b;en-us;247412
How is the clipboard accessed from Excel VBA?
....

Where is Clipboard, ClipBoard_SetData
 

Probably need to change "User" to "User32" also, but even so still can't open
it.

"Dennis W. Bulgrien" <dbulgrien vcsd com wrote in message
...
http://support.microsoft.com/default...kb;en-us;96900 contains a method
using Windows APIs from Access. I tried it and had the following results:
....
* I had to rename references of "Kernel" to "Kernel32"
....
* When I called ClipBoard_SetData "hello" kb_OpenClipBoard(0&) returned zero
thus "Could not open the Clipboard. Copy aborted."
....

Where is Clipboard, ClipBoard_SetData
 

I need to use the whole actual 32-bit API declarations (below) and I stripped
the "kb_" prefix from the actual calls:
Declare Function OpenClipboard Lib "user32" (ByVal hWnd As Long) As Long
Declare Function GlobalAlloc Lib "kernel32" (ByVal wFlags As Long, ByVal dwBytes
As Long) As Long
Declare Function GlobalLock Lib "kernel32" (ByVal hMem As Long) As Long
Declare Function lstrcpy Lib "kernel32" (ByVal lpString1 As String, ByVal
lpString2 As String) As Long
Declare Function GlobalUnlock Lib "kernel32" (ByVal hMem As Long) As Long
Declare Function CloseClipboard Lib "user32" () As Long
Declare Function EmptyClipboard Lib "user32" () As Long
Declare Function SetClipboardData Lib "user32" Alias "SetClipboardDataA" (ByVal
wFormat As Long, ByVal hMem As Long) As Long

And thus need to rename the % Integers to As Long.
Dim hGlobalMemory%, lpGlobalMemory&, hClipMemory%, X%

"Dennis W. Bulgrien" <dbulgrien vcsd com wrote in message
...
http://support.microsoft.com/default...kb;en-us;96900 contains a method
using Windows APIs from Access. I tried it and had the following results:
....
* I had to rename references of "Kernel" to "Kernel32"
....
http://msdn.microsoft.com/library/de...obalunlock.asp
* When I called ClipBoard_SetData "hello" kb_OpenClipBoard(0&) returned zero
thus "Could not open the Clipboard. Copy aborted."
Hmmm.

Where is Clipboard, ClipBoard_SetData
 

Fooey. Abort that code. It kept crashing Excel. Found a different example (
http://support.microsoft.com/default...b;en-us;274326 ) and simplified
it to work.

Private Declare Function CloseClipboard Lib "User32" () As Long
Private Declare Function OpenClipboard Lib "User32" (ByVal hWnd As Long) As Long
Private Declare Function GlobalAlloc Lib "Kernel32" (ByVal wFlags As Long, ByVal
dwBytes As Long) As Long
Private Declare Function SetClipboardData Lib "User32" (ByVal wFormat As Long,
ByVal hMem As Long) As Long
Private Declare Function EmptyClipboard Lib "User32" () As Long
Private Declare Function GlobalLock Lib "Kernel32" (ByVal hMem As Long) As Long
Private Declare Function GlobalUnlock Lib "Kernel32" (ByVal hMem As Long) As
Long
Private Declare Sub CopyMemory Lib "Kernel32" Alias "RtlMoveMemory" (pDest As
Any, pSource As Any, ByVal cbLength As Long)

Global Const CF_TEXT = 1

Sub ClipBoard_SetData(sData As String)
If CBool(OpenClipboard(0)) Then
Dim hMemHandle As Long, lpData As Long
hMemHandle = GlobalAlloc(0, Len(sData) + 10)
If CBool(hMemHandle) Then
lpData = GlobalLock(hMemHandle)
If lpData < 0 Then
CopyMemory ByVal lpData, ByVal sData, Len(sData)
GlobalUnlock hMemHandle
EmptyClipboard
SetClipboardData CF_TEXT, hMemHandle
End If
End If
Call CloseClipboard
End If
End Sub

ClipBoard_SetData "hello"

"Dennis W. Bulgrien" <dbulgrien vcsd com wrote in message
...
Visual Basic has a Clipboard object, e.g.
http://support.microsoft.com/default...b;en-us;247412
How is the clipboard accessed from Excel VBA?
....

Where is Clipboard, ClipBoard_SetData
 

Hi Dennis,
Did you see this note on Chip's page: http://www.cpearson.com/excel/clipboar.htm

Because these procedures use the DataObject variable type, you must
have a reference set in your VBA project to the Microsoft Forms 2.0 object library.
Be sure that you include the one that says Forms.

and that is the same one that I am using in Excel 2000, possibly
later versions of Excel use a higher number but it would still be among
your choices in the Excel VBE, Tools, References.

Some additional examples:
Right Click Menus (Context Menus) in Excel
http://www.mvps.org/dmcritchie/excel/rightclick.htm

HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Dennis W. Bulgrien" <dbulgrien vcsd com wrote in message ...
Fooey. Abort that code. It kept crashing Excel. Found a different example (
http://support.microsoft.com/default...b;en-us;274326 ) and simplified
it to work.

Where is Clipboard, ClipBoard_SetData vs DataObject
 

Yes, and I found it, but I wanted a leaner solution, not leaner source code wise
but DLL wise.

"David McRitchie" wrote in message
...
Hi Dennis,
Did you see this note on Chip's page:
http://www.cpearson.com/excel/clipboar.htm

Because these procedures use the DataObject variable type, you must
have a reference set in your VBA project to the Microsoft Forms 2.0 object
library.
....

Where is Clipboard, ClipBoard_SetData vbNullChar
 

I think there is a bug in the default.aspx?scid=kb;en-us;274326 sample code.
Add the following before sData is used to keep trash off the end of the string:
sData = sData & vbNullChar

"Dennis W. Bulgrien" <dbulgrien vcsd com wrote in message
...
Fooey. Abort that code. It kept crashing Excel. Found a different example (
http://support.microsoft.com/default...b;en-us;274326 ) and simplified
it to work.

....
Sub ClipBoard_SetData(sData As String)
If CBool(OpenClipboard(0)) Then
Dim hMemHandle As Long, lpData As Long
hMemHandle = GlobalAlloc(0, Len(sData) + 10)
....