Running a Stored Procedure from Excel.

Toekiller · 18 Apr 2007 at 20:28

I assume this is possible but I'm thick.

I have some parameter field I need to pass to a stored procedure that sit in excel. I want this sp to fill some temp tables and then drag the data back into the excel sheet.

Any pointers how to to the run the sql bit from excel?

I'm on 2007 btw.

Eriedor · 18 Apr 2007 at 21:10

Can you explain the problem abit more, sounds like you want to update a database from excel, can you clarify?

Toekiller · 18 Apr 2007 at 21:28

No nothing like that

I want to extract data from a sql database and put it into excel where I can report on it.

I have 5 parameters I need to pass from Excel into a stored procedure. This would then create the data in temp tables on the db. I can then do a simple link to each table and get it either into a pivot or as a data dump.

Toekiller · 19 Apr 2007 at 09:28

I'm thinking something like this...

Sub ExecSQL()

Dim stCon As String
Dim stSQL As String
Dim rngTemp As Range

stCon = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=Lynxassist;Data Source=KUNA;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=SH0839AJP;Use Encryption for Data=False;Tag with column collation when possible=False"

stSQL = "lynxassist.dbo.SSP_Decode_CV_Currency_Rates 'CO60193', '', '12 Apr 2007 23:59:59.000', 'USD', '6'"

Run stSQL
End Sub

But that just thinks the stSQL bit is a macro.

Eriedor · 19 Apr 2007 at 10:59

Ah I see what you mean, you need to use adodb which is quite simple:

Code:

Dim cn as ADODB.Connection
Set cn = New ADODB.Connection
With cn	
	.ConnectionString = stCon
	.Open
        .Execute stSQL
End With

Set cn = nothing