将 csv 文件加载到 VBA 数组而不是 Excel 工作表中 [英] Load csv file into a VBA array rather than Excel Sheet

问题描述

我目前可以通过下面的代码上传数据然后处理表格，将 csv 文件数据输入到 Excel VBA 中，这肯定不是最好的方法，因为我只对某些数据感兴趣并在使用后删除表格数据:

I am currently able to enter csv file data into Excel VBA by uploading the data via the code below then handling the table, surely not the best way as I am only interested in some of the data and delete the sheet after using the data:

Sub CSV_Import() 
Dim ws As Worksheet, strFile As String 

Set ws = ActiveSheet 'set to current worksheet name 

strFile = Application.GetOpenFilename("Text Files (*.csv),*.csv", ,"Please select text file...") 

With ws.QueryTables.Add(Connection:="TEXT;" & strFile, Destination:=ws.Range("A1")) 
     .TextFileParseType = xlDelimited 
     .TextFileCommaDelimiter = True 
     .Refresh 
End With 
End Sub 

是否可以简单地将 csv 加载到 VBA 中的二维变量数组中，而不是通过使用 Excel 工作表?

Is it possible to simply load the csv into a two dimensional variant array in VBA rather than going through the use of an excel worksheet?

推荐答案

好的，经过研究，我想到的解决方案是使用 ADODB(需要引用 ActiveX 数据对象，这将 csv 文件加载到数组中，而不需要循环行列.确实需要数据处于良好状态.

OK, after looking into this, the solution I have arived at is to use ADODB (requires reference to ActiveX Data Objects, this loads the csv file into array without cycling the rows columns. Does require the data to be in good condition.

Sub LoadCSVtoArray()

strPath = ThisWorkbook.Path & ""

Set cn = CreateObject("ADODB.Connection")
strcon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strPath & ";Extended Properties=""text;HDR=Yes;FMT=Delimited"";"
cn.Open strcon
strSQL = "SELECT * FROM SAMPLE.csv;"

Dim rs As Recordset
Dim rsARR() As Variant

Set rs = cn.Execute(strSQL)
rsARR = WorksheetFunction.Transpose(rs.GetRows)
rs.Close
Set cn = Nothing

[a1].Resize(UBound(rsARR), UBound(Application.Transpose(rsARR))) = rsARR

End Sub

这篇关于将 csv 文件加载到 VBA 数组而不是 Excel 工作表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！