CHAPTER 2 n DATA IN, DATA OUT 87
The first thing we??™re doing in our original code is setting up the worksheet by activating it
and then clearing a contiguous region in preparation for importing our data:
Set xlSheet = Sheets("Sheet2")
xlSheet.Activate
Range("A1").Activate
Selection.CurrentRegion.Select
Selection.ClearContents
Range("A1").Select
Then we??™re instantiating and opening a connection to our data:
Set cnn = New ADODB.Connection
sConnString = "Provider=SQLNCLI;Server=MYSERVERNAME\SQLEXPRESS;" ??
& "Database=AdventureWorks;Trusted_Connection=yes;"
cnn.Open sConnString
Next, we get our data into an ADO recordset and place it on our worksheet:
sSQL = "SELECT HumanResources.Employee.EmployeeID, Person.Contact.FirstName," ??
& " Person.Contact.LastName FROM Person.Contact" ??
& " INNER JOIN HumanResources.Employee" ??
& " ON Person.Contact.ContactID = HumanResources.Employee.ContactID" ??
& " WHERE (((HumanResources.Employee.EmployeeID) In" ??
& " (SELECT HumanResources.Employee.ManagerID" ??
& " FROM HumanResources.Employee)));"
Set rs = New ADODB.Recordset
rs.Open sSQL, cnn, adOpenDynamic
Sheets("Sheet2").Activate
Range("A1").CopyFromRecordset rs
And finally, we do a quick bit of formatting the sheet by using the AutoFit command to
resize the data columns:
xlSheet.Select
Range("A1").Select
Selection.CurrentRegion.Select
Selection.Columns.AutoFit
Range("A1").Select
These are four simple units of functionality that we can provide in a very generic and
reusable object-oriented solution.
Pages:
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112