• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Reflect comma (,) separated plain text from a variable into a Table


Hello there,

I have the below variable which stores a text string (comma separated) from some previous process.
This needs to be reflected in a table (to be placed on Table Header Column 2).

Please find attached the Sample Workbook.xlsm, which copies the variable string in the clipboard and paste it in the table as seen in Fig 1.0
I would like it to be working as seen in Fig 1.1

The Could you please help, how this can be done in a much simpler way in VBA?

lText_CSV can contain more than 10,000 lines and more than 300 Columns

lText_CSV =

When you do a paste it includes everything on the second column:

Fig 1.0

This how it should be:

Fig 1.1

Thank you and look forward to hearing from you.



  • Sample workbook.xlsm
    32.1 KB · Views: 3
Last edited:
Could you give us (or link to) a sample original lText_CSV file? (Not one you saved from Excel as a CSV file!)
Hello @p45cal

Thank you for your reply.

There is no physical file assigned to lText_CSV.
The variable lText_CSV gets the value assigned from a API Webservice response and should be dumped into an Excel Table.
This is the reason I included a small sample data assigned to lText_CSV, imitating the API response..

I understood from someone to use Split functionality to an array. Not quite familiar on how this could be done. Any other smart working, most welcomed

Rough and ready, a new starter/tweak for PasteValues:
Sub PasteValues()
Dim tbl As ListObject, RowFormula As String, lText_CSV As String
Dim lWorksheet As Worksheet
lText_CSV = lText_CSV & "WORLD1,Table,604" & Chr(10)
lText_CSV = lText_CSV & "WORLD2,VIEW,"
' Debug.Print lText_CSV
Set lWorksheet = ThisWorkbook.Worksheets("Sheet1")
Set tbl = lWorksheet.ListObjects("Tbl_TableView")
Call DeleteTableRows(pTable:=tbl)

tbl.HeaderRowRange(1, 1) = "SR."
RowFormula = "=ROW()-ROW(" & tbl.Name & "[[#Headers],[SR.]])"
tbl.ListColumns("SR.").DataBodyRange.FormulaR1C1 = RowFormula
CopyTextToClipboard lText_CSV

Dim DataObj As DataObject, myString, myArray, i, results(), r, ColmCount, c, x 'these usually at the top of the sub.
Set DataObj = New DataObject
myString = DataObj.GetText(1)
'Debug.Print myString
myArray = Split(myString, vbLf)
x = Split(myArray(0), ",")
ColmCount = UBound(x)
ReDim results(0 To UBound(myArray), 0 To ColmCount)
For r = LBound(myArray) To UBound(myArray)
  x = Split(myArray(r), ",")
  For c = 0 To Application.Min(UBound(results, 2), UBound(x))
    results(r, c) = x(c)
  Next c
Next r
'lWorksheet.Range("C4").PasteSpecial Paste:=xlPasteAll ' Format:="Unicode Text", Link:=False, DisplayAsIcon:=False
lWorksheet.Range("C4").Resize(UBound(results) + 1, ColmCount + 1).Value = results
Call ClearClipboard
End Sub
Wow, this works good. :):awesome::cool:

Thank you very much @p45cal for taking the time to make it work. This is very useful and I have learned something good from you.

I will study your solution specially the array part. I will try it out with a bigger lText_CSV value.

Based on your solution I made a slight modification:
1. Excluded the CopyTextToClipboard. Assigned the variable lText_CSV to myString

Two questions:
1. Now the results are assigned to:
lWorksheet.Range("C4").Resize(UBound(results) + 1, ColmCount + 1).Value = results

Is it possible to assign the results to the table directly and how would the code be?
eg. Assign to Header Row --> column 2 = results


2. What datatypes these variables be?
Dim myString, myArray, i, results(), r, ColmCount, c, x 'these usually at the top of the sub.

Thank you.



  • 1659027147884.png
    6.3 KB · Views: 0
Last edited:
Is it possible to assign the results to the table directly and how would the code be?
tbl.HeaderRowRange.Cells(2).Resize(UBound(results) + 1, ColmCount + 1).Value = results
What datatypes these variables be?
They're all variants. If you must type them then
myString is, well, a String
myArray and results() should remain Variant
the rest can be Long.