putting data into clipboard

[depcdivr]

Is it possible to put three textboxes worth of data into the clipboard so that they can be pasted into another program?

If you can do this, if the middle textbox is more than 1 line how can I put the 3rd textbox lined up with the top row from textbox2.

I.e. I am trying to copy

textbox1 value = part number
textbox2 value = description line 1
description line 2
description line 3
textbox3 value = price


I want to put this information into the clipboard so that when I go to paste it I get

part number description line 1 price
description line 2
description line 3

 

[BrianB]

Try this :-

'=============================================================================
'- USE DATAOBJECT TO PUT TEXT INTO THE CLIPBOARD
'- useful for pasting into an external application
'- also see my code at http://www.mrexcel.com/forum/showthread.php?t=310991
'- Brian Baulsom July 2008
'=============================================================================
Private Sub CommandButton1_Click()
    Dim MyStr As String
    Dim MyDataObject As DataObject
    Dim Text2 As String
    Dim TextReturn As String
    Dim EndLine1 As Integer
    Dim MyText As String
    '-------------------------------------------------------------------------
    Set MyDataObject = New DataObject
    TextReturn = Chr(13)
    '-------------------------------------------------------------------------
    '- need to get first line of textbox into a separate string
    '- lines of a textbox are separated by hidden characters 13 & 10.
    '- ** these characters are retained in this example **
    '-------------------------------------------------------------------------
    '- End of line 1 : find first Return Character (code 13) in the string
    Text2 = TextBox2.Value
    EndLine1 = InStr(1, Text2, TextReturn, vbTextCompare)
    '-------------------------------------------------------------------------
    '- format the text string
    If EndLine1 = 0 Then        ' single line of text in textbox
        MyText = Text2
    Else
        MyText = Left(Text2, EndLine1 - 1) _
                & " " & TextBox3.Value _
                & Right(Text2, Len(Text2) - EndLine1)
    End If
    MsgBox (MyText)     ' for testing
    '-------------------------------------------------------------------------
    '- put text into the clipboard
    With MyDataObject
        .SetText MyText
        .PutInClipboard
    End With
    '-------------------------------------------------------------------------
    Set MyDataObject = Nothing
End Sub
'-----------------------------------------------------------------------------

 

[depcdivr]

With sme modification this code worked well. Is there any way to insert a number of tabs in in front of each subsequent line so that the text will line up on lines 2 thru the end?

 

[BrianB]

Not sure exactly what you want.

This is how include a new line with required number of tabs with each addition (VBA contains system variables for Carriage Return(13), LineFeed(10) and Tab(9) as indicated below).

NB. This adds a final redundant "empty" line with tabs. Perhaps you could add code to remove it in the Form_Close event ? :-

'- PARTIAL CODE
'- Include new line after MyText with 2 Tabs
MyText = MyText & vbCr & vbLf & vbTab & vbTab

 

[depcdivr]

What I am trying to do is to make the description on line 2+ line up with the description on line 1. Unfortunately there is a field that I need to report before the description. This field can be anywhere from 7 to 18 characters. This can span either 1 or two tabs widths so making it line up is proving to be a challange.

 

[BrianB]

Could check the length of the extra text ... ?
eg. to add MyString

x=Len(MyString)
if x<10 Then ' add 2 tabs
MyText = MyText & vbCr & vbLf & vbTab & vbTab &MyString
Else ' add 1 tab
MyText = MyText & vbCr & vbLf & vbTab &MyString
End If