We help IT Professionals succeed at work.
Get Started

Create Multiple CSV Files from Same Table using Loop in VBA

Last Modified: 2017-04-23
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:

Source Table for CSV Exports
These are the two queries I am using in my code

Query Name :001-Q_SampleBillQuery-00400-JePds

Query Name:  001-Q_SampleBillQuery-00500-IndividualJes

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

Watch Question
Top Expert 2016
This problem has been solved!
Unlock 1 Answer and 2 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE