VBA to import data from access database to excel (1 Viewer)

[aryanaveen]

HI All,

Please help me in completing the my macro.

I need a VBA code which will copy all tables from access database to Excel file.

Access database path - C:\tracker

Database file name is Naveen.accdb.

Now I need a code which will open Naveen.accdb from c drive and imports tables to new excel file.

Thank you

 

[Ranman256]

in a macro, use transferspreadsheet.
the last parameter is the sheet name, of the same workbook.

 

[sneuberg]

Something like:

Sub ExportTables()

Dim AccessApp As Access.Application
Dim tdef As DAO.TableDef
Set AccessApp = CreateObject("Access.Application")
AccessApp.OpenCurrentDatabase ("C:\tracker\Naveen.accdb")
For Each tdef In AccessApp.CurrentDb.TableDefs
    If Left(tdef.Name, 4) <> "MSys" Then
        AccessApp.DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, tdef.Name, "C:\tracker\Naveen.xls"
    End If
Next tdef
AccessApp.CloseCurrentDatabase

End Sub

This puts each table in a different sheet without specifying a range. Talking about the Range argument it says here.

When you export to a spreadsheet, you must leave this argument blank. If you enter a range, the export will fail.

 

[aryanaveen]

Thank you

but the codes throws an error at below line

Dim AccessApp As Access.Application

compile error : User-defined type not defined

 

[sneuberg]

I tested this running it from Access? In which application are you running this?

You could add the reference to Access (Tools, References, select Microsoft Access XX.0 Object Library) or try late binding i.e.,

Edit: Don't use this. See Post #10

Sub ExportTables()

Dim AccessApp As Object
Dim tdef As DAO.TableDef
Set AccessApp = CreateObject("Access.Application")
AccessApp.OpenCurrentDatabase ("C:\tracker\Naveen.accdb")
For Each tdef In AccessApp.CurrentDb.TableDefs
If Left(tdef.Name, 4) <> "MSys" Then
AccessApp.DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, tdef.Name, "C:\tracker\Naveen.xls"
End If
Next tdef
AccessApp.CloseCurrentDatabase

End Sub

 

[Bilbo_Baggins_Esq]

I suspect the OP may be running this code from Excel...

 

[Bilbo_Baggins_Esq]

aryanaveen, you need to run sneuberg's code from a module in ACCESS, not Excel.

 

[Gasman]

HI All,

Please help me in completing the my macro.

I need a VBA code which will copy all tables from access database to Excel file.

Access database path - C:\tracker

Database file name is Naveen.accdb.

Now I need a code which will open Naveen.accdb from c drive and imports tables to new excel file.

Thank you

Why not record a macro for the first table and then amend to suit for the other tables?

 

[Bilbo_Baggins_Esq]

um, because that's MUCH more tedious than just using the code provided above.

 

[sneuberg]

aryanaveen, you need to run sneuberg's code from a module in ACCESS, not Excel.

But if you want to run in from Excel then use the following code. My previous post for late binding wasn't complete. I tested this one in Excel

Sub ExportTables()

Dim AccessApp As Object
Dim tdef As Object
Const acExport = 1
Const acSpreadsheetTypeExcel8 = 8
Set AccessApp = CreateObject("Access.Application")
AccessApp.OpenCurrentDatabase ("C:\tracker\Naveen.accdb")
For Each tdef In AccessApp.CurrentDb.TableDefs
    If Left(tdef.Name, 4) <> "MSys" Then
        AccessApp.DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, tdef.Name, "C:\tracker\Naveen.xls"
    End If
Next tdef
AccessApp.CloseCurrentDatabase

End Sub

Note that this just the minimum code needed and doesn't have any error checking.

 

[Bilbo_Baggins_Esq]

But if you want to run in from Excel then use the following code. My previous post for late binding wasn't complete. I tested this one in Excel

Sub ExportTables()

Dim AccessApp As Object
Dim tdef As Object
Const acExport = 1
Const acSpreadsheetTypeExcel8 = 8
Set AccessApp = CreateObject("Access.Application")
AccessApp.OpenCurrentDatabase ("C:\tracker\Naveen.accdb")
For Each tdef In AccessApp.CurrentDb.TableDefs
    If Left(tdef.Name, 4) <> "MSys" Then
        AccessApp.DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, tdef.Name, "C:\tracker\Naveen.xls"
    End If
Next tdef
AccessApp.CloseCurrentDatabase

End Sub

Note that this just the minimum code needed and doesn't have any error checking.

...And need to add a reference to the Access Object Library in Excel's VBE.

 

[sneuberg]

...And need to add a reference to the Access Object Model in the Excel's VBE.

No that's not required. It's set up for late binding