For example, if the recordset has two fields and ten rows,
the array appears as two rows and ten columns. Therefore, you need to transpose the array using your
TransposeDim() function before assigning the array to the range of cells.
CHAPTER 2 n DATA IN, DATA OUT 74
ADO Example 2: Importing SQL Data Based on a Selection
In this exercise, we??™ll see how we can use Excel to generate a list, and how by making a selection
from that list we can view detailed information about the selected item.
Adventure Works management wants to see a quick view of their reporting tree by manager.
We??™re going to create a list of managers and then add code that will show the selected
manager??™s reporting structure.
On Module1, add a new subroutine and name it GetManagerList. Add the following variable
declarations:
Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim xlSheet As Worksheet
Dim sConnString As String
Dim sSQL As String
Our setup code is very similar to our last example, except that we are going to put our list
of managers on Sheet2. Our opening line of setup code will now look like this:
Set xlSheet = Sheets("Sheet2")
The remainder of the code is the same, with the obvious exception of the SQL statement.
The SQL statement to generate our manager list looks like this:
sSQL = "SELECT HumanResources.Employee.EmployeeID, Person.Contact.FirstName," ??
& " Person.Contact.LastName FROM Person.Contact" ??
& " INNER JOIN HumanResources.Employee" ??
& " ON Person.
Pages:
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100