Copy data from Windows clipboard to Access

To retrieve information from the Clipboard, follow these steps.

NOTE: You may have some Microsoft Windows API functions defined in an
existing Microsoft Access library; therefore, your declarations may be
duplicates. If you receive a duplicate procedure name error message, remove
or comment out the declarations statement in your code.

1. Create a module and type the following lines in the Declarations
section:
----------------------- Put this in a Module ------------------
Declare Function OpenClipboard Lib "User32" (ByVal hwnd As Long) As Long
Declare Function CloseClipboard Lib "User32" () As Long
Declare Function GetClipboardData Lib "User32" (ByVal wFormat As Long) As Long
Declare Function GlobalAlloc Lib "kernel32" (ByVal wFlags&, ByVal dwBytes As Long) As Long
Declare Function GlobalLock Lib "kernel32" (ByVal hMem As Long) As Long
Declare Function GlobalUnlock Lib "kernel32" (ByVal hMem As Long) As Long
Declare Function GlobalSize Lib "kernel32" (ByVal hMem As Long) As Long
Declare Function lstrcpy Lib "kernel32" (ByVal lpString1 As Any, ByVal lpString2 As Any) As Long

Public Const GHND = &H42
Public Const CF_TEXT = 1
Public Const MAXSIZE = 4096
----------------------------------------------


2. Type the following procedure:

Function ClipBoard_GetData()
Dim hClipMemory As Long
Dim lpClipMemory As Long
Dim MyString As String
Dim RetVal As Long

If OpenClipboard(0&amp = 0 Then
MsgBox "Cannot open Clipboard. Another app. may have it open"

Exit Function
End If

' Obtain the handle to the global memory
' block that is referencing the text.
hClipMemory = GetClipboardData(CF_TEXT)
If IsNull(hClipMemory) Then
MsgBox "Could not allocate memory"
GoTo OutOfHere
End If

' Lock Clipboard memory so we can reference
' the actual data string.
lpClipMemory = GlobalLock(hClipMemory)

If Not IsNull(lpClipMemory) Then

MyString = Space$(MAXSIZE)
RetVal = lstrcpy(MyString, lpClipMemory)
RetVal = GlobalUnlock(hClipMemory)

' Peel off the null terminating character.
MyString = Mid(MyString, 1, InStr(1, MyString, Chr$(0), 0) - 1)
Else
MsgBox "Could not lock memory to copy string from."
End If

OutOfHere:

RetVal = CloseClipboard()
ClipBoard_GetData = MyString

End Function

---------------------------------------------------

3. To test this function, copy one of the lines from the function to the
Clipboard, type the following line in the Debug window, and then press
ENTER.

?ClipBoard_GetData()

or
' This is a Command button that gets Clipboard info and puts it in a text box
Private Sub Command6_Click()
Me!Text7 = ClipBoard_GetData()
End Sub