Create Multiple CSV Files from Same Table using Loop in VBA
I am working on some VBA code to extract out data from a table/query into multiple csv files using a loop in vba but I keep getting a "Can't Assign to Array" error.
I want all records in a table with a specific date included in separate .csv files. I created two sub-queries to use in my code. One to create a summary of dates in the table and the other to use in the loop to create the output files (whether I need this or not, I don't know.
And this is the code I have so far. The error is occurring on Line 13.
I am still a novice at this so any help would be greatly appreciated!
Dim db As DAO.Database Dim records() As DAO.Recordset Dim EntryDate As String Dim i As Integer Dim fs As String fs = "C:\TestECI\IN_572_COMPANY_" & Format(Now(), "yyyymmdd") & "_814EN01_" Set records = db.OpenRecordset("SELECT * FROM 001-Q_SampleBillQuery-00400-JEPds") 'loop through records, get list of unique DUNS numbers 'get unique duns For Each Record In records If IsInArray(Record.EntryDate, Dates) Then continue Else ReDim Preserve Dates(1 To UBound(Dates) + 1) As String ' add value on the end of the array arr(UBound(arr)) = Record.EntryDate End If Next For Each EntryDate In Dates Set records = db.OpenRecordset("SELECT * FROM 001-Q_SampleBillQuery-00500-IndividualJes WHERE EntryDate =" & Dates) i = 2000 fs = fs & i & ".csv" DoCmd.TransferText acExportDelim, , records, fs, True i = i + 1Next