Datasheet

678
Exporting Data to Excel
‘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
‘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”)
Set Xlbook = Xl.Workbooks.Add
Set Xlsheet = Xlbook.Worksheets(1)
‘Set Values in Worksheet
Xlsheet.Name = “Phone List”
With Xlsheet.Range(“A1”)
.Value = “Phone List “ & Date
.Font.Size = 14
.Font.Bold = True
.Font.Color = vbBlue
End With
‘Copy Recordset to Worksheet Cell A3
Xlsheet.Range(“A3”).CopyFromRecordset rs
‘Make Excel window visible
Xl.Visible = True
‘Clean Up Variables
rs.Close
Set cnn = Nothing
Set rs = Nothing
Set Xlsheet = Nothing
Set Xlbook = Nothing
Set Xl = Nothing
As you can see, the code is starting to grow. It’s not out of control, but it’s
common to have procedures that grow to pages in length. But don’t be
afraid; as long as you break it down into small chunks, it’s not so hard to
understand.
The first chunk of code sets up the Recordset object with a simple SQL
Select statement that gets the first name, last name, and phone number from
the Customers table, as follows:
44_532188-bk09ch01.indd 67844_532188-bk09ch01.indd 678 3/29/10 10:59 PM3/29/10 10:59 PM