September 2022 ~ LEARN MS-ACCESS TIPS AND TRICKS

Introduction.

Earlier, we explored how to work with live MS Access data in Excel. We also created a VBA-based procedure to export Table or Query data directly into the current version of an Excel Workbook. The main advantage of saving data in the current Excel format is that it automatically applies Excel’s default theme and formatting features, unlike the legacy Excel 2003 format used when relying on the 'acSpreadsheetTypeExcel3' to acSpreadsheetTypeExcel9 parameters.

This approach also allowed exporting filtered data based on multiple query criteria and saving each dataset to separate Worksheets within the same Workbook. A further extension of this method enabled exporting filtered records into entirely separate Workbooks as well.

Excel’s tools for data analysis, charting, and reporting are powerful and highly flexible. However, Excel was never designed to serve as a Database Management System. When historical data becomes important for decision-making, budget forecasting, business target setting, and other critical needs, users often find themselves turning to a more robust solution like MS Access.

That said, exporting the full set of Access Tables into Excel is not something that most users will require daily.

Exporting All Access Tables Into an Excel Workbook.

However, if necessary, you can use the VBA program provided below.

The VBA Program ExportAllTables2Excel()

Copy and Paste the Code given below into the Standard Module of your Project.

Public Sub ExportAllTables2Excel()
'----------------------------------------------------------------
'Program : ExportAllTables2Excel
'Purpose : Export All Access Tables into Excel WorkBook
'Author  : a.p.r. pillai
'Rights  : All Rights Reserved by www.msaccesstips.com
'Remarks : Creates separate WorkSheets for each Table
'        : in a single WorkBook.
'        : Table Name is Worksheet Name
'----------------------------------------------------------------
Dim db As Database
Dim xlsFileLoc As String
Dim xlsName As String
Dim xlsPath As String
Dim Tbl As TableDef
Dim tblName As String
Dim j As Integer
Dim wrkBook As Excel.Workbook

On Error GoTo Export2Excel_Err

xlsFileLoc = CurrentProject.Path & "\"
xlsName = "AllTables.xlsx"

xlsPath = xlsFileLoc & xlsName
    
If Len(Dir(xlsPath)) > 0 Then
    Kill xlsPath
End If

    Set wrkBook = Excel.Workbooks.Add
    wrkBook.SaveAs xlsPath
    wrkBook.Close
        
Set db = CurrentDb

j = 0
For Each Tbl In db.TableDefs
    tblName = Tbl.Name
    If Left(tblName, 4) = "MSys" Then 'System Tables
        GoTo nextstep
    Else
        j = j + 1
On Error Resume Next
        DoCmd.TransferSpreadsheet acExport, _
        acSpreadsheetTypeExcel12Xml, tblName, xlsPath, True
        If Err > 0 Then
            Err.Clear
          debug.print tblName
            j = j - 1
            Resume nextstep
        End If
End If
nextstep:
Next

On Error GoTo Export2Excel_Err

   MsgBox j & " Table(s) Exported to File:" & vbCr & xlsPath, , "Export2Excel()"
   
Set wrkBook = Nothing
Set db = Nothing

Export2Excel_Exit:
Exit Sub

Export2Excel_Err:
MsgBox Err & " : " & Err.Description, , "Export2Excel()"
Resume Export2Excel_Exit
End Sub

Caution: Before exporting, check the maximum number of rows available in your version of Excel (press End + Down Arrow in a blank worksheet) and compare it with the largest Access table you plan to export. If a table contains more records than Excel can handle, the extra rows may either spill into a second worksheet or be lost entirely. The code provided has not been tested for this scenario, so proceed with caution and use it at your own risk.

Before running the code, make sure to attach the latest version of the Microsoft Excel Object Library to your Access project. Otherwise, the VBA code may not compile. To do this:

Open the VBA editor (Alt + F11).
From the Tools menu, select References.
In the list, locate Microsoft Excel 16.0 Object Library (or the latest version available).
Select the checkbox to enable the reference.
Click OK to close the References window.

The VBA Code Review.

At the beginning of the code, the required variables are declared.

xlsFileLoc The variable is initialized with the database path. In this location, a new Excel workbook (in your current version of Excel) will be created to store the exported Access tables.
The workbook will be named AllTables.xlsx by default. If you prefer a different name, simply change it in the code. Alternatively, rename it after export.
The xlsPath variable is initialized with the full workbook path and filename.

In the next steps, the code creates a new Excel workbook named 'AllTables.xlsx', saves it in the specified location, and then closes it.

If a file with the same name already exists in that location, a warning message will appear. You can then either:

Overwrite the existing file, or
Cancel the export process to retain the current workbook contents.

Important: Ensure the target Excel file is not open while exporting. If the file is open, the export operation will fail.

The following TransferSpreadsheet command handles the export:

DoCmd.TransferSpreadsheet acExport, _
     acSpreadsheetTypeExcel12Xml, TblName, xlsPath, True

The xlsPath parameter specifies the Excel workbook file where the output data will be saved.

If the workbook already exists, the exported data will be written into a new worksheet within that file.
If you omit this parameter, Access will create a new target worksheet file, based on the file format defined by the second parameter (for example, acSpreadsheetTypeExcel12Xml).

The acSpreadsheetTypeExcel12Xml Option saves the data in Excel 2007 (and later) .xlsx format, but several other spreadsheet-type options are also available to match your requirements.

When you create a workbook in your current version of Excel and specify it as the target workbook, all the exported tables are saved in separate worksheets within the same file, rather than generating a new workbook for each table. Additionally, the exported data is automatically formatted using the default Office theme of your Excel version, giving it a more polished appearance.

Note: By default, the table export option creates an Excel 2003-format file, based on one of the legacy export options (acSpreadsheetTypeExcel3 to acSpreadsheetTypeExcel9). As a result, the formatting of the exported data may not look as refined as in newer Excel versions. This subject was discussed in detail in an earlier post titled MS Access and TransferSpreadsheet Command. You may refer to that article for a deeper understanding of the procedure, the different version options available, their output formats, and other key considerations.

In the next step, the Current Database object is assigned to the object variable db. The variable j is used to keep a count of the tables exported into the Excel workbook.

All table names can be retrieved from the TableDefs collection of the database object and passed to the DoCmd.TransferSpreadsheet command to transfer their records. However, this collection also contains system tables (normally hidden from view). Fortunately, all system table names begin with the prefix MSys, which makes them easy to identify and exclude from the export process.

j = 0
For Each Tbl In db.TableDefs
    tblName = Tbl.Name
    If Left(tblName, 4) = "MSys" Then 'System Tables
        GoTo nextstep
    Else
        j = j + 1
On Error Resume Next
        DoCmd.TransferSpreadsheet acExport, _
        acSpreadsheetTypeExcel12Xml, tblName, xlsPath, True
        If Err > 0 Then
            Err.Clear
          Debug.Print tblName
            j = j - 1
            Resume nextstep
        End If
End If
nextstep:
Next

In the For Each ... Next loop, each table definition from the TableDefs collection is read into the TableDef object Tbl.

The Name property of the table is then assigned to the string variable tblName.

Next, the code checks whether the first four characters of tblName equal to "MSys". If so, the table is identified as a system table, and the loop skips the export logic, moving on to the next table.

If the export operation fails due to an unforeseen error, that specific table is skipped, and the process continues with the next one. In such cases, the table counter variable is rolled back to reflect the last successful export, ensuring accuracy in the final count. The name of the table that caused the error is printed in the Debug Window, allowing you to investigate and take corrective action later. This way, a failure in a single table does not halt the entire process, and the remaining tables can still be exported as expected.

Once all tables have been processed, a message is displayed showing the total number of tables successfully exported into the Excel workbook.

You may download the Demo Database attached here and try it right away.

ExportAllTables.zip