Use Parameters in Excel Cells to return results from a SQL stored procedure

Posted on 2007-11-19
Last Modified: 2008-09-13
I have tried to get an answer to this on verious forums, but none are really giving me the answer.

Step 1: Microsoft Excel

Cell A1 = Orange
Cell B2 = Monkey

The Excel file also contains a pivot table.

Step 2: I want to use the values in A1 and B1 as parameters for a SQL Server 2000 Stored Procedure.

Thus, I need to know what steps must I take, to link to the Stored Procedure from excel, where the results are returned to the Pivot table.

Example of Stored procedure is shown below.....

How do i call the stored procedure?
How do i ensure is uses the paramters set?
How do i ensure that it returns the results to the pivot table?
Server = Busobjects
Database = Cap_plan
Create Proc sp_flickimp
@Colour varchar(6),
@Type varchar(6)
Select Location, sum(total_number)
From [Busobjects].Cap_plan.dbo.tbl_information as tbl
Where tbl.colour = @colour --this would be orange
and tbl.type = @type --this would be monkey
Group by Location

Open in new window

Question by:flickimp1717
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
LVL 15

Expert Comment

ID: 20315959
What I do is:  I have a worksheet for my sp results.  I have a routine called RunStoredProcedure.  After executing this routine you have a named range that you can use to define your pivottable.

I call the routine like this:
ContractID = Cells(2, 2).Value
Begindate = Cells(3, 2).Value
EndDate = Cells(4, 2).Value

RunStoredProcedure "NCCurrent", "NaturalClassXL2", Cells(2, 1), "@ContractID", ContractID, _
                        "@BeginDate", Begindate, "@EndDate", EndDate

NCCurrent is the name to use for the range
NaturalClassXL2 is the Stored Procedure Name
Cells(2,1) is the location to put the results

Const cnstr = "Provider='sqloledb';Data Source='SERVERNAME';Initial Catalog='DatabaseName';Integrated Security='SSPI';"
Sub RunStoredProcedure(RangeName As String, spname As String, ResultLocation As Range, ParamArray ArgList() As Variant)
'ParamArray should be list of Paramname and Value
'Returns a list of output parameter names and values
Dim cn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim rs As ADODB.Recordset
Dim CurrentCell As Range
If UBound(ArgList) Mod 2 = 0 Then
    MsgBox "Invalid Argument List in RunStoredProcedure - Contact Database Administrator"
    Exit Sub
End If
Set CurrentCell = ResultLocation ' .Row, ResultLocation.Column)
cn.ConnectionString = cnstr
cn.CommandTimeout = 300
Set cmd.ActiveConnection = cn
cmd.CommandText = spname
cmd.CommandType = adCmdStoredProc
cmd.CommandTimeout = 300
For i = 0 To UBound(ArgList) Step 2
    cmd(ArgList(i)) = ArgList(i + 1)
Set rs = cmd.Execute
' Print Field Names
For i = 0 To rs.Fields.Count - 1
    CurrentCell.Value = rs.Fields(i).Name
    Set CurrentCell = Cells(CurrentCell.Row, CurrentCell.Column + 1)
While Not rs.EOF
    Set CurrentCell = Cells(CurrentCell.Row + 1, ResultLocation.Column)
    For i = 0 To rs.Fields.Count - 1
        CurrentCell.Value = rs.Fields(i).Value
        Set CurrentCell = Cells(CurrentCell.Row, CurrentCell.Column + 1)
'Define Named Ranges
ActiveWorkbook.Names.Add RangeName, Range(ResultLocation, CurrentCell)
For i = 0 To rs.Fields.Count - 1
    ActiveWorkbook.Names.Add RangeName & Replace(rs.Fields(i).Name, " ", ""), Range(Cells(ResultLocation.Row, ResultLocation.Column + i), Cells(CurrentCell.Row, ResultLocation.Column + i))
Set rs = Nothing
Set cmd = Nothing
Set cn = Nothing
End Sub

Open in new window


Author Comment

ID: 20333308
Thanks for this, but I am not overly confident with VB and don't undertsand where my detials would go.

Could you not show me the above but with my relevant details from the code I pasted above?

This is critically urgent now.

Been to 5 different forums  and all have come with different answers.....but none are easy for me to understand...

LVL 15

Accepted Solution

JimFive earned 500 total points
ID: 20344890
In the code area, the first line needs your database connection info. All the rest of the code is generic.

Make a worksheet to hold the sp results

Create a command button to execute the sp.
The code for the command button should have

-------BEGIN CODE------------
ActiveWorkbook.Sheets("<Arguments WorkSheet>").Activate
Arg1 = Cells(2, 2).Value   ' Cells for Arguments
Arg2  = Cells(3, 2).Value  
Arg3 = Cells(4, 2).Value

RunStoredProcedure "ResultAreaName", "StoredProcedureName", Cells(2, 1), "@Param1", Arg1,"@Param2", Arg2, "@Param3", Arg3

------END CODE
ResultAreaName is used to name the range of the results, use this name for defining your pivottable

Cells(2,1) is the location to put the results in the ResultsWorksheet Anywhere is usually ok unless you are doing a lot of ranges then you need to make sure they don't overlap.

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

As technology users and professionals, we’re always learning. Our universal interest in advancing our knowledge of the trade is unmatched by most industries. It’s a curiosity that makes sense, given the climate of change. Within that, there lies a…
A company’s centralized system that manages user data, security, and distributed resources is often a focus of criminal attention. Active Directory (AD) is no exception. In truth, it’s even more likely to be targeted due to the number of companies …
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question