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 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.
1 Answer
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
James Sterling reports on renewable energy developments, climate policy, ecological conservation, and green tech innovations around the globe.
