Exporting Multiple Access Objects to Excel at Once

Question

Hi!

I am attempting to import all of the recordset from a access database into a single excel worksheet using VBA. The thing is that there about 40 recordsets, and I'm also trying to avoid hard coding in the table names and everything.....instead I would like to make it general, so that I can repeat this function with several MS Access files.  

Any tips on what the best way is to do this? 

Thanks!

Answer 1

Thank you!

I will give this a go! and when i was referring to recordsets, I meant the tables inside the databases. 

Basically, I'm aiming to extract data from several completed tables in MS Access in a specific format.

Answer 2

Here's a quick & dirty routine I just threw together to export all tables in the database to an Excel workbook:

'------ start of code ------

Sub ExportAllTables()

    On Error GoTo Err_General

    Dim ao                  As AccessObject

    Dim strWorkbookName     As String

    Dim intOutputType       As AcSpreadSheetType

    Dim lngTables           As Long

    Dim lngTablesExported   As Long

    With Application.FileDialog(msoFileDialogSaveAs)

        .Title = "Select or Enter Excel File for Export"

        If .Show = True Then

            strWorkbookName = .SelectedItems(1)

        End If

    End With

    If Len(strWorkbookName) = 0 Then

        Exit Sub

    End If

    Select Case Mid$(strWorkbookName, InStrRev(strWorkbookName, ".") + 1)

        Case "xlsx": intOutputType = acSpreadsheetTypeExcel12Xml

        Case "xls": intOutputType = acSpreadsheetTypeExcel9

        Case Else

            MsgBox "Sorry, I can only export to .xlsx or .xls files.", vbExclamation, "Unsupported File Type"

            Exit Sub

    End Select

    If Len(Dir(strWorkbookName)) > 0 Then

        Kill strWorkbookName

    End If

    For Each ao In CurrentData.AllTables

        If ao.Name Like "MSys*" Or ao.Name Like "~*" Then

            ' Skip system table or deleted table.

        Else

            lngTables = lngTables + 1

            On Error GoTo Err_TableExport

            DoCmd.TransferSpreadsheet acExport, intOutputType, ao.Name, strWorkbookName, True

            On Error GoTo Err_General

            lngTablesExported = lngTablesExported + 1

            DoEvents

        End If

NEXT_TABLE:

    Next ao

Exit_Point:

    MsgBox "Exported " & lngTablesExported & " out of " & lngTables & " tables.", _

        vbInformation, _

        "Export Complete"

    Exit Sub

Err_TableExport:

    If MsgBox( _

        "Unable to export table '" & ao.Name & "' - the following error occurred:" & vbCr & _

                vbCr & "     " & Err.Number & " : " & Err.Description & vbCr & _

                vbCr & "Skipping this table. Click Cancel to stop the export completely.", _

            vbExclamation + vbOKCancel + vbDefaultButton1, _

            "Error " & Err.Number) _

        = vbCancel _

    Then

        Resume Exit_Point

    Else

        Err.Clear

        Resume NEXT_TABLE

    End If

Err_General:

    MsgBox Err.Description, vbExclamation, "Error " & Err.Number

    Resume Exit_Point

End Sub

'------ end of code ------

Answer 3

What exactly do you mean by "recordset"?  I know what I mean by it, but I think that's probably not what you mean.

If you want to export all the non-system tables in the database, that's fairly simple.  If you want to export all non-system tables and all non-action queries, that's not much harder, but if any of those queries has any parameters -- or refers to a control on a form -- you'll have to decide whether you want to prompt for the parameters or skip the query.