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
CHAPTER 2 n DATA IN, DATA OUT 78
ActiveSheet.Range("A4").CopyFromRecordset rs
xlSheet.Select
Range("A3").Select
Selection.CurrentRegion.Select
Selection.Columns.AutoFit
Range("A1").Select
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.
Pages:
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103