Select puts the cursor within the region we want to work
with (in case there??™s more than one area with data on your worksheet). The next line,
Selection.CurrentRegion.Select, selects any contiguous area of cells based on the current
cursor location. Next comes our AutoFit command, followed by the selection of a single cell
(to remove the selection from the entire range).
The entire function should now look like Listing 2-3.
Listing 2-3. GetDAOAccessJet Method
Sub GetDAOAccessJet()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim xlSheet As Worksheet
Dim i As Integer
Dim arr_sPath(1) As String
'store path to Access 2007 and 2000 versions of Northwind db
arr_sPath(0) = "C:\projects\Excel2007Book\Files\northwind 2007.accdb"
arr_sPath(1) = "C:\projects\Excel2007Book\Files\northwind.mdb"
Set xlSheet = Sheets("Sheet1")
xlSheet.Activate
Range("A1").Activate
Selection.CurrentRegion.Select
Selection.ClearContents
Range("A1").Select
CHAPTER 2 n DATA IN, DATA OUT 57
Set db = Workspaces(0).OpenDatabase(arr_sPath(0), ReadOnly:=True)
Set rs = db.OpenRecordset("Employees")
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
xlSheet.Range("A2").CopyFromRecordset rs
xlSheet.Select
Range("A1").Select
Selection.CurrentRegion.Select
Selection.Columns.AutoFit
Range("A1").Select
rs.Close
db.Close
Set xlSheet = Nothing
Set rs = Nothing
Set db = Nothing
End Sub
Let??™s run our code and see the result on Sheet1.
Pages:
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84