Export Microsoft Access Data to Excel
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.
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 Sub2. 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.
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.
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 Sub2. Run Procedure
Run the procedure and check that the file was created in the path listed in excel_file_name 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.