Microsoft Office Tutorials and References
In Depth Information
Exporting Data to Excel
As you can see, the code is starting to grow. It’s not out of control, but
procedures commonly grow to pages in length. Don’t be afraid; as long as you
break code into small chunks, it’s not so hard to understand. The following
section gives you the breakdown for this code.
The code in this section requires the ActiveX Data Objects Library to be
selected in the References dialog box. For more information on ActiveX, see
Book VIII, Chapter 5.
Examining the export code
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:
‘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 using 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. You have three objects to declare when you’re working
with Excel: Application, Workbook, and Worksheet. By default, when you
open Excel from the Start menu, the program opens to a new workbook, and
each workbook 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”)
Search JabSto ::




Custom Search