Link to home
Create AccountLog in
Avatar of Amanda Walshaw
Amanda WalshawFlag for Australia

asked on

running stored procedure from excel office 2010

How would you run a stored procedure from excel 2010.
Avatar of nutsch
nutsch
Flag of United States of America image

Like this?

Sub Stufftodo

Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim strConn As String, objField As ADODB.Field
Dim strRst As String
Dim cmd As ADODB.Command
Set cmd = New ADODB.Command
 
strConn = "PROVIDER=SQLOLEDB;" & _
            "DATA SOURCE=DCC08SQL;INITIAL CATALOG=CAPEX;" & _
            " INTEGRATED SECURITY=sspi;"

Set conn = New ADODB.Connection
conn.ConnectionString = strConn
conn.Open

with activesheet
        
        cmd.ActiveConnection = conn
        cmd.CommandText = "spRetrievePivotByProject"
        cmd.CommandType = adCmdStoredProc
        
        cmd.Parameters.Refresh
        cmd.Parameters("@year").Value = lYearLoop
        cmd.Parameters("@projnum").Value = .Range("summary_ProjNum")
         
        Set rs = cmd.Execute()
        
        If Not rs.EOF Then
        
            .Cells(2, 2).CopyFromRecordset rs
        
            For Each objField In rs.Fields
                .Cells(1, lLoop) objField.Name
            Next
            
        End If 'Not rs.EOF
 end with

   
conn.Close
Set rs = Nothing
Set conn = Nothing

End Sub

Open in new window

Avatar of Amanda Walshaw

ASKER

IS THAT DONE AS A VB SCRIPT OR THROUGH Data connection wizard, I am using Excel 2010
ASKER CERTIFIED SOLUTION
Avatar of nutsch
nutsch
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account