Link to home
Start Free TrialLog in
Avatar of David Ackerman
David Ackerman

asked on

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.

This is the table I'm working from:

User generated image
These are the two queries I am using in my code

Query Name :001-Q_SampleBillQuery-00400-JePds

User generated image
Query Name:  001-Q_SampleBillQuery-00500-IndividualJes

User generated image
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
            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

    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 + 1

Open in new window

Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
a solution was provided