在 VBA 中解析制表符分隔值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1032088/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Parse Tab Separated Values in VBA
提问by jwoolard
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.
我正在尝试获取从 excel 复制的剪贴板数据（即制表符分隔的文本）并将其解析为字典集合。这个想法是每一行都将由一个字典表示，该字典从标题映射到单元格值。复制数据的第一行将包含标题。
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.
Access 返回运行时错误“12”：类型不匹配。
Updatefixed the problem above, thanks for the suggestions. Now I am getting an error on the line
更新解决了上述问题，感谢您的建议。现在我在线上遇到错误
Set cells = CreateObject(Scripting.Dictionary)
424: Object required.
424：需要对象。
Any hints as to what I'm diong wrong - VBA isn't really my forte.
关于我错在哪里的任何提示 - VBA 并不是我的强项。
Update 2fixed this issue too (thanks for suggestion below). The code now works.
更新 2 也解决了这个问题（感谢下面的建议）。代码现在可以工作了。
回答by Joel Goodwin
For your second problem -- you need provide the string name of the target class, so it's actually
对于你的第二个问题——你需要提供目标类的字符串名称，所以它实际上是
Set cells = CreateObject("Scripting.Dictionary")
回答by ozczecho
I think col has to be string type.
我认为 col 必须是字符串类型。
headers.Add strCols(col), cstr(col)
headers.Add strCols(col), cstr(col)
