Solved

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

Posted on 2007-11-19
5
1,086 Views
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)

as

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

0
Comment
Question by:flickimp1717
  • 2
5 Comments
 
LVL 15

Expert Comment

by:JimFive
Comment Utility
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:
ActiveWorkbook.Sheets("Parameters").Activate
ContractID = Cells(2, 2).Value
Begindate = Cells(3, 2).Value
EndDate = Cells(4, 2).Value

ActiveWorkbook.Sheets("NCRaw").Activate
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.Open

cn.CommandTimeout = 300

Set cmd.ActiveConnection = cn
 

cmd.CommandText = spname

cmd.CommandType = adCmdStoredProc

cmd.CommandTimeout = 300

cmd.Parameters.Refresh
 

For i = 0 To UBound(ArgList) Step 2

    cmd(ArgList(i)) = ArgList(i + 1)

Next
 

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)

Next
 

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)

    Next

    rs.movenext

Wend
 

'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))

Next
 

rs.Close

cn.Close

Set rs = Nothing

Set cmd = Nothing

Set cn = Nothing
 

End Sub

Open in new window

0
 

Author Comment

by:flickimp1717
Comment Utility
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...

Lost....
0
 
LVL 15

Accepted Solution

by:
JimFive earned 500 total points
Comment Utility
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

ActiveWorkbook.Sheets("<ResultsWorksheet>").Activate
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.
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

Suggested Solutions

CCModeler offers a way to enter basic information like entities, attributes and relationships and export them as yEd or erviz diagram. It also can import existing Access or SQL Server tables with relationships.
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
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.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

762 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now