I am looking to load a .txt file into a VBA array (in an access VBA), manipulate it there, and then paste the manipulated array into an access table. I will loop this macro then through round about 500 .txt files and populate the database with years of data.
I have done it before using Excel in this way:Populating the sheet for 1 .txt file, manipulating the data, loading into the database, clearing the sheet, and loading the next txt file and loop till all files have been processed. However, this takes years and it becomes obvious that Excel is not really needed, since everything is stored in Access anyway.
Since then, I have tried to figure out how to do it in access straight away, without using excel, but I have been struggling. I found some nice code on this board here:
Load csv file into a VBA array rather than Excel Sheet
and tried to amend it so it would work for me. Especially The_Barman's Answer at the bottom seems simple and very interesting.
I am new to arrays, VBA, SQL, basically everything, so maybe there is some big mistake I am making here that is easy to resolve. See my code below:
Sub LoadCSVtoArray()
Dim strfile As String
Dim strcon As String
Dim cn As ADODB.Connection
Dim rs As Recordset
Dim rsARR() As Variant
Set cn = New ADODB.Connection
strcon = "Provider=Microsoft.JET.OLEDB.4.0;Data Source=" & "\\filename.txt" &
";Extended Properties=""text;HDR=Yes;FMT=Delimited"";"
cn.Open strcon
strSQL = "SELECT * filename.txt;"
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
I dont even know if the bottom part of the code works, because an error message pops up that the file is not found. The interesting thing is, if I debug and copy the value of strcon into windows "run", it opens the correct file. So I guess the path is correct? Can I even open a .txt file through an ADODB connection? Right now I am a bit confused if this is possible and if it is the best solution.
Some more background regarding the text files I am trying to save into the array:
-They are output from another program, so it is always the same structure and very oreganized
- it comes in this format: Column a Column b .... data 1 data 1 data 2 Data 2 ...
and so on.
If possible, I would like to retain this structure, and even safe it as a table with the first row as column headers.
