Results 1 to 7 of 7
  1. #1
    Lounger
    Join Date
    Jan 2001
    Location
    Brecon, South Wales, UK
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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!

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

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

    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.
    Wendell

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

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

    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.
    Charlotte

  4. #4
    Star Lounger
    Join Date
    Dec 2001
    Location
    Fredensborg, Denmark
    Posts
    86
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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.
    Attached Files Attached Files

  5. #5
    Lounger
    Join Date
    Jan 2001
    Location
    Brecon, South Wales, UK
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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.

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

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

    I found this (or something very similar) in the MSDN library (I think) some time ago, which will copy a string to the clipboard:
    <pre>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
    </pre>

    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  7. #7
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •