Good Evening.
Access has two built in Functions that allow you to do exactly what you want quite easily.
If you refer to the MsAccess help file, you will find information on the following three topics that will give you a more detailed information so you can modify the code as needed.
AllTables Collection
TransferSpreadsheet
TransferText
That being said, the code below will do exactly as you requested. Note that there are hidden Tables that are used my MsAccess. Their names begin with "MSys" and can easily be eliminated by this simple conditional statement.
Option Compare Database
Public Sub ExportAll()
Dim obj As AccessObject, dbs As Object
Set dbs = Application.CurrentData
For Each obj In dbs.AllTables
If Left(obj.Name, 4) <> "MSys" Then
DoCmd.TransferText acExportDelim, , obj.Name, obj.Name & ".csv", True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, obj.Name, obj.Name & ".xls", True
End If
Next obj
End Sub
Take Care.
Scott