Connection String to SQL to run Stored Procedure

[Carl]

Does anyone have samle code to connect and run a stored Proc on SQL Server?
I have the Server name. It is added to my ODBC and connection is good, but
to simply connect and run a sp from Excel is driving me crazy.
Thanks

 

[Nigel]

This can drive you mad!!

Try recording a macro while using the menu options Data->Import External
Data and using the appropriate connections etc. It should give you a
starter template if not the whole answer.

 

[paul.robinson]

Hi
This from code samples with "Excel 2002 VBA" by Stephen Bullen et al

Public Sub SavedQuery()

Dim objField As ADODB.Field
Dim rsData As ADODB.Recordset
Dim lOffset As Long
Dim szConnect As String

' Create the connection string.
szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Files\Northwind.mdb;"

' Create the Recorset object and run the query.
Set rsData = New ADODB.Recordset
rsData.Open "[Sales By Category]", szConnect, adOpenForwardOnly, _
adLockReadOnly, adCmdTable

' Make sure we got records back
If Not rsData.EOF Then
' Add headers to the worksheet.
With Sheet1.Range("A1")
For Each objField In rsData.Fields
.Offset(0, lOffset).Value = objField.Name
lOffset = lOffset + 1
Next objField
.Resize(1, rsData.Fields.Count).Font.Bold = True
End With
' Dump the contents of the recordset onto the worksheet.
Sheet1.Range("A2").CopyFromRecordset rsData
' Close the recordset
rsData.Close
' Fit the column widths to the data.
Sheet1.UsedRange.EntireColumn.AutoFit
Else
MsgBox "Error: No records returned.", vbCritical
End If

Set rsData = Nothing

End Sub

regards
Paul