Parse Tab Separated Values in VBA

Question

I am trying to take clipboard data copied from excel (i.e. tab separated text) and parse it into a Collection of Dictionaries. The idea is that each row will be represented by a Dictionary which maps from headers to cell values. The first row in the copied data will contain the headers.

Getting the text from the clipboard is easy enough:

Dim dataObj As DataObject
Dim clipString As String
Set dataObj = New DataObject
dataObj.GetFromClipboard
clipString = dataObj.GetText

Then I split the input into rows:

Dim strRows As Variant

strRows = Split(clipString, vbNewLine)

Next I try to extract the headers:

Dim headers As New Collection
Dim strCols As Variant
strCols = Split(strRows(0), vbTab)

For col = LBound(strCols) To UBound(strCols) - 1
    headers.Add strCols(col)
Next

Finally I extract the rows:

Dim cells
Dim rows As New Collection

For i = 1 To UBound(strRows) - 1
    strCols = Split(strRows(0), vbTab)
    Set cells = CreateObject("Scripting.Dictionary")
    For col = 0 To UBound(strCols) - 1
        cells.Add headers.Item(col + 1), strCols(col)
    Next
    rows.Add cells
Next

However, I am getting an error. On the line

headers.Add strCols(col), col

Access comes back with Run-time error '12': type mismatch.

Update fixed the problem above, thanks for the suggestions. Now I am getting an error on the line

Set cells = CreateObject(Scripting.Dictionary)

424: Object required.

Any hints as to what I'm diong wrong - VBA isn't really my forte.

Update 2 fixed this issue too (thanks for suggestion below). The code now works.

Solution 1

For your second problem -- you need provide the string name of the target class, so it's actually

Set cells = CreateObject("Scripting.Dictionary")

Solution 2

I think col has to be string type.

headers.Add strCols(col), cstr(col)