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

问题描述

目前，我可以通过以下代码将数据输入到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?

推荐答案

p>好的，看了这个，solu我非常喜欢使用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 Sheet的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！