VBA

Export Microsoft Access Data to Excel

vba 1 For this example, we will be using a simple table with a list of beer names.  Our goal is to export the contents of this table into an Excel workbook.

Access provides two ways to do this.  If you wish to export the entire table, without any frills, Access provides a simple method to export (and import or link) Excel data.

If you need to use some custom logic while exporting though, you can also use VBA’s Excel object model.  You will learn both ways in this tutorial.

Test Data

As mentioned, we will use the very simple data below for this tutorial.

2016 12 15 access create table

The Simple Way

1. Create VBA Procedure

Microsoft Access provides the very handy method DoCmd.TransferSpreadsheet to export, import, or link Excel data.  The procedure below shows how to use this method to export our simple table.  Create a new VBA Module and paste the code.

Option Compare Database
Option Explicit

Sub export_table_to_excel()
    
    Dim table_to_export As String
    Dim excel_file_name As String
    Dim has_header As Boolean
    
    'Name of the Access table to export:
    table_to_export = "Table1"
    
    'Path of the file to put data in.  Update with your own path:
    excel_file_name = "C:\users\renan\desktop\file_to_put_data"
    
    'Export headers with data:
    has_header = True
    
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, table_to_export, excel_file_name

End Sub
2. Run Procedure

Run the procedure and check that the file was created in the path listed in the excel_file_name variable in your procedure.  You will notice that the field names were also exported.  This is one of the parameters for TransferSpreadsheet, which can be excluded if needed. 2016 12 15 excel export data

For More Complex Cases

1. Import Excel References

In order to use the Excel object model in VBA, we must first import the Excel object library reference.  This can be done by going to Tools->References in the VBA editor menu.

Scroll down to the “Microsoft Excel Object Library” entry (Excel version may vary), check it, and click OK. 2016 12 15 access import excel reference

2. Create VBA Procedure

Although the name of the tutorial step is the same, more complex cases require more than a single method.  The code below uses VBA’s Excel object model to create a workbook and write to cells within a sheet.

Option Compare Database Option Explicit

Sub export_table_to_excel()

    'Access objects:
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim table_name As String
    table_name = "Table1"
    
    'Excel objects:
    Dim excel_application As Excel.Application
    Dim workbook As Excel.workbook
    Dim sheet As Excel.Worksheet
    Dim excel_file_name As String
    Dim sheet_name As String
    Dim beer_name_column As Integer
    
    'Path of the file to put data in.  Update with your own path:
    excel_file_name = "C:\users\renan\desktop\file_to_put_data"
    beer_name_column = 2
    
    'Open Access recordset to iterate through and write to Excel:
    Set db = CurrentDb
    
    'This can also be used to open a query instead of a table:
    Set rs = db.OpenRecordset(table_name)
    
    'Instantiate Excel objects:
    Set excel_application = New Excel.Application
    Set workbook = excel_application.Workbooks.Add
    Set sheet = workbook.Sheets.Add
    
    'Loop through recordset and write to cell:
    Dim rowIndex As Integer
    
    'Write header:
    sheet.Cells(1, beer_name_column).Value = "My Beers"
    sheet.Cells(1, beer_name_column).Font.Bold = True
    
    For rowIndex = 1 To rs.RecordCount
        'Below we are appending "Beer Name is" to the text being
        'written.  This could not be done using the simpler transferSpreadsheet method.
        sheet.Cells(rowIndex + 1, beer_name_column).Value = "Beer Name " & rowIndex & " is " & rs.Fields("Beer Name")
        'Go to next record:
        rs.MoveNext
    Next
    rs.Close
    
    workbook.SaveAs excel_file_name
    workbook.Close
    excel_application.Quit
    
    'Clean up:
    Set sheet = Nothing
    Set workbook = Nothing
    Set excel_application = Nothing
    Set rs = Nothing
    Set db = Nothing
End Sub
3. Run Procedure

Run the procedure and check that the file was created in the path listed in excelfilename variable for your procedure.

As you can see, we were able to do some customization by using the Excel object model. For example, we were able to append to the text for each record, and format the header font. Basically, anything you can do in Excel can be done in VBA using the object model.

2016 12 15 access vba complex excel result