By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
457,705 Members | 1,368 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 457,705 IT Pros & Developers. It's quick & easy.

Exporting Multiple Tables from Access

P: 1

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
  1. Sub ExportTable()
  3. 'Define Variables
  4. SelectTableToExport = DLookup("[Table]", "dbo_000_DataCubeProcessing", "[Select] = True")
  5. TableSaveName = DLookup("[ExportFileName]", "dbo_000_DataCubeProcessing", "[Select] = True")
  7. 'Export file to network
  8. DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, SelectTableToExport, "H:\" & TableSaveName, True
  10. 'Reset Table
  11. DoCmd.SetWarnings False
  12. DoCmd.RunSQL "UPDATE Dbo_000_DataCubeProcessing SET [Select]=No"
  13. DoCmd.SetWarnings True
  15. End Sub
Jan 2 '08 #1
Share this Question
Share on Google+
2 Replies

Expert 2.5K+
P: 2,653
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
  1. Public Function ExportTable2Excel(strTableName As String, _
  2.                                   strFileNameToExportTo As String) As Boolean
  4.     On Error GoTo ExportFailed
  5.     DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
  6.         strTableName, "H:\" & strFileNameToExportTo, True
  7.     ExportTable2Excel = False
  9. ExitFunc:
  10.     Exit Function
  12. ExportFailed:
  13.     ExportTable2Excel = True
  14.     Resume ExitFunc
  16. End Function
Then run update query:
Expand|Select|Wrap|Line Numbers
  1. UPDATE Dbo_000_DataCubeProcessing SET [Select]=ExportTable2Excel([Table], [ExportFileName]) WHERE [Select]=True;

P.S. Make backup first. LOL.
Jan 2 '08 #2

Expert Mod 15k+
P: 31,754
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).
Jan 3 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.