CurrentRegion.Select
Selection.ClearContents
Range("A1").Select
We??™re assigning the Sheet1 object from our workbook to the variable xlSheet to provide
easier access to that sheet. This eliminates the need to type Sheets("Sheet1") whenever we
need to reference the worksheet we??™re manipulating.
Next we??™ll instantiate our database and recordset objects:
Set db = Workspaces(0).OpenDatabase(arr_sPath(0), ReadOnly:=True)
Set rs = db.OpenRecordset("Employees")
This code creates the default Jet workspace and fills a recordset with the information in
the Employees table in the Northwind 2007 database.
Now we??™ll fill the first row in the worksheet with the field names from the recordset and
add bold formatting to the column headings:
CHAPTER 2 n DATA IN, DATA OUT 56
For i = 0 To rs.Fields.Count - 1
xlSheet.Cells(1, i + 1).Value = rs.Fields(i).Name
Next i
xlSheet.Range(xlSheet.Cells(1, 1), xlSheet.Cells(1, rs.Fields.Count)) ??
.Font.Bold = True
Rather than create a loop to walk through the recordset and populate the sheet row by
row and column by column, we??™ll use Excel??™s CopyFromRecordset method to fill the sheet with
data:
xlSheet.Range("A2").CopyFromRecordset rs
The last thing we??™ll do before inserting our cleanup code is adjust the column widths to
show the full text values (using the AutoFit method):
xlSheet.Select
Range("A1").Select
Selection.CurrentRegion.Select
Selection.Columns.AutoFit
Range("A1").Select
The first call to Range("A1").
Pages:
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83