Access.2007.VBA.Bibl..

If Excel is not running, the workbook will open in a new Excel window.

As with exporting Access data to Word, when writing Automation code to work with Excel, you
only need a few components of the Excel object model: Workbooks, Worksheets, the Range object,
Rows, and Columns. These objects are used in the more complex Excel Automation procedures in
the following sections.

Minimally Formatted Worksheets ....................................................................................

If you need to create a simple tabular worksheet listing the contacts in qryContacts, with minimal
formatting, you can create a new workbook in VBA code, from a saved workbook template with a
title, correctly sized columns, and the font and other layout of your choice, and fill it with Access
data. The ExportContactsToExcelprocedure creates a recordset based on qryContacts, and
exports selected fields from each record in that query to a workbook created from a template, with
a title, column headings, and some minimal formatting:

Public Function ExportContactsToExcel()

On Error GoTo ErrorHandler

Dim dbs As DAO.Database

Dim rst As DAO.Recordset

Dim strWorksheetPath As String

Dim appExcel As Excel.Application

Dim strTemplatePath As String

Dim bks As Excel.Workbooks

Dim rng As Excel.Range

Dim rngStart As Excel.Range

Dim strTemplateFile As String

Dim wkb As Excel.Workbook

Dim wks As Excel.Worksheet

Dim lngCount As Long

Dim strPrompt As String

Dim strTitle As String

Dim strTemplateFileAndPath As String

Dim prps As Object

Dim strSaveName As String

Dim strTestFile As String

Dim strDefault As String

Set appExcel = GetObject(, “Excel.Application”)

strTemplatePath = GetWorksheetTemplatesPath

strTemplateFile = “Access Contacts.xltx”

strTemplateFileAndPath = strTemplatePath _

& strTemplateFile

Working with Excel Worksheets 7