Link to home
Start Free TrialLog in
Avatar of Pandora
PandoraFlag for United Kingdom of Great Britain and Northern Ireland

asked on

VBA copying to clipboard object

I need to copy some text taken from a control (and slightly modified with a VBA routine) to the clipboard so that it is available for pasting in other applications.  In excel VBA etc, there is the PutInClipboard method but I cannot find a similar method in Access; any clues anyone please?
Avatar of threeps99
threeps99

Hmm, I would be very interested to. I cant think of the command, though Im sure someone here will be able to. The dirty workaround is to use SENDKEYS to copy the text and send Control and C. Though there has to be a neater way of doing it I think.

threeps
You can use these API's:

Declare Function TSB_API_OpenClipboard Lib "USER32" Alias "OpenClipboard" _
  (ByVal hwnd As Long) _
As Long

Declare Function TSB_API_GetClipboardData Lib "USER32" Alias "GetClipboardData" _
  (ByVal wFormat As Long) _
As Long

Declare Function TSB_API_CloseClipboard Lib "USER32" Alias "CloseClipboard" _
  () _
As Long

Declare Function TSB_API_SetClipboardData Lib "USER32" Alias "SetClipboardData" _
  (ByVal wFormat As Long, ByVal hMem As Long) _
As Long

Declare Function TSB_API_EmptyClipBoard Lib "USER32" Alias "EmptyClipboard" _
  () _
As Long
Avatar of Pandora

ASKER

jeez, that looks good although I'm always a bit scared of APIs for some reason; how would I actually call a sub to do this passing text as a variable though, and would I just put these functions in a normal module?  Yours, very ignorant in the ways of API! P.
Sorry about that - I was rushing as I was at the end of my lunch hour. You will need the declarations above as well as the following:

Declare Function TSB_API_GlobalAlloc Lib "kernel32" Alias "GlobalAlloc" _
  (ByVal wFlags As Long, ByVal dwBytes As Long) _
As Long

Declare Function TSB_API_GlobalLock Lib "kernel32" Alias "GlobalLock" _
  (ByVal hMem As Long) _
As Long

Declare Function TSB_API_lstrCopy Lib "kernel32" Alias "lstrcpyA" _
  (ByVal lpString1 As Any, ByVal lpString2 As Any) _
As Long

Declare Function TSB_API_GlobalUnlock Lib "kernel32" Alias "GlobalUnlock" _
  (ByVal hMem As Long) _
As Long

Private Const GHND = &H42
Private Const CF_TEXT = 1

Private Function SetClipboardData(ByVal sTextToWrite As String) As Boolean
 
    Dim lHoldMem As Long
    Dim lGlobalMem As Long
    Dim lClipMem As Long
    Dim lTmp As Long
 
    On Error GoTo SetClipboardData_Error

    ' Allocate moveable global memory.
    lHoldMem = TSB_API_GlobalAlloc(GHND, Len(sTextToWrite) + 1)
   
    ' Lock the block to get a far pointer to this memory.
    lGlobalMem = TSB_API_GlobalLock(lHoldMem)
   
    ' Copy the string to this global memory.
    lGlobalMem = TSB_API_lstrCopy(lGlobalMem, sTextToWrite)
 
    ' Unlock the memory.
    If TSB_API_GlobalUnlock(lHoldMem) = 0 Then
 
        ' Open the Clipboard to copy data to.
        If TSB_API_OpenClipboard(0&) <> 0 Then
 
            ' Clear the Clipboard.
            lTmp = TSB_API_EmptyClipBoard()
 
            ' Copy the data to the Clipboard.
            lClipMem = TSB_API_SetClipboardData(CF_TEXT, lHoldMem)
 
            lTmp = TSB_API_CloseClipboard()
        End If
    End If

    SetClipboardData = True

SetClipboardData_Exit:
    Exit Function

SetClipboardData_Error:
    SetClipboardData = False
    Resume SetClipboardData_Exit
End Function


You can now use the function above to write text to the clipboard. The function also returns a Boolean to state whether the Clipboard Write was successful or not.

I hope I haven't left anything else out. If I have, give me a yell.
Noggy,

I'm glad to see someone else who is using FMS's Total Access Source Book.  Of course, you should do a bit of editing and remove the TBS from the calls so that it appears that it is original thought. :-)>

After all, it has never been written that a good consultant know all the answers.  They just have to know better than the client where to find them.

Jim
JimMorgan - I didn't use FMS's Total Access Source Book - it's a bit of common code that we have in our team at work. However, I don't know who got it, and, as you indicate, it may have indeed have come from a book like this one. If that is the case, thanks FMS.

I also never implied that I knew all the answers - I just had the answer. As you say, I just knew where to find it - albeit not knowing where the original source came from.

BTW, it's TSB and not TBS :-)
Darn, these old fingers just have troubles with the BS's and the SB's.  (Blush, that was a Freudian slip!)

Yeah, FMS adds TSB at the front of all their calls, etc.  I guess they want to see how much of it comes around.  I kinda judge my contract programmers by how much they charge me to "write" code which has TSB, ADH ("Access Developer's Handbook" by Litwin, Getz, & Gilbert), or AES ("Access Expert Solutions" by Leszynski) in their code.  If they supply whole modules lifted from the books, CD's, addins and charge me for the time to have written the code, my estimation of their worth goes down in my eyes.

Total Access Source Book is not a book.  It actually is an Access program which has hundreds of code routines which you can search for and lift to use in your own programs.  No typeing and better than a CD.
Avatar of Pandora

ASKER

Noggy, wherever it comes from this is a brilliant answer and I thank you very much! Much obliged all, P
P.S. Noggy will you 'answer' the question so i can give you the points
JimMorgan - I totally agree with what you are saying - but, if code already exists, there's no point reinventing the wheel, is there? :-) You may even find that your contractors may not have been charging you for such modules as they can simply copy and paste - unless they charge you per line of code rather than for the whole job.
ASKER CERTIFIED SOLUTION
Avatar of Noggy
Noggy
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Go to FMSInc.COM for all their Total Access Products for both Access and VB.
Cheers, Jim (you don't mind me calling you Jim do you?). I've had a bit of a snoop and it looks good. "Boss, get your cheque book out!"
For young pups like you its Mr. Morgan.  :-)  Jim's fine.  That's why I used my real name for my handle.  The only other pseudonym that I've ever had in my life was CondorWizard.  I didn't come up with it.  My customers used to call me that and it kind of stuck.  If you were around in the glory years of BIX, you may have seen the name running around the forums there.
An even simpler way to copy text to the clipboard is to use the  DataObject Object of MSForms.



Dim CopyClip As MSForms.DataObject

Set CopyClip = New MSForms.DataObject
CopyClip.SetText Word.Value
CopyClip.PutInClipboard
eblockard, You look like a new face around here.  Welcome with the comments and question.  I noticed one posted today but I am not quite a 2000 expert yet, so kept my 2 cents worth out of it.

Jim
I was stuck up to my neck in API calls but that DataObject object does a real fine job.
This question was awarded, but never cleared due to the JSP-500 errors of that time.  It was "stuck" against userID -1 versus the intended expert whom you awarded.  This corrects the problem and the expert will now receive these points; points verified.

Please click on your Member Profile and select "View Question History" to navigate through any open or locked questions you may have to update and finalize them.  If you are an EE Pro user, you can also choose Power Search to find all your open questions.

This is the Community Support link, if help is needed, along with the link to All Topics which reflects many TAs recently added.

https://www.experts-exchange.com/jsp/qList.jsp?ta=commspt
https://www.experts-exchange.com/jsp/zonesAll.jsp
 
Thank you,
Moondancer
Moderator @ Experts Exchange