Helen Feddema's Working with Excel eBook - expert Excel consultants

When it comes to Access you can say we wrote the book on it, literally. This screenshot is from Helen’s latest eBook, Working with Excel. It is a great book. Another great book by Helen. Helen is an expert in Excel, Access, Word, Outlook, and ADO. You can work with Helen or other expert Access consultants right here at expert Excel Consultants .com.

Helen Feddema's Working with Excel eBook - expert Excel consultants

The following code example comes from Helen’s eBook.


Sometimes you just need to just do a dump of raw data to an Excel workbook, so somebody can review it, and maybe do some data manipulation using Excel. The TransferSpreadsheet method is excellent for this purpose; it creates a new unformatted workbook filled with Access data, and this method gives you a lot of choices for the workbook format.

To export data to a workbook using the TransferSpreadsheet method, select the TransferSpreadsheet option in the Export Method option group, then select a data source for export in the Select Data Source for Export combo box. If you don’t need to filter the data by date, you can just click the large Export button to do the export.
To filter the data by date, click the Selected Dates or Selected Months option in the Date Mode for Export option group, then select the dates or months to use for filtering the data for export, and then click the large Export button to do the export.

Figure 2. The date filtering section of the main menu.
The Click event of the large Export button in the Access 2010 database runs this code:
DoCmd.TransferSpreadsheet transfertype:=acExport, _
spreadsheettype:=acSpreadsheetTypeExcel12Xml, _
tablename:=strRecordSource, _
FileName:=strWorkbook, _

The strRecordSource variable is set to the data source selected in the Select Data Source for Export combo box, and the strWorkbook variable is set as follows:
strWorkbook = strXLPath & strDataSource & ” ” _
& strCurrentDateTime & “.xlsx”

where strXLPath is the Exported Documents path set using the folder path selector on the main menu.
For the Access 2003 version of the TransferSpreadsheet code, you can either use the acSpreadsheetTypeExcel9 named constant for the spreadsheettype argument, or omit this argument, since that is the default value.
The workbook created by selecting the Monthly Sales by Region data source with no date filtering is shown below (after resizing the columns):

Figure 3. A workbook created using the TransferSpreadsheet method.
Note that the workbook title is created from the data source name, the current date, and the time with no space between the hour and minute values (to prevent problems with characters forbidden for file names). Note also that there is no formatting at all in the workbook – column headings are the same font and size as the data, and the Currency values are not formatted as Currency (in Office 2010 – curiously, the Currency formatting from the Access query is preserved in the workbook when TransferSpreadsheet is used in Access 2003).

For a data dump with a little formatting, you can use the OutputTo method instead. The code for this method is as follows:
DoCmd.OutputTo objecttype:=acOutputQuery, _
objectname:=strRecordSource, _
outputformat:=”Excel Workbook (*.xlsx)”, _
outputfile:=strWorkbook, _

There is no named constant for the outputformat argument representing an Excel 2007-2010 (.xlsx) workbook; instead, you have to use the full text – ” Excel Workbook (*.xlsx)”. For the 2003 version of this code, you can use the acFormatXLS named constant. The resulting workbook has minimal formatting (the column headings row is bold, shaded and underlined), and it preserves the Currency formatting: