Getting text onto clipboard from code (Access 97/2000)

Topic

New Reply

Anyone know a handy way in VBA code of getting text from a string variable onto the Windows Clipboard? I want a quick-and-dirty way to concatenate name and address information from the current record in a form, add it to a string variable and then place the value on the Clipboard (so that I can switch to Word and then paste the text). This is not a mail-merge, multi-record situation. I want my users to be able to do this ‘on the fly’ for a single record. There must be a way of doing this. There’s also probably a much more elegant way of doing it!

Reply | Quote

Replies

The part you can’t really do is to add it to a string variable, as the Clipboard doesn’t know anything about VBA. The approach we usually use is OLE Automation. In that approach, the user has a button on a form that they click and that starts an instance of Word (sometimes with a special template that is a form letter), and we take the data directly from the form and paste it into the Word document, and give focus to the Word document. If you want to pursue it, there is a good description of the process in the Access Developers Handbook by Getz, et al published by Sybex. There are also several knowledge base articles that deal with OLE Automation and driving Word from Access (and vice versa). If you need further references, please post again.

Reply | Quote

To piggyback on Wendell’s post, the ADH also contains code for manipulating the clipboard, but it is far from simple and intuitive. You would have to copy their code and build and interface to it yourself.

Reply | Quote

Couldn’t help it. Had to try to find a simple way of doing a copy of a string to the clipboard without heavy coding.

All there is to it is this:
Combine in another textbox on your form the text you want to copy, select the new textbox and use the RunCommand method to do the job.

Now, one would probably not do that. It looks a bit clumsy, does it not?

So, why not put a bit of code in a module to perform the job. The core of it is just a few lines of code:

DoCmd.OpenForm “frmTextToClipBoard”
Forms!frmTextToClipBoard!txtData = strText
DoCmd.RunCommand acCmdCopy
DoCmd.Close acForm, “frmTextToClipBoard”

frmTextToClipBoard is a simple form which has just one unbound textbox named txtData. No code.

The attached sample db skows how it can be used to combine the contents of textboxes on a form and copy to the clipboard by calling public function CopyTextToClipBoard(strText).

May be not extremely useful, but fun to do and could be used as a handy way of doing the odd copying of a combination of name, address etc. from Access to Word, Excel or whatever.

Reply | Quote

Great, Claus. Thanks very much. This is exactly the kind of quick and (slightly) dirty solution I was looking for. My users just want to get one name and address out of Access and into wherever they are currently working in Word (without being tied to a particular template, document, place in a document, etc.). Somehow OLE automation and firing up a new instance of Word seem like massive overkill for this purpose and could present other problems. I suppose, at the end of the day, this is just the kind of thing (simple, user-controlled and very flexible intra- or inter-application data transfer) which the Clipboard was invented to do and which helped to make Windows the flexible animal it is.

Thanks very much to all who contributed so helpfully and patiently to this thread.

Reply | Quote

I found this (or something very similar) in the MSDN library (I think) some time ago, which will copy a string to the clipboard:

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

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

Function fSetClipboardString(strInput As String)
   Dim hGlobalMemory As Long, lpGlobalMemory As Long
   Dim hClipMemory As Long, lngReturn As Long
   
   ' Allocate moveable global memory.
   '-------------------------------------------
   hGlobalMemory = GlobalAlloc(GHND, Len(strInput) + 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, strInput)
   
   ' Unlock the memory.
   If GlobalUnlock(hGlobalMemory)  0 Then
      MsgBox "Could not unlock memory location. Copy aborted."
      GoTo Graceful_exit
   End If
   
   ' Open the Clipboard to copy data to.
   If OpenClipboard(0&) = 0 Then
      MsgBox "Could not open the Clipboard. Copy aborted."
      Exit Function
   End If
   
   ' Clear the Clipboard.
   lngReturn = EmptyClipboard()
   
   ' Copy the data to the Clipboard.
   hClipMemory = SetClipboardData(CF_TEXT, hGlobalMemory)

Graceful_exit:

   If CloseClipboard() = 0 Then
      MsgBox "Could not close Clipboard."
   End If

End Function

Hope that helps.

Reply | Quote

Thanks Claus

Great piece of code for us newies to digest.

It again proves a picture is worth a 1000 words.

Keep up the good work

Reply | Quote