在 VBA 中解析制表符分隔值
我正在尝试获取从 Excel 复制的剪贴板数据（即制表符分隔的文本）并将其解析为字典集合。 这个想法是，每一行将由一个字典表示，该字典从标题映射到单元格值。 复制数据中的第一行将包含标题。
从剪贴板获取文本非常简单：
Dim dataObj As DataObject
Dim clipString As String
Set dataObj = New DataObject
dataObj.GetFromClipboard
clipString = dataObj.GetText
然后我将输入分成行：
Dim strRows As Variant
strRows = Split(clipString, vbNewLine)
接下来我尝试提取标题：
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
最后我提取行：
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
但是，我收到错误。 Access返回
headers.Add strCols(col), col
运行时错误“12”：类型不匹配。
更新修复了上述问题，感谢您的建议。 现在我在第 424 行收到错误
Set cells = CreateObject(Scripting.Dictionary)
：需要对象。
关于我哪里错了的任何提示 - VBA 并不是我的强项。
更新 2 也修复了此问题（感谢下面的建议）。 代码现在可以运行了。
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.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
对于你的第二个问题——你需要提供目标类的字符串名称，所以它实际上是
For your second problem -- you need provide the string name of the target class, so it's actually
我认为 col 必须是字符串类型。
headers.Add strCols(col), cstr(col)I think col has to be string type.
headers.Add strCols(col), cstr(col)