4

I have set up a connection to my SQL server to the database where the stored procedure is located. The Stored Procedure works fine in SQLServer. The Stored Procedure runs from the connection with hard coded parameters in Excel just fine. I get my dataset and it is inserted into my spreadsheet. The macro does not work. In the macro, I am trying to point to a range in the spreadsheet that contains my parameter values so I can type the values into the spreadsheet and then have the macro pick them up and pass them to the stored procedure. I am using date formats for both the spreadsheet values and the parameters in the Stored Procedure. I want the dataset returned to be updated in the spreadsheet with the new data when the macro runs. Here is my Macro:

Sub GetHeatMapData()

    With ActiveWorkbook.Connections("CARLA-PC-Billing-SP").OLEDBConnection.CommandText = "EXECUTE dbo.GetBillingHeatMap '" & Range("A9").Value & "" & Range("B9").Value & "'"

    End With
    ActiveWorkbook.Connections("CARLA-PC-Billing-SP").Refresh

End Sub

However if I try to run the Stored Procedure from a macro in Excel, one of two things happens:

  1. If there is an existing dataset in the spreadsheet that was created running the stored procedure from the connection window, then the macro runs without errors but it is not picking up the dynamic variables so the data does not change as it should.

  2. If I delete the data set created by running the Stored Procedure from the connection window, select the cell where the data should start, then fire the macro I get a 'subscript out of range' error and nothing happens.

I am setting NOCOUNT to off at the end of my Stored Procedure. Here are the parameter definitions in the Stored Procedure:

-- Add the parameters for the stored procedure here
    @StartDate Date, 
    @EndDate Date

Here are my connection settings: enter image description here

My question is why is stored procedure not getting my parameters from the Excel spreadsheet cells and using them to filter the returned data?

Improve this question

6 Answers 6

Reset to default
5

1 – expose parametars in excel

enter image description here

2 – define that parameters are entered from excel

enter image description here

3 – OK and refresh.

enter image description here

OK, You don't have to enter parameters in the command text, you can define command text in a way to expect parameters to be passed to the store procedure from excel cell. This is useful especially if your country don’t use US data format. Also, it enables end user to make refresh with some other parameters without need to edit command text. It is simple – in the excel that have connection to sql server in command text after store procedures name you enter ? – as many ? as you have different parameters that your store procedure expects (divided by ,) – like this - execute dbo.your_procedure ?,? After that, you go in the parameters tab (on the same form that you entered command text) and define from witch cell witch parameter is passed to store procedure. Of course, in the stored procedure also need to be specified what parameters are expected:

CREATE procedure [dbo].[your_procedure]

( @DateFrom datetime, @DateTo datetime ) As

--- your store procedure ---

In excel – parameter 1 will be send to the store procedure to a DateFrom parametar.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Could you share your inputs in text form, such that they can be copied?
using Excel 2016 and I cannot get the parameter button to become ungreyed...no matter what I type in, the button remains greyed out. I was able to simply put the values in there I wanted to pass but I would prefer using the parameters so a person could change it
Which connection type is this?
1

When you execute a stored procedure, the statement generally looks like:

EXECUTE myProc('param1', 'param2')

The way your command will expand will come out like:

EXECUTE myProc 'param1param2`

Which is nonsense. Try instead:

With ActiveWorkbook.Connections("CARLA-PC-Billing-SP").OLEDBConnection.CommandText = "EXECUTE dbo.GetBillingHeatMap ('" & Range("A9").Value & "','" & Range("B9").Value & "');"

You may still run into issues with the date formatting, so you can handle that in VBA too:

With ActiveWorkbook.Connections("CARLA-PC-Billing-SP").OLEDBConnection.CommandText = "EXECUTE dbo.GetBillingHeatMap ('" & FORMAT(Range("A9").Value, "m/d/yyyy") & "','" & FORMAT(Range("B9").Value, "m/d/yyyy") & "');"

Lastly, I find it good practice to send dynamically generated sql to a variable, and print it to the debug/immediate window before executing so I can catch stuff like that.

sqlStatement = "EXECUTE dbo.GetBillingHeatMap ('" & FORMAT(Range("A9").Value, "m/d/yyyy") & "','" & FORMAT(Range("B9").Value, "m/d/yyyy") & "');"
debug.print sqlStatement
With ActiveWorkbook.Connections("CARLA-PC-Billing-SP").OLEDBConnection.CommandText = sqlStatement

And now you'll have your statement in your immediate window, which you can copy and paste into a SQL client to execute independently of your code.

Improve this answer

3 Comments

Thank you for your response JNevill. I tried the first 2 suggestions and the macro ran without error but did not pick up the dates in the spreadsheet range so the returned data set was not filtered by the new date range. The stored procedure runs fine in the SQL Server client so I am not sure of what you are recommending in your third suggestion. I am building spreadsheets for non technical users. I need the macro to run the stored procedure using the predefined connection and the parameter values that they enter into the designated range and return an updated dataset.
The results I am getting are from the .CommandText value in the connection properties window. It does not appear to be dynamically changing the .CommandText value to the one in my macro.
It will only know of the commandtext that you sent to it. So if you see something hanging out in the commandtext property of the connection it must be from this VBA. Again, though, I would suggest doing that last step of sticking the commandtext into a string variable, and then sending the string to the debug/immediate window. When you run the macro, then you can see what is sent to the immediate window by going to view>>ImmediateWindow (or Ctrl+G). You'll see the commandtext print out in there so you can figure out what it is that you are sending up to the DB and properly debug this thing.
0

You're missing that comma

"EXECUTE dbo.GetBillingHeatMap '" & Range("A9").Value & "','" & Range("B9").Value & "'"

Improve this answer

1 Comment

Thank you for your response Mike Miller. I have tried it with and without the comma. Neither gives me the returned dataset filtered by the parameter values in the range cell of the spreadsheet.
0 
Sub Samp()
    Public con As New ADODB.Connection
    Public PassRecordset As New ADODB.Recordset
    Public objCmd As New ADODB.Command

    Dim Para1 As ADODB.Parameter
    Dim Para2 As ADODB.Parameter
    Dim Para3 As ADODB.Parameter

    'Check the database connection
    If con.State = 0 Then If Not CreateConnection 
       Then Exit Sub

    Set objCmd = New ADODB.Command
    objCmd.ActiveConnection = con

    With objCmd
      .ActiveConnection = con
      .CommandType = adCmdStoredProc
      .CommandText = StoredProcedureName

   End With
    
   Set Para1 = objCmd.CreateParameter("@TextPara1", adVarChar, adParamInput, 50, "Value1")
   objCmd.Parameters.Append Para1

   Set Para2 = objCmd.CreateParameter("@TextPara2", adVarChar, adParamInput, 255, "Value2")
   objCmd.Parameters.Append Para2
    
   Set Para3 = objCmd.CreateParameter("@DatePara3", adDate, adParamInput, , "DateValue")
   objCmd.Parameters.Append Para3
    
   Set PassRecordset = objCmd.Execute
    
   Sheet1.Range("A1").CopyFromRecordset PassRecordset

End Sub
Improve this answer

1 Comment

Above procedure will call the store procedure which is in the SQL Server with 3 parameters.
0

It might be very late to answer this question and we all might be aware of the recent ways to execute the parameterized stored procedures from Excel. But I devised a step-by-step method to achieve this and wanted to share it with the group. I hope it helps somebody else who is looking for the solution.

  1. Create a new Excel Sheet
  2. Choose the cell that you want to use for parameters and border it for ease
  3. Goto Data tab
  4. Get Data
  5. From Other Sources
  6. From Microsoft Query
  7. Click New Data Source and click OK
  8. Give the name
  9. Give driver as 'SQL Server Native Client' and hit connect
  10. Enter the server name
  11. Use Trusted Connection
  12. Goto Options and choose the Database and click OK
  13. Live the last Item empty and click OK
  14. Select new created Data Source and click OK
  15. It will ask which table you want to include in your query, click Cancel
  16. Then it asks, 'Do you want to continue editing this query in Microsoft Query?' - Click Yes
  17. It will open the query builder close the pop window,
  18. Click on the SQL button and it will ask for an SQL statement - Enter this query

{CALL [stored procedure] (?,?)} -- here (?,?) are for two parameters

{CALL [stored procedure] (?)} --just note: this is for 1 parameter

  1. Click OK
  2. Will show pop-up 'SQL Query can't be represented graphically. Continue anyway?' - click OK
  3. Will ask for Parameter - Enter it and click OK
  4. Goto File menu and choose 'Return Data to Microsoft Excel'
  5. Don't click OK, instead click 'Properties'
  6. Select 'Definition', inside Command text: we will have to associate with a particular cell
  7. Click 'Parameter'
  8. Choose 'Get the value from the following cell:' and on clicking on the icon on the right another pop-up will appear where we can select the particular cell (cell where we will provide the parameters, bordered cell in 2)
  9. Expand this pop-up by clicking the icon on the right
  10. Select the checkbox for 'Refresh automatically when cell value changes' (if we want to refresh dataauto, else don't, we can refresh the sheet later to pull dynamic data) and click OK
  11. Click OK again and it will ask where to dump the data
  12. Select the Cell from where you want the data
  13. Voila, On entering the parameter/s in the selected cell/s, and refreshing the sheet, will give the required data
Improve this answer

Comments

0

To simplify this kind of task, I have been using an Excel add-in called SaveToDb (www.savetodb.com) for a number of years. It's simple to use and works well. It adds a menu in the Excel ribbon. Here will reside fields relating to your SP parameters. Just change the values at runtime and the results will appear in Excel. You can add other columns to the extracted data, e.g. calculations and they persist when the data is refreshed. You can use SaveToDb to write data back to the database also - after reading the tutorials on how to do this in a controlled manner.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.