Excel with Stored Procedures

Question

Excel with Stored Procedures

Viewing 15 posts - 46 through 60 (of 120 total)

You must be logged in to reply to this topic. Login to reply

Old SQL Newbie Ten Centuries Points: 1030 More actions · Answer 1

David, you explained it better than I did, but that's exactly the problem I am having. Does anyone know how to do this? Thanks!

Eric L Hackett SSC Eights! Points: 978 More actions · Answer 2

Try this.

Dim prm As ADODB.Parameter

Dim cmd As New ADODB.Command

cmd.CommandType = adCmdStoredProc

cmd.CommandText = "dbo.proc_StoredProcedure"

Dim strParameterValue As String

strParameterValue = Sheet1.Range("A1").Value

Set prm = cmd.CreateParameter("@ParameterName", adVarChar, adParamInput, 50, strParameterValue)

cmd.Parameters.Append prm

If you use Command type of adCmdText then you will need to concatenate until you have the full SQL statement.

strCommand = "Exec dbo.sp_Proc @ParameterName = " & strParameterValue

Old SQL Newbie Ten Centuries Points: 1030 More actions · Answer 3

Thanks, now I'm sure I'm in over my head. :w00t:Where do you put all that? In the exec proc_exceltest?

david0044 SSC Veteran Points: 206 More actions · Answer 4

Thanks for the quick reply. At the risk of baring all of my ignorance at once, that looks like VBA (?). Unfortunately, all I know about VBA is how to spell it. I'm trying to keep the connection info contained in the Connection Properties>Definition>Command Text box. It looks like it should be possible because I can hard code a parameter. But I can't figure a correct syntax for a cell reference.

I'm quickly getting the feeling that I'll need to actually learn some VBA to really up my ability to manipulate my data.

Thanks again (and I'm still open to other thoughts),

David

Eric L Hackett SSC Eights! Points: 978 More actions · Answer 5

I just realized you said Excel 2007, which I've yet to use.

If you read this thread from the beginning, I believe you will find a satisfactory VBA-less method of using cell references for stored procedure parameters.

Old SQL Newbie Ten Centuries Points: 1030 More actions · Answer 6

I read the thread from the beginning the day it was started, and my original post says I tried that and it does't work in Excel 2007.

david0044 SSC Veteran Points: 206 More actions · Answer 7

Good morning,

I'm still struggling with this issue, so any help would be appreciated. Just to refresh, I'm trying to call a SQL 2000 stored procedure from Excel 2007. I am using an SP so I can pass a parameter and return a subset of data. Because I'm using an SP, I haven't figured out a way to use MSQuery-it wants tables or views (and I'd prefer not too for simplicity sake). Also, I don't know much about VBA, so I'd prefer not use it (although that's beginning to look inevitable).

So, in the Excel>Connection Properties>Definition>Command Text I can execute a stored procedure and pass a hard coded parameter (e.g. exec proc_exceltest '12/1/2008') and get the desired results. What I'd like to do is pass a cell reference in the same statement, from the command text window (e.g. exec proc_exceltest [Sheet1$A1]). My only problem is that I can't find the proper syntax.

Do I need to use the =?, similar to what MSQuery creates? And if so, does the parameter need to be declared and set? Or, can I just reference a cell range in the worksheet to pass back to SQL.

Thanks for your help,

David

Korey Ewing SSC Veteran Points: 207 More actions · Answer 8

The syntax for calling a SQL Server stored procedure with parameters in Excel 2007 (maybe even for 2003, not sure): {Call sp_name(?,?)}

You can add a connection through ms query, as usual. Then instead of adding tables, cancel and choose to edit your connection through Ms query. Once in Ms Query, click the SQL button which allows you to write free form SQL, and enter your stored procedure call.

You will be warned that the query can't display graphically. But you don't care.

david0044 SSC Veteran Points: 206 More actions · Answer 9

Thanks for the help. I was able to get the following to work:

{call proc_exceltest('12/1/2008')}, but as soon as I change it to

{call proc_exceltest('Sheet1$I1')}, I get an error (invalid character value for cast specification). I'm guessing its taking the 'Sheet1$I1' literally instead of passing the value thats in the cell?

Any thoughts?

Thanks again,

David

Old SQL Newbie Ten Centuries Points: 1030 More actions · Answer 10

Just curious, because I took the Parm out of my SP and have nothing to test with: did you try it without the tick marks?

Thanks

Eric L Hackett SSC Eights! Points: 978 More actions · Answer 11

In Excel 2003:

If you leave the ? in the call, it should prompt for a value. Enter a value to get a result set, then use the Send to Excel button to send the data back to Excel. Right-click a cell within the returned data and choose 'Parameters'. This will bring up a dialog for referencing a cell.

Is this not the same in Excel 2007?

david0044 SSC Veteran Points: 206 More actions · Answer 12

I hadn't but when I just tried

{call proc_exceltest(Sheet1$I1)} I got the same error.

Any other thoughts? I have not been able to find a decent language reference anywhere.

david0044 SSC Veteran Points: 206 More actions · Answer 13

Eric,

I was trying to do this directly from the connection properties, without using Query. When I tried this from the connection properties

{call proc_exceltest(?)}, I get 'no value given for required parameters'. I hoped it would open the parameters dialog (similar to Query), but it doesn't.

Old SQL Newbie Ten Centuries Points: 1030 More actions · Answer 14

I had tested this a few weeks ago and it appears Excel 2007 is different. I could not get a cell reference to work. (I can't look just now but I think I posted that comment earlier in this topic.)

david0044 SSC Veteran Points: 206 More actions · Answer 15

Just to try something different, I created the connection through MSQuery. I used the SQL box and successfully got

exec proc_exceltest '12/10/2008' to work. As soon as I opened the properties and changed it to

exec proc_exceltest ? it gave me a

[MS][ODBC SQL Server Driver]Invalid parameter number, and

[MS][ODBC SQL Server Driver]Invalid Desriptor Index, and

The following data range failed to refresh.

I was able to access the parameter button and link the correct cell to the parameter, but I still get the same errors everytime the cell values change.