Microsoft Office 2000/Visual Basic Programmer's Guide

Exporting Static Data from an Access Database into Excel

If your solution is based in Access, you can use the TransferSpreadsheet method of the Access DoCmd object to export a table or query to an Excel worksheet, as shown in the following procedure:

Sub ExportDataToExcel(strTableName, _
                      strFileName As String, _
                      blnHasFieldNames As Boolean)
   ' Exports a table or query as static data to Excel.
   
   DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
      strTableName, strFileName, blnHasFieldNames
End Sub

The TransferSpreadsheet method in Access provides a quick way to get data from an Access database into Excel. However, it's limited in its flexibility. Because using the TransferSpreadsheet method creates a static report, you must re-create the report in order to update the data. Also, you must pass a table or saved query to the TransferSpreadsheet method — you can't create a query on the fly by passing in an SQL statement. If you want to create a dynamic report to display Access data in Excel, use Automation (formerly OLE Automation) from Access to create a query table or PivotTable report within an Excel workbook, as discussed in the following section.