Open sConnString
Set cmd = New ADODB.Command
cmd.ActiveConnection = cnn
Set param = New ADODB.Parameter
With param
.Name = "ManagerID"
.Type = adInteger
.Value = ActiveSheet.Range("A1").Value
End With
With cmd
.CommandType = adCmdStoredProc
.CommandText = "uspGetManagerEmployees"
.Parameters.Append param
End With
Set rs = New ADODB.Recordset
Set rs = cmd.Execute
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("A1").Select
rs.Close
cnn.Close
CHAPTER 2 n DATA IN, DATA OUT 72
Set cmd = Nothing
Set param = Nothing
Set rs = Nothing
Set cnn = Nothing
Set xlSheet = Nothing
End Sub
Note that our cleanup code also refers to cell A3 when setting up the worksheet with the
AutoFit method.
We can test this code out by entering a ManagerID in cell A1 on Sheet1 and running the
GetManagerEmployeeListSQL method from the macro list.
1. Enter 16 in cell A1.
2. Choose GetManagerEmployeeListSQL from the macro list and run the code. The
results are shown in Figure 2-23.
Figure 2-23. Result of GetManagerEmployeeListSQL code
3. Enter a manager ID of 21 in cell A1 and run the code again. You??™ll see a longer list of
employees since this is a higher-level manager.
4. Enter a manager ID of 16 again to see the setup code at work, clearing the used cells
for the next round of data import.
Pages:
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98