ACC2000: How to Retrieve Information from the Clipboard (210213)

SUMMARY

Versions of Access earlier than Access 2000 do not have a command that you can use to retrieve information from the Clipboard. In earlier version, to retrieve information from the Clipboard, you have to define a Visual Basic for Applications function that calls several Windows API functions.

This article describes the RunCommand method constants that are new to Access 2000, and defines a function that you can use to retrieve information from the Clipboard.

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements.

MORE INFORMATION

RunCommand Method Constant, acCmdPaste.

The following code will paste the contents of the Clipboard to a text box named txtSample:

Private Sub cmdPaste_Click()
   Me!txtSample.SetFocus
   DoCmd.RunCommand acCmdPaste
End Sub

Other constants that apply to the Clipboard contents are:

acCmdCopy - Copies the contents of the control with the focus to the Clipboard.
acCmdPasteAppend - Appends the contents of the Clipboard to the control with the focus.
acCmdPasteSpecial - Displays the Paste Special dialog box.

Using API Calls to Retrieve Information from the Clipboard.

To use API calls 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.

Create a module, and then type the following lines in the Declarations section:

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

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&) = 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

To test this function, copy one of the lines from the function to the Clipboard, type the following line in the Immediate window, and then press ENTER:
?ClipBoard_GetData()
Note that the line that you copied is displayed in the Immediate window.