vba 用于读取 CSV 文件的 Excel 用户定义函数？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8894405/
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
An Excel user-defined function to read CSV files?
提问by Richard H
I am looking for a VBA user-defined function that will read in the contents of a .csv file and return the results as an array to populate the cells of a worksheet, i.e something like this:
我正在寻找一个 VBA 用户定义的函数，它将读取 .csv 文件的内容并将结果作为数组返回以填充工作表的单元格，即像这样：
=CSV_Read("path/to/file.csv")
I have found thisbut looks proprietary/shareware. Does anyone know of any alternatives? Or what the best approach to rolling my own in VBA would be?
我找到了这个，但看起来是专有/共享软件。有谁知道任何替代方案？或者在 VBA 中滚动我自己的最佳方法是什么？
回答by shahkalpesh
In your VBA code, add reference to "Microsoft Scripting Runtime".
Add a module, paste the following code.
在您的 VBA 代码中，添加对“Microsoft Scripting Runtime”的引用。
添加一个模块，粘贴以下代码。
Option Explicit
Public Function CSV_Read(ByVal path As String) As Variant
Dim fso As Scripting.FileSystemObject
Dim tsm As Scripting.TextStream
Dim data
Dim text As String
Dim allRowsInArray As Variant
Dim currentLineToArray As Variant
Dim columnCount As Integer
Dim columnCounter As Integer
Dim rowCounter As Integer
Dim rowCount As Integer
Set fso = New Scripting.FileSystemObject
Set tsm = fso.OpenTextFile(path, ForReading)
rowCounter = 0
If Not tsm.AtEndOfStream Then
text = tsm.ReadAll
tsm.Close
Set tsm = Nothing
Set fso = Nothing
allRowsInArray = Split(text, vbCrLf)
rowCount = UBound(allRowsInArray)
Do While rowCounter < (rowCount + 1)
currentLineToArray = Split(allRowsInArray(rowCounter), ",")
If Not IsArray(data) Then
columnCount = UBound(currentLineToArray)
ReDim data(rowCount, columnCount)
End If
For columnCounter = 0 To columnCount
data(rowCounter, columnCounter) = currentLineToArray(columnCounter)
Next
rowCounter = rowCounter + 1
Loop
End If
CSV_Read = data
End Function
Sample CSV file data
示例 CSV 文件数据
a,b,c,d,e,f,g
1,2,3,4,5,6,7
7,6,5,4,3,2,1
q,w,e,r,t,y,u
Looking at the data, it has 7 columns and 4 rows.
查看数据，它有 7 列和 4 行。
Now, goto cell A1 and selectthe range from cell A1 to G4.
Type in the formula (=CSV_Read("the full path to your csv file"), in cell A1 (keeping the selection as it is).
现在，转到单元格 A1 并选择从单元格 A1 到 G4 的范围。
键入公式 ( =CSV_Read("the full path to your csv file"), 在单元格 A1 中（保持选择不变）。
Important:
Press CTRL + SHIFT + ENTER, once you have finished typing in the formula. This is array formula.
重要提示：
按下CTRL + SHIFT + ENTER，一旦你的公式输入完毕。这是数组公式。
