Datasheet

Book IX
Chapter 1
Automation with Other
Office Programs
679
Exporting Data to Excel
‘Declare and set the Connection object
Dim cnn As ADODB.Connection
Set cnn = CurrentProject.Connection
‘Declare and set the Recordset object
Dim rs As New ADODB.Recordset
rs.ActiveConnection = cnn
‘Declare and set the SQL Statement to Export
Dim sSQL As String
sSQL = “SELECT FirstName, LastName, Phone FROM Customers”
‘Open the Recordset
rs.Open sSQL
For more information on recordsets and creating SQL statements in VBA
code, see Book VIII, Chapter 5.
The next chunk of VBA code initializes the Excel objects so that you can
manipulate them. There are three objects to declare when working with
Excel: Application, Workbook, and Worksheet. By default, when you
open Excel from the Start menu, it opens to a new workbook, and each work-
book contains at least one worksheet. Here’s the code:
‘Set up Excel Variables
Dim Xl As New Excel.Application
Dim Xlbook As Excel.Workbook
Dim Xlsheet As Excel.Worksheet
Set Xl = CreateObject(“Excel.Application”)
After opening the Excel Application object, we use the Add method of the
Workbooks collection to create a new workbook, stored in the Xlbook variable:
Set Xlbook = Xl.Workbooks.Add
After adding a new Workbook to the Excel Application, we set the
Xlsheet variable to the first Worksheet of the Workbook object using the
Worksheets collection:
Set Xlsheet = Xlbook.Worksheets(1)
Now that the worksheet is initialized and set, it’s time to start playing
around. First, we set the Name of the worksheet to something other than
Sheet1. Then we change cell A1 to a meaningful heading that includes the
date, and format the cell to a larger, bolder, more colorful font:
‘Set Values in Worksheet
Xlsheet.Name = “Phone List”
With Xlsheet.Range(“A1”)
.Value = “Phone List “ & Date
.Font.Size = 14
44_532188-bk09ch01.indd 67944_532188-bk09ch01.indd 679 3/29/10 10:59 PM3/29/10 10:59 PM