Passing Dynamic Query Values from Excel to SQL Server

Problem

We have always used Excel as a presentation layer to import data from SQL Server for analysis. For queries using parameters - if we have to change the values of parameters, we have to go to the Connection and make the change which isn't an easy way of doing it. In this tip we will learn about passing parameters from Excel cells to a Query at run time to import the data from SQL Server using Visual Basic.

Solution

We have to create the necessary Data Connection to Connect to SQL Server, we will be passing the values entered in the cells to the Query by the click of an ActiveXControl Command Button. The click of the command button will be coded in Visual Basic to pass the values from the cells to the data connection and import the data to the spreadsheet.

For our illustration, I have used a query from the AdventureWorks2014 database to find the List Price of all products by their SellStartDate and SellEndDate.

To make our work easier, I created a stored procedure using the below query with SellStartDate and SellEndDate as parameters.

CREATE PROCEDURE dbo.ProductListPrice @SellStartDate as Date, @SellEndDate as Date 
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
   SET NOCOUNT ON;

-- Insert statements for procedure here
SELECT
    PR.[Name] ProductName
       ,PS.Name SubCategory
       ,PC.NAME ProductCategory
       ,PM.Name ProductModel
       ,[StandardCost]
       ,[ListPrice]   
       ,CAST([SellStartDate] AS DATE) SellStartDate
       ,CAST([SellEndDate] AS DATE) SellEndDate
FROM [AdventureWorks2014].[Production].[Product] PR
INNER JOIN [AdventureWorks2014].[Production].[ProductSubcategory] PS 
ON PR.[ProductSubcategoryID]=PS.[ProductSubcategoryID] 
INNER JOIN [AdventureWorks2014].[Production].[ProductCategory] PC 
ON PS.ProductCategoryID=PC.ProductCategoryID 
INNER JOIN [AdventureWorks2014].[Production].[ProductModel] PM 
ON PR.ProductModelID=PM.ProductModelID
WHERE SellStartDate>=@SellStartDate AND SellEndDate<=@SellEndDate
ORDER BY SellStartDate,productname

END

Open a new Excel spreadsheet and enter our input parameters SellStartDate and SellEndDate as Labels in cells A3 and A4 and make B3 and B4 as their input fields, format them as input cell styles.

Create a Connection to SQL Server by clicking the "From Other Sources" and select the "From SQL Server" option from the Data ribbon.

Enter the server name in the Data Connection Wizard.

I have used my AdventureWorks2014 database for this demo, select it from the drop down list and select any table.

Rename the connection as "AdventureWorksConnection" in the "File Name" and "Friendly Name" fields.

Enter the cell you want to import the data from SQL Server, I would like to populate my data from cell A7 onwards.

Click the Properties button and go to its Definition tab and change its Command Type to "SQL" and in the Command Text box enter the name of stored procedure we created above and pass the parameters initially using empty quotes for the parameter values as shown below.

Click OK and you will see the field names from the stored procedure get populated from cell A7 as a table.

We are done with the data connection process, our next step is to pass the parameters to the query for which we will be using an ActiveX Command Button and Visual Basic code behind it to pass the parameters to the query. Please make sure you have the Developer tab enabled in your spreadsheet. If not go to File > Options > Customize Ribbon and Enable the Developer tab.

Go to the Developer tab we enabled, in the Insert option, double click the Command Button from the ActiveX Control tab.

It will open a Command Button in the Spreadsheet and position the Button where you want, enable the Design Mode and double click the Command Button. It will take you to the VB Script.

Enter the following code in the Command Button function:

Private Sub CommandButton1_Click()

Dim SellStartDate As Date  'Declare the SellStartDate as Date
Dim SellEndDate As Date    'Declare the SellEndDate as Date

SellStartDate = Sheets("Sheet1").Range("B3").Value   'Pass value from cell B3 to SellStartDate variable
SellEndDate = Sheets("Sheet1").Range("B4").Value     'Pass value from cell B4 to SellEndDate variable

'Pass the Parameters values to the Stored Procedure used in the Data Connection
With ActiveWorkbook.Connections("AdventureWorksConnection").OLEDBConnection
.CommandText = "EXEC dbo.ProductListPrice '" & SellStartDate & "','" & SellEndDate & "'"
ActiveWorkbook.Connections("AdventureWorksConnection").Refresh
    
End With
End Sub

Click Save and Save the Spreadsheet as a Macro Enabled format.

Uncheck the Design mode option. Enter the input parameters as 09/12/2011 and 09/12/2013 in the input cells B3 and B4 and click CommandButton1.

You will see the data gets populated in the Selected Range.

Also we can see the parameter values passed to the Stored Procedure by going to the Data Connection, you will see the parameters we entered in the input cells.

Next Steps

• Try Creating a Message Box to prompt the user about doing a data refresh with parameter values shown in the Message Box before the refresh.
• Take a look at these other Excel / SQL Server tips