Solved: Export all Access tables to CSV

[xluser2007]

Hi All,

I have not used Access before.

I have an access mdb database file which contains many tables.

I have it stored as "C:\DS\SQLDataFundamentals.mdb"

I would like to extract all tables from this mdb file and store them in the same folder (i.e. ""C:\DS\") as a CSV file and also an Excel 95 file with the same name as the table

e.g. If a table in "SQLDataFundamentals.mdb" is called "DUAL", I would like to export this to both "C:\DS\DUAL.csv" and "C:\DS\DUAL.xls")

Could anyone please show me some code that i could run to allow this to happen?

Any help appreciated.

kind regards

[Demosthine]

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

[xluser2007]

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
Scott,

Thank you for your great code. It works fantastically.

Only one thing though. I pasted it in the module of the "C:\DS\SQLDataFundamentals.mdb", and ran it.

It outputted the *.CSV and *.xls files to a default My Documents Folder as opposed to the folder in which the mdb file is saved.

Could youn please explain how to amend to output in the same folder as the mdb file, for future use of this great VBA code?

thanks and regards

[Demosthine]

Evening again.

You can add the current Database's Path to the Output File using Application.CurrentProject.Path.

Take care.
Scott

[xluser2007]

Evening again.

You can add the current Database's Path to the Output File using Application.CurrentProject.Path.

Take care.
Scott

Hi Scott,

Thanks for your prompt reply.

I tried the following, as per your suggestion:

Option Compare Database


Public Sub ExportAllTables_to_CSV_and_XLS()

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, Application.CurrentProject.Path & obj.Name & ".csv", True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, obj.Name, Application.CurrentProject.Path & obj.Name & ".xls", True

End If

Next obj

End Sub

My main mdb file is located in:

"C:\Users\Username\Documents\PROGRAMMING\SQL\Datasets\SQLFUN2002.mdb"

If I run the above code from the mdb file, it exports all the CSV/ XLS files to:

"C:\Users\Username\Documents\PROGRAMMING\SQL" i.e. one level above where the mdb file is currently stored.

It's pretty close, but just for my understanding for future use, how would I export, in this case to the "C:\Users\Username\Documents\PROGRAMMING\SQL\Datasets" folder?

Thanks again for your patience and great help :).

regards

[hansup]

I think you need a backslash between the path and object name. Try this:

Dim strFolder As string
strFolder = Application.CurrentProject.Path & "\"
Then substitute

strFolder & obj.name[/vba] where you have[vba]Application.CurrentProject.Path & obj.Name

[xluser2007]

I think you need a backslash between the path and object name. Try this:
Dim strFolder As string
strFolder = Application.CurrentProject.Path & "\"
Then substitute
strFolder & obj.name where you haveApplication.CurrentProject.Path & obj.Name
This works very nicely hansup, thank you :friends:.

[LuciaLotter]

Thank you for the advice provided. I tried the code as well:



Option Compare Database
Public Sub ExportAll()
Dim strFolder As String
strFolder = Application.CurrentProject.Path & ""
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, "Table1 Export Specification", obj.Name, strFolder & obj.Name & ".csv", True
End If
Next obj
End Sub


This exports the first table, but not the second one with a table name of "users". The error message is "The Microsoft Access database engine could not find the object 'users#csv'.". Your assistance will be highly appreciated.

[SamT]

This 13 yo thread is closed.

Please open another thread with your question.