Export multiple tables at once to Excel. HELP!

[Kay Ree]

Does anybody out there have any code or knows how to do
export multiple tables in Access to Excel at once? Please
help. Big thank you in advance.

 

[Van T. Dinh]

If you use VBA, write a Sub using multiple instances of the
TransferSpreadsheet Method.

If you use Macro, write a Macro using multiple instances on the
TransferSpreadsheet Macro action.

Check Access / Access VB Help on the TransferSpreadsheet Method or action as
appropriate.

 

[Kay Ree]

How would I do it for all the tables in the database? I
can do it for one in a macro; but the tables have
different names. Any ideas?

 

[Van T. Dinh]

As I wrote, use multiple TransferSpreadsheet actions, one for each Table, in
your Macro.

In each action, you specify a different Table in your Database. Thus, use
as many TransferSpreadsheet actions as the number of Tables you have.

I assume you know that you can have more than 1 action in a Macro.

Or you are thinking of something more sophisticated / complicated???

 

[Kay Ree]

Yes, I was thinking of something more complicated because
I have a lot of tables in there 50 tables and doing
multiple macros with different names for the table would
take up a lot of time. Any idea on how to export all
tables in the database to an Excel workbook?

 

[Van T. Dinh]

Well ... I don't know how to do it in Macro as I don't use Macros enough,
especially that I am not sure whether it is possible to enumerate the Table
names and to write the loop structure in Macros (I don't think it is
possible but I may be wrong).

You certainly can do with a VBA Sub. The algorithm should be something
like:

Create a Recordset of Table names (think local and / or linked Tables)
If Recordset is not empty
Go to 1st returned Row in the Recordset
End If

Do While Not Recordset.EOF
Export the Table using DoCmd.TransferSpreadsheet
Move to next Row in the Recordset
Loop 'Return to do while.

I leave the coding details for you to do. You need to check whether an
Excel workbook can have 50 worksheets or not.