In the VBE, add a new standard module and name it basManagers. Add two module-level
variables to hold our cExcelSetup and cData objects:
Dim m_cData As cData
Dim m_cXL As cExcelSetup
These are placed at module level in case we need to use the objects across function calls.
Create a new subroutine and name it GetManagers. Add the following code:
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.Employee.ManagerID" ??
& " FROM HumanResources.Employee)));"
Here we are instantiating our cExcelSetup and cData objects, and preparing variables to
set up the cData class.
When we analyzed the original code, we found we needed to have three sets of functionality,
prepare the worksheet for data import, get and display the data, and resize the columns
for the data. We are going to create helper functions to do most of this work.
Add a new subroutine to basManagers and name it DoClearSheet(). Add the following
code:
With m_cXL
Set .Worksheet = Sheets("Sheet1")
.SetKeyCells .
Pages:
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119