troubleshooting Question

Create Multiple CSV Files from Same Table using Loop in VBA

Avatar of David Ackerman
David Ackerman asked on
Microsoft AccessProgrammingVisual Basic ClassicDatabasesVBA
2 Comments1 Solution252 ViewsLast Modified:
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
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 2 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 2 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros