Exporting tables - External table is not in expected format

[memaxt]

Hi all,

I'm currently using the following VBA to export all tables within my DB to Excel on seperate tabs:

Dim td As DAO.TableDef, db As DAO.Database
Dim out_file As String

out_file = CurrentProject.Path & "\" & "Backup.xls"

Set db = CurrentDb()
For Each td In db.TableDefs
If Left(td.Name, 4) = "MSys" Then
'We do not need MSys tables in excel file
Else
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
td.Name, out_file, True, Replace(td.Name, "dbo_", "") 'We do not need dbo prefix in sheetnames
End If
Next


But upon exporting I get the following error:

"Run-time error 3274'
External table is not in expected format"

It then hightlights the following line:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
td.Name, out_file, True, Replace(td.Name, "dbo_", "")

If i navigate to the directory i can see that it has exported it to excel(attempted), but half of the tables(tabs) are missing.


Any ideas?

Many thanks
Max

 

[Rx_]

This would be my idea on how to proceed. It might help for other related projects.

http://www.btabdevelopment.com/ts/default.aspx?PageId=10

Find code to move an Access Table to an Excel worksheet.
Also code sample to move all Table names to Excel.

Copy each of these to get an idea of how they work.
Then modify the for each table to export the data to a tab.

This is working code that can be modified to your DB name.

Once at that point, use Code Tags to post your code. That will allow others to evaluate well written code to assist you.
By posting working code and marking it as a solution, it will help many others later.