How do I export Access data to Excel - Part 2

There are many ways to export data from an Access database to an Excel spreadsheet.  These include:

1. Manual Methods
   • Cut and Paste
   • Manual Export
   • Saved Exports
2. TransferSpreadsheet Methods (this article) 
   • Macros
   • VBA
3. Office Automation Methods
   • Do…While
   • CopyFromRecordSet

In Part 1, I discussed various manual methods.  This time I’ll look at the TransferSpreadsheet method, which allows you to have more control over automating exports.

Import Export Spreadsheet – Macro Action

Macros in Microsoft Access offer a quick easy way to automate some processes.  Macros are created and maintained in a graphical user interface, You don’t have to do any coding or know a programming language.

To create a new macro, go to the Create tab on the ribbon and choose Macro.

The Macro Editor will look like this

To add a new macro action, click the drop down, Add New Action.  However, you’ll note that the ImportExportSpreadsheet action is not in the list.

 

This is because when Action Catalog is selected on the ribbon, you only get actions the Microsoft considers “safe”. In this case, that means that using any of these actions don’t require the database to be in a Trusted Location. Unfortunately, ImportExportSpreadsheet is not one of those actions, so you’ll need to select Show All Actions, instead.

 

Now you can select ImportExportSpreadsheet.  You’ll get a number of options to fill out:

1. Transfer Type: Obviously, we want Export, but the same action can be used to Import or Link data.
2. Spreadsheet Type: Excel Workbook exports to your installed version of Excel. If you need to export it in a different spreadsheet format, you can change it here.
3. Table Name: The name of the Table or Query you want to export.
4. File Name: The path and file name of the spreadsheet. The filename will have a direct effect on how the export behaves.
   • If the file does not exist, a new spreadsheet named for the table will be created and the data will be placed on a tab also named for the table (i.e. TheDataQuery)
   • If the file does exist, AND named tab also exists, the data in the tab will be over-written.
   • If the file does exist BUT the named tab does not exist, a new tab named for the table will be created. Existing tabs will be left alone.
5. Has Field Names: If Yes, it will put the field names in row 1 of the spreadsheet.

Run the macro using the Run button on the Design tab.

It will create a workbook named Data_Spreadsheet.xlsx with a datatab called TheDataQuery.

Why Bother?

There’s no real value to creating a macro for a one-time export.  However, a macro will allow you to export multiple times or multiple “tables” or both. So to export a second “table”, I simply add another macro action.

The file name also determine how multiple exports are handled.

• If the filenames are the same for multiple exports, the will be exported to the same workbook.
• If the filenames are different, they will be exported to different workbooks.

Transfer Spreadsheet – VBA Method

The Macro method is useful if you always export the same tables or queries to the same locations.  It’s also very easy to set up.  However, since all the table names and path/file names are hard coded, to change anything, you have to modify the application.  This is less than ideal if you want to the application to be used by non-developers.

A better way is to store the names of the queries/tables in a tables and use a bit of VBA to repeat the export process for each value in the table.

Converting Macros to VBA

Fortunately, you don’t have to start from scratch.  You can convert an existing macro to a VBA procedure, which will give you the basic layout.  To do this,open the macro in Design View and click: Convert Macros to Visual Basic

The procedure will appear in a Module named for the macro and will look something like this:

'------------------------------------------------------------
' Macro2
'------------------------------------------------------------
Function Macro2()
On Error GoTo Macro2_Err

    DoCmd.TransferSpreadsheet acExport, 10, "TheDataQuery", _
        "C:\Users\roger_000\Documents\Access\Data_Spreadsheet.xlsx", _
        True, ""
    DoCmd.TransferSpreadsheet acExport, 10, "TheDataQuery2", _
        "C:\Users\roger_000\Documents\Access\Data_Spreadsheet.xlsx", _
        True, ""

Macro2_Exit:
    Exit Function

Macro2_Err:
    MsgBox Error$
    Resume Macro2_Exit

End Function
'------------------------------------------------------------

If I had named my macro something relevant, like “Export_Data”, the procedure would be named for that.

Creating an Export table.

Next, I will create a table called MyExport which will hold the text values of the table and filenames I want exported.

Then modify the code as follows.  I’ve added comments in the code to explain the modifications

'------------------------------------------------------------
' Export_Data
'------------------------------------------------------------
Sub Export_Data()
On Error GoTo Export_Data_Err

'add object and scalar variables
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim TheTable As String
Dim TheFile As String

'open the Export Table (MyExport) as a recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("MyExport")

'loop through recordset
Do While Not rs.EOF

    'set scalar variables
    TheTable = rs!Export_Table
    TheFile = rs!Export_Filename
   
    'export the table using the variables
    DoCmd.TransferSpreadsheet acExport, 10, TheTable, _
        TheFile, True, ""
       
    'move to the next record
    rs.MoveNext
Loop

Export_Data_Exit:
    Exit Sub

Export_Data_Err:
    MsgBox Error$
    Resume Export_Data_Exit

End Sub
'------------------------------------------------------------
Note: I prefer Sub procedures rather than functions in this case, so I modified it shown in the highlighting.

Now Just Run It

Next I just run the code (usually by way of a button on a form), and the indicated tables/queries will be exported.  In this case to the same workbook.  I can export them to different workbooks by simply changing the filenames in the MyExport table.  This can be useful if you want each query to go to a different user’s folder.

Download A Sample

You can find the companion sample here: ExportToExcel_TransferSpreadsheet

Taking It Further

You can take the automated process even further by exporting the data for a formatted sheet or chart using an Excel template. You can download samples with complete explanation here:

• ExportToExcel.mdb ( intermediate )
  This sample demonstrates how to export data from a database using the TransferSpreadsheet method, but have the data populate a formatted spreadsheet. The trick here is to export the data to a NEW tab in the Excel workbook and link the fields from the new tab into the formatted spreadsheet.
• ExportToExcelCharts.mdb ( intermediate )
  Sample with Documentation. This sample demonstrates how to export data from a database using the TransferSpreadsheet method, but have the data populate a Chart. The trick here is to export the data to a NEW tab in the Excel workbook and link the fields from the new tab into the chart.

Next up, in Part 3, I’ll show how to automate exports even further using Office Automation.