Microsoft Office Tutorials and References
In Depth Information
Exporting Data to Excel
After opening the Excel Application object, use the Add method of the
Workbooks collection to create a new workbook stored in the Xlbook
variable, as follows:
Set Xlbook = Xl.Workbooks.Add
After adding a new Workbook to the Excel Application, set the Xlsheet
variable to the first Worksheet of the Workbook object, using the
Set Xlsheet = Xlbook.Worksheets(1)
Now that the worksheet is initialized and set, it’s time to start playing around.
First, set the Name of the worksheet to something other than Sheet1. Then
change cell A1 to a meaningful heading that includes the date, and format the
cell to use a larger, bolder, more colorful font, as follows:
‘Set Values in Worksheet
Xlsheet.Name = “Phone List”
.Value = “Phone List “ & Date
.Font.Size = 14
.Font.Bold = True
.Font.Color = vbBlue
Now it’s time to take the data from the Recordset object and put it into the
spreadsheet. There’s no looping through the recordset here; just use the
CopyFromRecordset method to copy the contents of a recordset into a
particular range of cell. For this example, copy the data from the recordset
starting with cell A3, as follows:
‘Copy Recordset to Worksheet Cell A3
Finally, make the Excel application visible so that you can see your data in
Excel (as shown in Figure 1-8), and clean up the variables.
You can add much more code to this routine to fully customize the look of
the spreadsheet. You can change the column widths, change the cells’
background colors, and sort the data.
If you can perform a task with the mouse and keyboard in the Excel window,
you can find a way to do it with VBA.