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:

Expand|Select|Wrap|Line Numbers

 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).

Expand|Select|Wrap|Line Numbers

 
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:

Expand|Select|Wrap|Line Numbers

 
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).

Exporting Multiple Tables from Access

re: Exporting Multiple Tables from Access

re: Exporting Multiple Tables from Access

Similar Microsoft Access / VBA bytes

/bytes/development

/bytes/it

/bytes/about