store procedure in excel - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Other SQL Server 2008 Topics

store procedure in excel

Author

Topic

peace
Constraint Violating Yak Guru

420 Posts

Posted - 2014-11-03 : 08:28:15

i have a store procedure where need to pass in the parameter.

example exec store_procedure 1

i manage to create the connection in excel. how can i pass in to prompt the parameter?

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-11-03 : 08:48:10

Here is an example:


    Dim cmd As New ADODB.Command
    Dim conn As New ADODB.Connection
    
    conn.Open "Driver={SQL Server};Server=YourServerName;Database=YourDBName;Uid=UserNameHere;Pwd=PasswordHere"
    cmd.ActiveConnection = conn
    cmd.CommandText = "store_procedure_name_here"
    cmd.CommandType = adCmdStoredProc
    
    cmd.Parameters.Append cmd.CreateParameter("@YourStoredProcParameter", adInteger, adParamInput, 8)
    cmd.Parameters("@YourStoredProcParameter") = 1
        
    cmd.Execute
    
    Set cmd = Nothing
    conn.Close
    Set conn = Nothing

peace
Constraint Violating Yak Guru

420 Posts

Posted - 2014-11-03 : 08:54:41

do you have step by step to apply this?

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-11-03 : 09:35:34

Not sure what you mean by step by step.

Starting with a fresh excel sheet, click insert -> shapes and insert a shape, say a rectangle. Then right click on the inserted rectangle, select assign macro, new macro and OK. That will open up the VBA for Applications window with the stub for a Subroutine. Paste the code that I posted into that window.

In Tools -> References in the VBA for applications window, find Microsoft ActiveX Data Objects X.X library (pick the latest you have) and put a check mark against it, click ok.

Now go back to the excel sheet and if you click the button, the code should execute and the stored procedure should get executed.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources