3. Add the following variable declarations:
Dim cnn As ADODB.Connection
Dim cmd As ADODB.Command
Dim param As ADODB.Parameter
Dim xlSheet As Worksheet
Dim rs As ADODB.Recordset
Dim sConnString As String
Dim i As Integer
We??™re using a few ADO objects to retrieve our data: an ADO Connection object to connect
to the data, an ADO Command object to run our stored procedure, an ADO Parameter object to
pass the ManagerID data to the stored procedure, and an ADO Recordset object to hold the
results of our stored procedure.
In this example we are going to use cell A1 to hold the ManagerID information for our
stored procedure??™s parameter. Let??™s add a modified version of the code we??™ve been using to
set up and clear our Excel worksheet:
Set xlSheet = Sheets("Sheet1")
xlSheet.Activate
Range("A3").Activate
Selection.CurrentRegion.Select
Selection.ClearContents
Range("A1").Select
Although this looks very similar to the code used in the DAO examples, the third line,
Range("A3").Activate, has changed. The DAO examples activated cell A1 to clear the entire
current region on the worksheet. Since we??™re using cell A1 as input to our stored procedure in
this example, we want to start clearing the contiguous range beginning at cell A3 instead.
Let??™s open our connection and assign it to a Command object:
Set cnn = New ADODB.Connection
sConnString = "Provider=SQLNCLI;Server=MyServerName\SQLEXPRESS;" ??
& "Database=AdventureWorks;Trusted_Connection=yes;"
cnn.
Pages:
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95