Export multiple tables to Excel

  • Thread starter Thread starter Kay Ree
  • Start date Start date
K

Kay Ree

Does anybody have any code on how to export multiple
tables from an Access database to Excel? Say I have 50
tables in an Access database, how do I do a multi export
to Excel. I know I can do it using a Macro
TransferSpreadsheet but am not familiar with VBA. Any code
to help me do it would be much appreciated. Thank you!
 
Kay,

Try this (watch out for line wrapping):

Function TableExpoter()

Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Dim tdfs As DAO.TableDefs

Set dbs = CurrentDb()
Set tdfs = dbs.TableDefs

For Each tdf In tdfs
DoCmd.TransferSpreadsheet acExport,
acSpreadsheetTypeExcel9, tdf.Name, "C:\Path\" & tdf.Name
& ".xls", True
Next

Set tdfs = Nothing
Set dbs = Nothing

End Function

HTH

Jake
 
Jake,

I am not familiar with VBA, I posted the code in the VB
editor, made some changes am its not working. Any ideas?
Does tdf or tdfs have to be called the table names in my
database?
 
What version of Access are you using? If it is 2000 or
above, open the VBA editor and go to Tools, then
References, and make sure that Microsoft DAO 3.something
(probably 3.51 or 3.6) is checked, then try again.

Jake
 
Back
Top