Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

How to paste clipboard into Text Box 2

Status
Not open for further replies.

tbl

Technical User
May 15, 2001
175
BE
Can anyone help me with pasting the contents of the clipboard directly into an Excel Text Box (a shape)?
My VBA script copies the contents of a Wordpad text file successfully so that I have what I need to paste into Excel, but although I can manually select my Text Box and paste the text into it, I can't do it with VBA.

Richard
 
Thanks for the reply.

activesheet.paste does not paste inside the text box unfortunately, and sendkeys is something that one tries to avoid if possible. The curious thing is that one can perform the operation manually just by pasting.

Richard
 
Hi tbl,

ActiveSheet Properties and Methods do not refer to Controls on the Sheet - you must refer to the Controls more directly. Something like this should work ..

Code:
Sheet1.textbox1.Paste

Enjoy,
Tony
 
One way that you could try is to use the Clipboard API's. What follows copies the clipboard contents to a String buffer from which you can easily copy into the text box, or you might even be able to copy directly into the textbox.
In the declarations section of a code module, add the following declarations.
Code:
Public Const CF_TEXT = 1
Public Declare Function GetClipboardData Lib "user32" (ByVal wFormat As Long) As Long
Public Declare Function CloseClipboard Lib "user32" () As Long
Public Declare Function OpenClipboard Lib "user32" (ByVal hwnd As Long) As Long
Public Declare Function lstrlen Lib "kernel32" Alias "lstrlenA" (ByVal lpString As Long) As Long
Public Declare Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" (pDst As Any, pSrc As Long, ByVal ByteLen As Long)
and in appropriate location in the code where you want the paste to happen, drop in the following code
Code:
Dim lLnd_TextHandle As Long
Dim lLng_TextLen As Long
Dim lStr_StrBuffer As String

OpenClipboard Me.hwnd
lLnd_TextHandle = GetClipboardData(CF_TEXT)
If (lLnd_TextHandle <> 0) Then
   lLng_TextLen = lstrlen(lLnd_TextHandle)
   If (lLng_TextLen > 0) Then
      lStr_StrBuffer = Space(lLng_TextLen)
      CopyMemory ByVal lStr_StrBuffer, ByVal lLnd_TextHandle, lLng_TextLen
      MsgBox lStr_StrBuffer
   End If
End If
CloseClipboard

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein
 
Tony
I was having a go at this and could now kick myself! It's faily easy within a form but that takes the biscuit!!

Have a purple pointy pip from me!
;-)

If a man says something and there are no women there to hear him, is he still wrong? [ponder]
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244
 
Thanks very much Tony,
I had suspected that best answer would be also the simplest. The beauty of the simple paste is that it retains all the best of the formatting without showing the return characters

Richard
 
I agree that TonyJollans' approach is better. At the time I posted the API alternative, I was under the impression that the .Paste method wasn't working.

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein
 
Thanks CajunCenturion for taking the trouble to post. Using your approach I did arrive at the solution that I am cuurrently using.

Sub GetClipboard()
Dim clipboardContents As DataObject
Dim clipcontents As String, X As Integer

Set clipboardContents = New DataObject
clipboardContents.GetFromClipboard
clipcontents = &quot;&quot;
clipcontents = Replace(clipboardContents.GetText(1), Chr(13), &quot;&quot;)

Set txt = ActiveSheet.TextBoxes(&quot;Text 7&quot;)
txt.Text = &quot;&quot; 'clear text box
txt.Text = clipcontents
NrChrs = Len(clipcontents)

For X = 0 To 20
If (X * 255 + 1) > NrChrs Then Exit Sub
txt.Characters(X * 255 + 1).Text = Mid(clipcontents, X * 255 + 1, 255)
Next X

End Sub

Richard
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top