Using Excel cell content in a stored procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Using Excel cell content in a stored procedure

Author

Topic

Lionheart
Starting Member

41 Posts

Posted - 2009-02-18 : 16:31:36

I have a stored procedure which is called from excel to populate a pivot table. What I would like to do is be able to change what the procedure pulls based on the content of a cell in the excel spreadsheet.

How do I reference the excel cell in the stored procedure?

Thanks,

LH

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2009-02-18 : 18:40:28

If you are using VB Script and ADO to access the stored proc, one way that you can reference a cell in an excel spreadsheet is by passing the value in the cell as a parameter to the stored procedure. The code snippet would be something like this:

Dim cmd As New ADODB.Command
   startDate = CDate(Range("C3").Value)
   Dim objParameter As ADODB.Parameter
   Set objParameter = cmd.CreateParameter("@startDate", adDBTimeStamp, adParamInput)
   objParameter.Value = startDate
   cmd.Parameters.Append objParameter

I assume you are already doing something like this to identify the stored procedure to be executed:


   cmd.CommandText = "uspMyStoredProc "
   cmd.CommandType = adCmdStoredProc

The code I have highlighted in red is one way to refer to contents of a cell. There are other ways; often times people find it more convenient to use R1C1 notation especially when the cell from which you need to pick up the value changes.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources