Access.2007.VBA.Bibl..

The Excel button on the Ribbon (with or without the layout and formatting preserved) is a useful

option when you need to create a quick-and-dirty Excel worksheet in Excel 2007 format (the new

extension is .xlsx). If you need to export to an older Excel format, but you don’t need fancy format-

ting, you can do an export with a single line of VBA code, using the TransferSpreadsheet

method, which has been available since the earliest days of Access.

The TransferSpreadsheetmethod allows you to select the Excel version for creating your

worksheet filled with Access data, so you can create worksheets that will be usable by recipients

who have older versions of Office. The procedure listed as follows exports tblContacts to an Excel

97-2003 worksheet called Contacts.xls (the named constant for this worksheet version is

acSpreadsheetTypeExcel7):

Public Function TransferToExcel()

On Error GoTo ErrorHandler

Dim strTable As String

Dim strWorksheetPath As String

strWorksheetPath = GetWorksheetsPath

strWorksheetPath = strWorksheetPath & “Contacts.xls”

strTable = “tblContacts”

strWorksheetPath = GetWorksheetsPath()

strWorksheetPath = strWorksheetPath & “Contacts.xls”

Debug.Print “Worksheet path: “ & strWorksheetPath

Export table data to a new worksheet:

DoCmd.TransferSpreadsheet transfertype:=acExport, _

spreadsheettype:=acSpreadsheetTypeExcel7, _

TableName:=strTable, FileName:=strWorksheetPath, _

hasfieldnames:=True

ErrorHandlerExit:

Exit Sub

ErrorHandler:

MsgBox “Error No: “ & Err.Number _

& “; Description: “ & Err.Description

Resume ErrorHandlerExit

End Sub

The exported worksheet looks just like the unformatted worksheet exported from the Ribbon

selection, but it is in the older format, as you can see from the “(Compatibility Mode)” after the

worksheet name in its title bar (see Figure 7.4).

Part II Writing VBA Code to Exchange Data between Office Components