Accessing clipboard through VBA

[pjelliffe]

Is there a way to access what's in the clipboard from a macro? FOr
example, I'd like to copy a range of cells, select a new location, and
paste the transpose of the original range. If I record this as a
macro, I have to hard code the range that gets copied.

I don't want to use Paste Special every time, it's too cumbersome.

I wasn't able to find any info on this in the help files.

 

[Guest]

Look for DataObject in Excel's help.

 

[RB Smissaert]

This API will do it:

Private Const GHND = &H42
Private Const CF_TEXT = 1

Private Declare Function GlobalAlloc _
Lib "kernel32" (ByVal wFlags&, _
ByVal _
dwBytes As Long) As Long
Private Declare Function GlobalLock _
Lib "kernel32" (ByVal hMem As Long) _
As Long
Private Declare Function GlobalSize _
Lib "kernel32" (ByVal hMem As Long) _
As Long
Private Declare Function lstrcpy _
Lib "kernel32" (ByVal lpString1 As Any, _
ByVal lpString2 As Any) As Long
Private Declare Function GlobalUnlock _
Lib "kernel32" (ByVal hMem As Long) _
As Long
Private Declare Function OpenClipboard _
Lib "user32" (ByVal hwnd As Long) _
As Long
Private Declare Function CloseClipboard _
Lib "user32" () As Long
Private Declare Function GetClipboardData _
Lib "user32" (ByVal wFormat As _
Long) As Long
Private Declare Function EmptyClipboard Lib "user32" () As Long
Private Declare Function SetClipboardData _
Lib "user32" (ByVal wFormat _
As Long, _
ByVal hMem As Long) As Long

Function ClipBoard_GetText() As String

Dim hClipMemory As Long
Dim lpClipMemory As Long
Dim strCBText As String
Dim retval As Long
Dim lngSize As Long

If OpenClipboard(0&) <> 0 Then
'Obtain the handle to the global
'memory block that is referencing the text
'----------------------------------------
hClipMemory = GetClipboardData(CF_TEXT)
If hClipMemory <> 0 Then
'Lock Clipboard memory so we can
'reference the actual data string
'--------------------------------
lpClipMemory = GlobalLock(hClipMemory)
If lpClipMemory <> 0 Then
lngSize = GlobalSize(lpClipMemory)
strCBText = Space$(lngSize)
retval = lstrcpy(strCBText, lpClipMemory)
retval = GlobalUnlock(hClipMemory)
'Peel off the null terminating character
'---------------------------------------
strCBText = Left$(strCBText, InStr(1, strCBText, Chr$(0),
0) - 1)
Else
MsgBox "Could not lock memory to copy string from."
End If
End If
Call CloseClipboard
End If

ClipBoard_GetText = strCBText

End Function

Public Function ClipBoard_SetText(strCopyString As String) As Boolean

Dim hGlobalMemory As Long
Dim lpGlobalMemory As Long
Dim hClipMemory As Long

'Allocate moveable global memory
'-------------------------------
hGlobalMemory = GlobalAlloc(GHND, Len(strCopyString) + 1)

'Lock the block to get a far pointer to this memory
'--------------------------------------------------
lpGlobalMemory = GlobalLock(hGlobalMemory)

'Copy the string to this global memory
'-------------------------------------
lpGlobalMemory = lstrcpy(lpGlobalMemory, strCopyString)

'Unlock the memory and then copy to the clipboard
'------------------------------------------------
If GlobalUnlock(hGlobalMemory) = 0 Then
If OpenClipboard(0&) <> 0 Then
Call EmptyClipboard
hClipMemory = SetClipboardData(CF_TEXT, hGlobalMemory)
ClipBoard_SetText = CBool(CloseClipboard)
End If
End If

End Function


Sub test()

ClipBoard_SetText "testing"

MsgBox ClipBoard_GetText

End Sub


RBS

 

[Guest]

You could attach this macro to a button on your toolbar, then copy your
range, select your destination, and click the button

Sub Macro1()
Selection.PasteSpecial Paste:=xlAll, _
Operation:=xlNone, SkipBlanks:=False, _
Transpose:=True
Application.CutCopyMode = False
End Sub