SQL Server Stored Procedure

[JimP]

Is it possible to use a SQL Server stored procedure in an Excel query?

Where can I get documentation to get me started? I'm unfamiliar with Excel
programming, but pretty familiar with MS-Access, VBA and TSQL.

 

[Joel]

In excel you simply create an Access object and then use the same code as
Access except use the Access object. You also have to add the reference to
the Access library in excel from the VBA menu Tools - Reference - Microsoft
Access 11.0 object library.

You can also run queries that were dfefine in Access from excel. And
finally you can actual execute Access macro from excel like you requested.
let me know which method you prefer.

set obj = createobject("Access.Application")

or delcare the Access Object like in this code I wrote in January

Sub Submit()
'filename of database is with MakeDatabase macro

Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset

strDB = Folder & FName

If Dir(strDB) = "" Then
MsgBox ("Database Doesn't Exists, Create Database" & strDB)
MsgBox ("Exiting Macro")
Exit Sub
End If

ConnectStr = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & Folder & FName & ";" & _
"Mode=Share Deny None;"

cn.Open (ConnectStr)
With rs
.Open Source:="Submissions", _
ActiveConnection:=cn, _
CursorType:=adOpenDynamic, _
LockType:=adLockOptimistic, _
Options:=adCmdTable

If .EOF <> True Then
.MoveLast
End If
End With

With Sheets("Internal Project Plan")

ClientName = .Range("B4")
ImpMgr = .Range("B5")
LaunchDate = .Range("C4")

LastRow = .Range("K" & Rows.Count).End(xlUp).Row
For RowCount = 7 To LastRow

If UCase(.Range("K" & RowCount)) = "X" Then

DueDate = .Range("E" & RowCount)
ActualDate = .Range("F" & RowCount)
DateDif = .Range("M" & RowCount)
Accurate = .Range("L" & RowCount)
Task_ID = .Range("B" & RowCount)

With rs
.AddNew
!Task_ID = Task_ID
![Client Name] = ClientName
![Effective Date] = LaunchDate
![Imp Mgr] = ImpMgr
![Due Date] = DueDate
![Actual Date] = ActualDate
![Date Difference] = DateDif

.Update
End With
End If
Next RowCount

End With

Set appAccess = Nothing
End Sub

 

[gimme_this_gimme_that]

Hi JimP,

How can you be unfamiliar with Excel programming but be pretty
familiar with VBA?

And how can you be pretty familiar with VBA if you don't know ADO?

And it occur to you to search the Web before asking your question?

All you need to do is create an ADODB connection, a command object,
and run execute.

What is there not to know?

 

[Tim Zych]

Here's one example which returns a SQL Server recordset to Excel.

Sub RecordsetToXL()

Dim cn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim rs As ADODB.Recordset

cn.Open "Driver=SQL
Server;Server=<Your_Server>;Database=<Your_Database>"

If cn.State = adStateOpen Then

With cmd

' set the command object properties
.ActiveConnection = cn
.CommandText = "<Your_Stored_Proc_Name>"
.CommandType = adCmdStoredProc
' .Parameters("@OptionalParameterName").Value = "ParamValue"

Set rs = .Execute

' Stick in a sheet
Range("A1").CopyFromRecordset rs

End With

End If

Set cmd = Nothing
If rs.State = adStateOpen Then
rs.Close
End If
If cn.State = adStateOpen Then
cn.Close
End If
Set cn = Nothing
Set rs = Nothing

End Sub

This connection string above uses the "bare minimum" for Windows
authentication.

See www.connectionstrings.com for many more connection string examples.

 

[JimP]

I use DAO objects, not ADO. I also tried MSDN and didn't get very far.

Hi JimP,

How can you be unfamiliar with Excel programming but be pretty
familiar with VBA?

And how can you be pretty familiar with VBA if you don't know ADO?

And it occur to you to search the Web before asking your question?

All you need to do is create an ADODB connection, a command object,
and run execute.

What is there not to know?

 

[JimP]

Thanks to all - will check this out.

Here's one example which returns a SQL Server recordset to Excel.

Sub RecordsetToXL()

Dim cn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim rs As ADODB.Recordset

cn.Open "Driver=SQL
Server;Server=<Your_Server>;Database=<Your_Database>"

If cn.State = adStateOpen Then

With cmd

' set the command object properties
.ActiveConnection = cn
.CommandText = "<Your_Stored_Proc_Name>"
.CommandType = adCmdStoredProc
' .Parameters("@OptionalParameterName").Value = "ParamValue"

Set rs = .Execute

' Stick in a sheet
Range("A1").CopyFromRecordset rs

End With

End If

Set cmd = Nothing
If rs.State = adStateOpen Then
rs.Close
End If
If cn.State = adStateOpen Then
cn.Close
End If
Set cn = Nothing
Set rs = Nothing

End Sub

This connection string above uses the "bare minimum" for Windows
authentication.

See www.connectionstrings.com for many more connection string examples.