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

[inddon]

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 =
"OBJECT_NAME,OBJECT_TYPE,COUNT_RECORDS
WORLD1,Table,604
WORLD2,VIEW,"

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.

Regards,
Don

 

[p45cal]

Could you give us (or link to) a sample original lText_CSV file? (Not one you saved from Excel as a CSV file!)

 

[inddon]

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

Regards,
Don

 

[p45cal]

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 = "OBJECT_NAME,OBJECT_TYPE,COUNT_RECORDS" & Chr(10)
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)
DoEvents

tbl.HeaderRowRange(1, 1) = "SR."
RowFormula = "=ROW()-ROW(" & tbl.Name & "[[#Headers],[SR.]])"
tbl.ListColumns("SR.").DataBodyRange.FormulaR1C1 = RowFormula
DoEvents
    
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
DataObj.GetFromClipboard
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
DoEvents
Call ClearClipboard
End Sub

 

[inddon]

Wow, this works good.

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.

Regards,
Don

 

[p45cal]

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.

 

[inddon]

Super. Thank you @p45cal