使用VBA将.csv字符串读入多维数组
使用VBA将.csv字符串读入多维数组
提问于 2017-07-19 05:27:33
我正在尝试自动将数据导入到我在Excel中构建的工具中。其思想是将数据从.csv文件直接读取到数组中，或者将数据作为字符串读取，然后使用空格“”和逗号"，“作为分隔符，后跟一个数组对其进行解析。我已经走了这么远了：
Public Sub ImportData()
Dim myData as String, strData() as String
Dim thisFile as String
thisFile = ActiveWorkbook.Path & "\" & "s.csv"
Open thisFile For Binary As #1
myData = Space$(LOF(1))
Get #1, , myData
Close #1
End Sub这里"myData“是由逗号和空格分隔的数据字符串(新列用逗号分隔，新行用空格分隔)。
如何将其重新构建为多维(2D)数组，以便将其打印到正在处理的工作表上，或直接从内存中引用？还是有一种更简单的方法？
回答 1
Stack Overflow用户
发布于 2017-07-19 10:52:04
这是@Tim建议的实现
Option Explicit
Public Sub OpenFile()
Dim rawData As String, lineArr As Variant, cellArr As Variant
Dim ubR As Long, ubC As Long, r As Long, c As Long
Open ActiveWorkbook.Path & "\" & "s.csv" For Binary As #1
rawData = Space$(LOF(1))
Get #1, , rawData
Close #1
If Len(rawData) > 0 Then
'If spaces are delimiters for lines change vbCrLf to " "
lineArr = Split(Trim$(rawData), vbCrLf)
ubR = UBound(lineArr) + 1
ubC = UBound(Split(lineArr(0), ",")) + 1
ReDim arr(1 To ubR, 1 To ubC)
For r = 1 To ubR
If Len(lineArr(r - 1)) > 0 Then
cellArr = Split(lineArr(r - 1), ",")
For c = 1 To ubC
arr(r, c) = cellArr(c - 1)
Next
End If
Next
ActiveSheet.Range(Cells(1), Cells(ubR, ubC)) = arr 'Place array on the sheet
End If
End Sub页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/45177404
复制相关文章
相似问题
腾讯云开发者
