CODEWITHSUNDEEP
vbaexcellistboxclipboard
ppw
ppw

Reputation: 155

Adding items from a listbox into the clipboard in a excel paste friendly format

The Aim: using VBA (excel) to populate the clipboard with a table (from a listbox) in an excel friendly format so that I can then paste (ctrl+V) into excel and the values are split into the correct columns and rows as they were in the original listbox.

The Problem: I can't seem to get the formatting right; when I paste the contents of my clipboard into excel each row is contained in one cell (the first column) separated with "," (which is what I have tried to use as a sort of "delimiter" - not sure if this is correct or not?)

I am using the API workaround method to populate the clipboard and I don't want to wall you to death with code but here is the most important bit (I feel anyway - ask me if you need to see more code).

Private Sub btnCopyTable_Click()    

Dim I As Long
Dim J As Long
Dim tmp As String
Dim arrItems() As String
Dim clipboard As DataObject

ReDim arrItems(0 To lbIPActions.ColumnCount - 1)
For J = 0 To lbIPActions.ListCount - 1
    For I = 0 To lbIPActions.ColumnCount - 1
        On Error Resume Next ' Handles null values
        arrItems(I) = lbIPActions.Column(I, J)
        On Error GoTo 0
    Next I
    tmp = tmp & Join(arrItems, ",") & vbCrLf
Next J

MsgBox tmp

ClipBoard_SetData tmp

End sub

The Current Output:

1,1,Low,Controls,,LS,Do this,02-Oct-2015,Note 1,Brev
2,1,Low,Controls,,LS,Do that,02-Oct-2015,Note 2,Brev

The Current State: I have had a little look around and found a way to do it in code to a specific sheet, but my end user requires the freedom to copy it into numerous sheets in a very ad hoc fashion. I know there is always the option to right click -> import text wizard, but again ctrl+V is the preferred method if possible.

As always, any help/advice is appreciated, thanks in advance.

Upvotes: 1

Views: 2277

Answers (1)

Ambie
Ambie

Reputation: 4977

Separate your string with Tab in each line and it'll work. Like so:

Dim I As Long
Dim J As Long
Dim textItem As String
Dim copyText As String
Dim clipboard As DataObject

For J = 0 To lbIPActions.ListCount - 1
    For I = 0 To lbIPActions.ColumnCount - 1
        textItem = vbNullString
        On Error Resume Next ' Handles null values
        textItem = lbIPActions.Column(I, J)
        On Error GoTo 0
        If I > 0 Then copyText = copyText & vbTab
        copyText = copyText & textItem
    Next I
    If J < lbIPActions.ListCount - 1 Then copyText = copyText & vbNewLine
Next J

ClipBoard_SetData clipboardText

Upvotes: 2

Related Questions