Mass Exporting Tables from Access to Excel

Recently I have been working with some Access databases that have large amounts of tables in them. Reviewing data in numerous Access tables can be burdensome and it would be easier to review them is they were in Excel.

I found this post that runs some code that does exactly what I need, here are the steps.

• In Access under the 'Database Tools' option click 'Visual Basic'
• In the new window click 'Run' then 'Run Macro'
• Type a name in like "AccessMacro" then click 'Create'
• In the new screen that appears copy and paste the code below replacing anything that was originally in the new window

Sub AccessMacro()

Dim tbl As TableDef
For Each tbl In CurrentDb.TableDefs
  DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, tbl.Name, "C:\Users\Josh\Desktop\NexGen\Test2.xls", True, tbl.Name
Next

End Sub

You will need to adjust the area that is in bold above to the proper path on your computer when the file will be exported to.

Next click Run, then run macro and that's it!

If you hit an error that says 'User Defined Type Not Defined' simply do the following:

Tools > Reference... > (Check) Microsoft DAO 3.6 Object Library > OK

Below is the link to the original site that I found this information:

http://stackoverflow.com/questions/4036772/how-to-export-all-tables-from-an-access-database-into-excel-a-sheet-for-each-t