We help IT Professionals succeed at work.
Get Started

Create Multiple CSV Files from Same Table using Loop in VBA

251 Views
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

001-Q_SampleBillQuery-00400-JEPds
Query Name:  001-Q_SampleBillQuery-00500-IndividualJes

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

Open in new window

Comment
Watch Question
CERTIFIED EXPERT
Top Expert 2016
Commented:
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