Export dynamic number of Access tables to Excel

Question

I am trying to export multiple Access tables to Excel.

It works if I nominate the tables and RS1 etc., but I need to do this as a dynamic number of Access tables into one Excel report. (This is a weekly customer report and can vary from week to week, but need the export to be in one report, not multiple Excel exports.)

The following works.

Dim rs1 As DAO.Recordset,
Dim rs2 As DAO.Recordset

The below returns an error.

Dim rs() As DAO.Recordset

I have a counter that can count every time the Access table changes, but the RS part now shows an error.

myRowCount = 1            
Set rs(myRowCount) = CurrentDb.OpenRecordset(mycustomers, dbOpenSnapshot)

Answer 1

You could use an array:

Dim Recordsets()    As DAO.Recordset
Dim Index           As Integer

ReDim Recordsets(1 To 3)

Set Recordsets(1) = CurrentDb.OpenRecordset("Customer")
Set Recordsets(2) = CurrentDb.OpenRecordset("Client")
Set Recordsets(3) = CurrentDb.OpenRecordset("Contact")

For Index = LBound(Recordsets) To UBound(Recordsets)
    Debug.Print Index, Recordsets(Index).RecordCount
    Recordsets(Index).Close
Next