ADO Example 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 Modulel, 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.EmployeelD, Person.Contact.FirstName,"

& " Person.Contact.LastName FROM Person.Contact"

& " INNER JOIN HumanResources.Employee"

& " ON Person.Contact.ContactID = HumanResources.Employee.ContactID"

& " WHERE (((HumanResources.Employee.EmployeelD) In"

& " (SELECT HumanResources.Employee.ManagerlD"

& " FROM HumanResources.Employee)));"

Let's dissect this SQL statement a bit. Our manager list will show the employee ID as well as the first and last name for each manager. As you can see, the data is stored in two tables. The HumanResources.Employee table stores the EmployeeID field and the Person.Contact table stores the name fields.

The two tables have a common field, ContactID, that is used to join the tables in this query. Notice the WHERE clause, which contains a SELECT statement within it. This is known as nested SQL or an SQL subquery. Essentially, it says, "Only show us those employees whose employee ID can be found in the result of the subquery that contains only manager IDs." Subqueries such as this are a nice way to avoid creating temporary tables or individual queries to narrow down our search.

Here's the complete GetManagerList code:

Sub GetManagerList() Dim cnn As ADODB.Connection Dim rs As ADODB.Recordset Dim xlSheet As Worksheet Dim sConnString As String Dim sSQL As String

Set xlSheet = Sheets("Sheet2")

xlSheet.Activate

Range("Al").Activate

Selection.CurrentRegion.Select

Selection.ClearContents

Range( "Al").Select

Set cnn = New ADODB.Connection sConnString = "Provider=SQLNCLI;Server=MyServerName\SQLEXPRESS;" * & "Database=AdventureWorks;Trusted_Connection=yes;"

cnn.Open sConnString 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("Al").CopyFromRecordset rs xlSheet.Select Range( "Al").Select Selection.CurrentRegion.Select Selection.Columns.AutoFit Range( "Al").Select rs.Close cnn.Close

Set rs = Nothing Set cnn = Nothing Set xlSheet = Nothing End Sub

Run the code, and your result on Sheet2 should look like Figure 2-24.

Figure 2-24. The manager list displayed

Now that we have our list of managers, let's write the code to show the selected manager's staff.

Add a new subroutine to Module1 and name it GetSelectedManagerEmployeeListSQL. Since this code is very similar to GetManagerEmployeeListSQL, take a look at Listing 2-6, which shows the entire code set, and we'll review the differences.

Listing 2-6. GetSelectedManagerEmployeeListSQL Subroutine

Sub GetSelectedManagerEmployeeListSQL()

Dim cnn As ADODB.Connection

Dim cmd As ADODB.Command

Dim param As ADODB.Parameter

Dim rs As ADODB.Recordset

Dim xlSheet As Worksheet

Dim sConnString As String

Dim iMgrID As Integer

Dim sMgrName As String

Dim i As Integer

Set xlSheet = Sheets("Sheet3")

xlSheet.Activate

Range("A3").Activate

Selection.CurrentRegion.Select

Selection.ClearContents

Range( "A1").Select

Sheets("Sheet2").Activate 'make sure we're on the right sheet Set cnn = New ADODB.Connection sConnString = "Provider=SQLNCLI;Server=MyServerName\SQLEXPRESS;" * & "Database=AdventureWorks;Trusted_Connection=yes;"

cnn.Open sConnString

Set cmd = New ADODB.Command cmd.ActiveConnection = cnn iMgrID = GetMgrID sMgrName = GetMgrName

Set param = New ADODB.Parameter With param

.Name = "ManagerID" .Type = adInteger .Value = iMgrID End With

With cmd

.CommandType = adCmdStoredProc .CommandText = "uspGetManagerEmployees" .Parameters.Append param End With

Set rs = New ADODB.Recordset Set rs = cmd.Execute xlSheet.Activate 'activate the display sheet Range("A1").Value = "Employee List for: " & sMgrName Range("A1").Font.Bold = True

For i = 1 To rs.Fields.Count

ActiveSheet.Cells(3, i).Value = rs.Fields(i - 1).Name Next i xlSheet.Range(xlSheet.Cells(3, 1), _

xlSheet.Cells(3, rs.Fields.Count)).Font.Bold = True

ActiveSheet.Range("A4").CopyFromRecordset rs xlSheet.Select

Range( "A3").Select

Selection.CurrentRegion.Select

Selection.Columns.AutoFit

Range(

rs.Close cnn.Close

Set

cmd = Nothing

Set

param = Nothing

Set

rs = Nothing

Set

cnn = Nothing

Set

xlSheet = Nothing

End Sub

When a manager is selected and this code is run, it will generate the employee list on Sheet3. The manager's name will appear at the top of the page in cell A1, and the employee list will populate below it. We've added a couple of variables to our declarations:

Dim iMgrID As Integer Dim sMgrName As String

These will hold the ID for our search and the name for our display. We're setting our xlSheet variable to refer to Sheet3:

Set xlSheet = Sheets("Sheet3")

And we're pointing back to Sheet2 to get our selected manager information: Sheets("Sheet2").Activate

We've added calls to two helper functions, GetMgrID and GetMgrName. These functions refer to the active sheet, so this line of code is important. We could optionally have made explicit references to Sheet2 in our functions or passed in the worksheet as an argument to the functions.

Add these functions to Module1.

Function GetMgrID() As Integer Dim iReturn As Integer Dim rngMgrID As Range

Set rngMgrID = Cells(ActiveCell.Row, 1) iReturn = rngMgrID.Value Set rngMgrID = Nothing

GetMgrID = iReturn End Function

Function GetMgrName() As String Dim sReturn As String Dim iRow As Integer iRow = ActiveCell.Row sReturn = Cells(iRow, 2).Value & " " & Cells(iRow, 3).Value

GetMgrName = sReturn End Function

These functions illustrate two methods for referring to cells on Sheet2. GetMgrID uses a variable of type Range to refer to the cell in the current row and column 1. GetMgrName uses direct references to the cells by using the Cells object.

Let's test the code. On Sheet2, put your cursor in any column on a row containing manager information, as in Figure 2-25.

14 Taylor Maxwell 16 Jo Brown

15 John I Campbell" 21 ^e-ter Krebs

Figure 2-25. Selecting a manager

In the Macro window, run the GetSelectedManagerEmployeeListSQL subroutine, the results of which are shown in Figure 2-26.

Figure 2-26. Results of manager's employee search
0 0

Responses

Post a comment