Microsoft Office Tutorials and References
In Depth Information
Exporting Data to Excel
date when the phone numbers were exported. You can create a button
anywhere in your application to do this, so we’ll just show you the code:
Book IX
Chapter 1
‘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
‘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
Set cnn = Nothing
Set rs = Nothing
Set Xlsheet = Nothing
Set Xlbook = Nothing
Set Xl = Nothing
Search JabSto ::

Custom Search