Run SP from Excel VBA - 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 2005 Forums

Transact-SQL (2005)

Run SP from Excel VBA

Author

Topic

JezLisle
Posting Yak Master

132 Posts

Posted - 2009-05-28 : 07:16:36

I am trying to run a Stored Procedure from Excel VBA, I have created a connection to SQL server and now I want to Execute the SP by picking up parameters in certain cells which I have named

DataParam1
DataParam2
DataParam3

How can I get it to run the SP using these?

This is what I have so far but keep getting error message "Object doesnt support this property or method.


Sub CYP()
Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim stProcName As String
Dim strFilePath As String
    strFilePath = "Driver={SQL Native Client};" & _
                            "Server=MyServer;" & _
                            "Database=MyDB;" & _
                            "Trusted_Connection=Yes"
    Set cnn = New ADODB.Connection
    cnn.Open strFilePath
'---------------------------------------------------------------------'Run Query for Physio Paediatrics
    Set rs = New ADODB.Recordset
    Application.ScreenUpdating = False
'Define name of Stored Procedure to execute
    stProcName = "EXEC dbo.sp_WL_SS_Name_Report '" & Worksheets("Check").DataParam1.Value & "', '" & Worksheets("Check").DataParam2.Value & "',  " & _
                            "'" & Worksheets("Check").DataParam3.Value & "', 'PD,PDD,PP,PGA,PBCS,PBCN,PDOS' "
    Sheet13.Range("B5:C10").ClearContents
    rs.CursorLocation = adUseClient
    rs.Open stProcName, cnn, adOpenDynamic, adLockOptimistic, adCmdText
    Application.ScreenUpdating = False
    Sheet13.Range("B5").CopyFromRecordset rs
    rs.Close
    Set rs = Nothing
    cnn.Close
    Set cnn = Nothing
    Exit Sub
End Sub

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-28 : 07:46:20

Which line produces the error?

E 12°55'05.63"
N 56°04'39.26"

JezLisle
Posting Yak Master

132 Posts

Posted - 2009-05-28 : 08:00:56

This line gives the error, sorry missed that off

stProcName = "EXEC dbo.sp_WL_SS_Name_Report '" & Worksheets("Check").DataParam1.Value & "', '" & Worksheets("Check").DataParam2.Value & "', " & _
"'" & Worksheets("Check").DataParam3.Value & "', 'PD,PDD,PP,PGA,PBCS,PBCN,PDOS' "

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-28 : 08:07:39

Is DataParam an object? Or a cell?

E 12°55'05.63"
N 56°04'39.26"

JezLisle
Posting Yak Master

132 Posts

Posted - 2009-05-28 : 08:51:55

Its a Named Range, would it be easier to work from a cell reference?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-28 : 09:09:44