Worksheet.Range("A1"), .Worksheet.Range("A3")
.SetupWorksheet
End With
Note that we??™re using our cExcelSetup object??™s SetKeyCells method, allowing us to assign
values to the InitialCellSelection and DataRegionStart properties with one line of code.
Add another subroutine called GetData. This procedure will take two arguments: the connection
string and the SQL statement. Here is the code for the GetData method:
Sub GetData(ConnString As String, which As String)
With m_cData
.ConnectString = ConnString
.OpenConnection
.SQL = which
CHAPTER 2 n DATA IN, DATA OUT 95
m_cXL.Worksheet.Range("A1").CopyFromRecordset .GetData
.CloseConnection
End With
End Sub
Both of these methods use only our cExcelSetup and cData objects with no external code.
Let??™s finish our GetManagers procedure by adding calls to these methods, and also adding
some cleanup code. The entire GetManagers subroutine should look like this:
Sub GetManagers()
Dim sConnString As String
Dim sSQL As String
Set m_cXL = New cExcelSetup
Set m_cData = New cData
sConnString = "Provider=SQLNCLI;Server=MyServerName\SQLEXPRESS;" ??
& "Database=AdventureWorks;Trusted_Connection=yes;"
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.
Pages:
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120