Excel to execute stored procedure

[Bebe]

Anyone know the code to run a stored procedure in VBA or VB and populate Excel with the results -- I would take VB as well, but prefer VBA

[billwagnon]

I know VBA. I don't understand what you are trying to do. What do you mean by a stored procedure?

[Bebe]

How could I run a query or stored procedure from SQL in Excel. A stored procedure sits on the table in SQL ( a group of queries, essentially) and can be scheduled to execute manually or automatically.

[billwagnon]

That I don't know. Any ideas, Yonatan?

[Nitro]

I have to go to a meeting right now, but when I come back I will search for the codes.

Off hand you have to use the command Execute with Parameters.

[Nitro]

Hello Bebe!

Try this and give me an update!

Private conn As Connection
Private cmd As Command
Sub p_Stored_Procedure()
  'PURPOSE: Open the Connection
  Set conn = CreateObject("ADODB.Connection")
  conn.open "dsn=dsn;uid=username;pwd=password"
  
  'PURPOSE: Call Stored Procedure
  Set cmd = CreateObject("ADODB.Command")
  Set cmd.activeconnection = conn
  cmd.commandtext = "Procedure_name"
  cmd.commandtype = adcmdStoredProc
  cmd.Parameters.Refresh
  cmd.Parameters(1).Value = "Value1"
  cmd.Parameters(2).Value = "Value2"
  cmd.Parameters(n).Value = "Valuen"
  
  'PURPOSE: Get the results from the Stored Procedure
  Dim varReturn As Variant
  cmd.Execute
  varReturn = cmd.Parameters(0).Value
  
  'PURPOSE: Get the results set from Stored Procedure
  Dim rs As Recordset
  Set rs = cmd.Execute
  conn.Close
End Sub

[Nitro]

After you get the above code to work. I will be happy to help you populate the Result Sets into Excel. One thing at a time, otherwise you might get confuse.

[Bebe]

Wow, thank you this is a great start. I need to get the stored procedures done and I will take you up on your offer. What I have is a formatted type report in Excel -- with colors and stuff. Does than make it any more difficult. No, charts though!!!

Thanks again!!!

[Nitro]

Hello Bebe,

I recommend you do a small test stored procedure
first. The more complicated the stored procedure, the
more difficult to understand where the problem.

Once your stored procedure runs fine, then test the
vb code against the stored procedure.

I have been doing Excel VBA for a while so that should
be the easy part.

Good Luck!

To populate the spreadsheet, do either a CreateObject
or GetObject against Excel.