Exporting entire Access database into Excel

[Coelho_c]

I have an Access database with many tables and queries which needs to be exported into Excel. IS there a quicker way of exporting this into Excel than doing each object seperately?

 

[dk]

Hi,

One way is to use a macro. The following code will only work in Access 2000 along with Excel 2000 (or XP). Just add this code to a module in your database and run it. Let me know how you get on

Option Explicit

Sub ExportAll()
    Dim oXLApp As Object
    Dim oADOXCat As Object, oADOXTable As Object
    Dim lngFieldCount As Long
    Dim oADORs As Object

    'Code uses ADO and ADOX to export all tables and queries into and new
    'Excel workbook (one table/query per worksheet).

    'WILL ONLY WORK WITH ACCESS 2000/EXCEL 2000 OR LATER

    'Daniel Klann March 2003

    'Establish the connection to this database
    Set oADOXCat = CreateObject("ADOX.Catalog")
    oADOXCat.ActiveConnection = CurrentProject.Connection

    'Get an instance of Excel to use
    On Error Resume Next
    Set oXLApp = GetObject(, "Excel.Application")
    If Err.Number <> 0 Then Set oXLApp = CreateObject("Excel.Application")
    On Error GoTo 0
    oXLApp.Workbooks.Add

    For Each oADOXTable In oADOXCat.Tables

        If oADOXTable.Type = "ACCESS TABLE" Or oADOXTable.Type = "VIEW" Then    'Table or query

            'Get the data from that particular table
            Set oADORs = CreateObject("ADODB.Recordset")
            oADORs.Open "SELECT * FROM [" & oADOXTable.Name & "]", CurrentProject.Connection, 0, 1

            'Dump the query or table into a new worksheet in the active workbook
            oXLApp.Worksheets.Add

            With oXLApp.ActiveSheet

                'This will put the table/query field names into the Excel worksheet
                For lngFieldCount = 0 To oADORs.Fields.Count - 1
                    .Cells(1, lngFieldCount + 1).Value = oADORs.Fields(lngFieldCount).Name
                Next lngFieldCount

                'This will copy the data into the worksheet
                .Range("A2").CopyFromRecordset oADORs

            End With

        End If

    Next

    oXLApp.UserControl = True
    oXLApp.Visible = True


End Sub

 

[Parra]

Depending on how big the tables are...You can just copy and paste the data.