Exporting Multiple Tables from Access

[danorourke99]

Hi,

In my Access database I have a table (dbo_000_DataCubeProcessing) which contains a list of tables that I need to export to Excel on a regular basis along with a checkbox for each. Once I select the relevant table via checkbox, I have VBA code written which will pick this table and can export to a pre-defined location on my network in Excel format. I was wondering if there exists code (e.g. some type of Loop procedure) whereby I can select multiple tables via this checkbox and the VBA code can export all of these at once?

This would be very useful as there are occasions where I to export 10-15 tables at the same time and would like to perform it in one VBA procedure instead of re-running each time

My current code is:

Sub ExportTable()
 
'Define Variables
SelectTableToExport = DLookup("[Table]", "dbo_000_DataCubeProcessing", "[Select] = True")
TableSaveName = DLookup("[ExportFileName]", "dbo_000_DataCubeProcessing", "[Select] = True")
 
'Export file to network
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, SelectTableToExport, "H:\" & TableSaveName, True
 
'Reset Table
DoCmd.SetWarnings False
DoCmd.RunSQL "UPDATE Dbo_000_DataCubeProcessing SET [Select]=No"
DoCmd.SetWarnings True
 
End Sub

Thanks!
Dan

[FishVal]

Hi, Dan.

I think you may try to run VBA export procedure in update query thus invoking it for each record. Anyway you already run an update query at the end of the export sequence. ;) Additionally this VBA function may return boolean value to update [Select] field (False of success, True if export failed).

Public Function ExportTable2Excel(strTableName As String, _
                                  strFileNameToExportTo As String) As Boolean
 
    On Error GoTo ExportFailed
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
        strTableName, "H:\" & strFileNameToExportTo, True
    ExportTable2Excel = False
 
ExitFunc:
    Exit Function
 
ExportFailed:
    ExportTable2Excel = True
    Resume ExitFunc
 
End Function
 

Then run update query:

UPDATE Dbo_000_DataCubeProcessing SET [Select]=ExportTable2Excel([Table], [ExportFileName]) WHERE [Select]=True;
 

Regards,
Fish

P.S. Make backup first. LOL.

[NeoPa]

That's an ingenious approach Fish :)
Just as an alternative (better or worse often depends on the OP's preferences) I would suggest using a DAO RecordSet (Basic DAO recordset loop using two recordsets) approach.
The RecordSet would be the SQL that selects just the records required (WHERE ([SELECT])).
Each iteration through the loop would include an "export" as well as a couple of lines to reset the [SELECT] field (using .Edit & .Update).