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 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.
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 Sub
2. 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.