Best way to call stored procedure, have results return in cell ran

[SQLScott]

I am totally new to Excel programming, so please be patient with me while I
try and explain what it is that I need.

I would like to call a stored procedure from Excel and have the results
placed in a specific range of cells, for example starting at D7 to K7 for
however many rows are returned.

So my question is this: What is the best way to call the Stored Procedure
and how do i place the results in a specific range of cells?

Should I place a button on the page and call the proc on the click event?

Any advise is greatly appreciated.

 

[dmoney]

where is the procedure? in an excel module? if so, you can use Call xxx
xxxbeing the name of the sub routine. Otherwise, more information is needed
to be able to assist.

 

[JLGWhiz]

Assume your procedure exists and is error free if run. The first line of
code is the title line. for example:

Sub myProcedure()
'Lot's of events
End Sub

The above procedure's name is "myProcedure"

When you record a procedure, there is usually a dialog box that appears and
asks if you want to assign a shortcut key. At that time you can pick any
letter of the alphabet as a shortcut key. To use that key to call the
procedure, you mus press the Ctl key and hold it while pressing your shortcut
key.

To assign a shortcut key to an existing procedure that was not recorded,
click Tools>Macro>Macros and left click the name of your procedure to make it
appear in the small window of the dialog box. Then click Options at the
bottom of the dialog box and when the Macro Options dialog box appears, type
the keyboard letter in the small box and click OK, then Cancel or the big X
on the Macro dialog box.

If you want to use a button, there are two sources for the button. One is
from the forms toolbar. If you use that button, you put the button on the
sheet, then right click it and select "Assign Macro" from the drop down menu
and basically follow the prcedure above for the Macro Options box.

If you use the button from the Control Toolbox, then when you put the b
utton on the sheet, you must be in design mode to right click the button and
from the drop down menu select "View Code" When the code window opens, put
this paste this code into the code window.

Private Sub CommandButton1_Click()
myProcedure
End Sub

This assumes that myProcedure is in the standard module1 code module.

If you want to run the code for editorial purposes, you can open the VB
editor and click the run button or you can click Tools>Macro>Macros then
click on the procedure you want and click Run.

That's pretty much it!

 

[SQLScott]

I guess I should have been more specific.

I want to call a SQL Server stored procedure from within Excel and populate
a specific range of cells with the results returned from the SQL Server
stored procedure.

I hope this clarifies things...