title: Parse Tab Separated Values in VBA
body: <p>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. </p>
  
  <p>Getting the text from the clipboard is easy enough:</p>
  
  <pre><code>Dim dataObj As DataObject
  Dim clipString As String
  Set dataObj = New DataObject
  dataObj.GetFromClipboard
  clipString = dataObj.GetText
  </code></pre>
  
  <p>Then I split the input into rows:</p>
  
  <pre><code>Dim strRows As Variant
  
  strRows = Split(clipString, vbNewLine)
  </code></pre>
  
  <p>Next I try to extract the headers:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>Finally I extract the rows:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>However, I am getting an error. On the line</p>
  
  <pre><code>headers.Add strCols(col), col
  </code></pre>
  
  <p>Access comes back with Run-time error '12': type mismatch.</p>
  
  <p><strong>Update</strong> fixed the problem above, thanks for the suggestions. Now I am getting an error on the line</p>
  
  <pre><code>Set cells = CreateObject(Scripting.Dictionary)
  </code></pre>
  
  <p>424: Object required.</p>
  
  <p>Any hints as to what I'm diong wrong - VBA isn't really my forte.</p>
  
  <p><strong>Update 2</strong> fixed this issue too (thanks for suggestion below). The code now works.</p>