VBA excel populate Listobject with clipboard content

Question

I am trying to insert the content of the clipboard (text) into a listobject.

CODE:

   Dim myTbl As ListObject
   Set myTbl = ThisWorkbook.Sheets("applianswer").ListObjects(1)

    Dim obj As New DataObject
    obj.GetFromClipboard

    ' this is to be sure that the text is actually there
    MsgBox obj.GetText


    'this is to be sure that the databodyrange is actually existing. 2 as result
    MsgBox "nr of rows " & myTbl.ListRows.Count

    myTbl.DataBodyRange.ClearContents
    'this line gives error:
    myTbl.Paste Destination:=myTbl.ListColumns(1).DataBodyRange.iTem(1)

error I get:

Run-time error 438 Object does not support this property or method

The following solution is not optimal for me:

myTbl.ListColumns("text").DataBodyRange.iTem(1).value = obj.GetText

because then everything is inserted in the first cell I want the text to be distributed along the cells where there are line breaks etc.

Some ideas why the paste is not done correctly? thanks.

Answer 1

The problems pasting into a table (ListObject) are always going to be very specific to your own situation. For example, if I have the following lines in my clipboard:

row one for listobject
row two for listobject

I probably want each line to paste into its own row in the table, but do I want the entire line in the first cell or each word in the clipboard line separated into different columns in the table? What happens if you have an empty (blank) line in the clipboard? Different problems will require modifying your solution to match your user's expectations. So while a Paste may work for you (and certainly may work as a manual operation), if you are performing the action with VBA you can be much more specific.

For my example below, I'm clearing and deleting all the rows in the table -- except for one row. I've found in working with tables that Excel seems much happier if there is at least one row in the DataBodyRange.

'--- clear the table data and delete all the rows, because
'    the incoming data may have a different number of rows
With myTbl
    .DataBodyRange.ClearContents
    Do While .DataBodyRange.Rows.Count > 1
        .DataBodyRange.Rows(.DataBodyRange.Rows.Count).Delete
    Loop

Next, set up an object that gives me access to one of the rows in the table. In this case, it's the only row in the table.

    Dim i As Long
    Dim thisRow As ListRow
    Set thisRow = .ListRows(1)

Then, I'll create an array of lines from the text in the clipboard, splitting along the EOL character:

    Dim lines() As String
    lines = Split(clipBoardText, vbCrLf)

Now it's a simple matter of looping over the lines and copying the clipboard text into (new) rows in the table.

Here is the whole example routine:

Option Explicit

Sub ClipboardToTable()
    Dim myTbl As ListObject
    Set myTbl = ThisWorkbook.Sheets("applianswer").ListObjects(1)

    Dim obj As DataObject
    Dim clipBoardText As String
    Set obj = New DataObject
    obj.GetFromClipboard
    clipBoardText = obj.GetText

    '--- clear the table data and delete all the rows, because
    '    the incoming data may have a different number of rows
    With myTbl
        .DataBodyRange.ClearContents
        Do While .DataBodyRange.Rows.Count > 1
            .DataBodyRange.Rows(.DataBodyRange.Rows.Count).Delete
        Loop

        Dim i As Long
        Dim thisRow As ListRow
        Set thisRow = .ListRows(1)

        Dim lines() As String
        lines = Split(clipBoardText, vbCrLf)
        For i = LBound(lines) To UBound(lines)
            If Len(lines(i)) > 0 Then
                '--- use this next statement if you want the entire
                '    line from the clipboard into the first cell on
                '    this table row
                thisRow.Range.Cells(1, 1).Value = lines(i)

                '--- use this next section if you want to split the
                '    clipboard line and distribute to the table columns\
                '    (example splits on the spaces)
                Dim j As Long
                Dim columnOffset As Long
                Dim parts() As String
                Dim numberOfParts As Long
                parts = Split(lines(i), " ")
                columnOffset = IIf(LBound(parts) = 0, 1, 0)
                numberOfParts = UBound(parts) + columnOffset
                '--- we might have to add new columns to fit the data
                Do While numberOfParts > .ListColumns.Count
                    .ListColumns.Add Position:=(.ListColumns.Count + 1)
                Loop
                For j = LBound(parts) To UBound(parts)
                    thisRow.Range.Cells(1, j + columnOffset).Value = parts(j)
                Next j

                Set thisRow = .ListRows.Add(AlwaysInsert:=True)
            End If
        Next i

        '--- delete the last listrow because it's empty from
        '    the loop above
        .ListRows(i).Delete
    End With
End Sub