Export all tables to a spreadsheet(s)

[SteveC24]

Hello,

I have a DB which contains about 110 tables, all have the same fields in (I know, poor design, it is an export from some dodgy 3rd party bit of software).

Anyway - I would like to export them all to either seperate spreadsheets, or a single spreadsheet, either as seperate sheets, or all in one.

I'm really not very bothered HOW it get it out... I just need to get it out!

I would imagine it is a time for a loop in vba, but I'm afraid my knowledge of things is limited at best!


Any help would be much appreciated!

 

[Dennisk]

look up DoCmd.TransferSpreadSheet. This will do the job for you.

 

[SteveC24]

That bit I know - it is the looping bit I don't really understand!

The tables don't have nice logical names either - they are client names, which is less than helpful!

 

[MSAccessRookie]

That bit I know - it is the looping bit I don't really understand!

The tables don't have nice logical names either - they are client names, which is less than helpful!

Since there are "ONLY" 110 tables, you can always create Table 111 that contains the 110 names associated with the tables, and use that as part of the looping process (unless, of course, you want to add a new column to all 110 tables that contains the name associated with the table).

 

[SteveC24]

The only way to get those table names into a table is to type them (well, copy & paste) them manually - they aren't the same names as we have in our contact management system, so it's not like I can export them.

If I have to do that - I may as well probably export them all manually - which obviously is possible, but a real pain in the bum!

This isn't a one off either - otherwise I'd have done it manually.


Any other ideas?