Pass an Excel Parameter to Power Query. I was delivering training yesterday in Sydney and one of my students (Stephane) asked me how to pass a parameter from Excel to Power Query. I have written previous articles about this principle at PowerPivotPro.com but thought it would be good to blog about how simple it is to do this. Customize a parameter query - Change the custom prompt for a parameter query On an Excel worksheet, click a cell anywhere in an external data range that was created. On the Data tab, in the Connections group, click Properties. In the Properties dialog box, click Connection Properties. I'm using MS Excel to get data from a MySQL database through ODBC. I successfully get data using an SQL query. But now I want that query to be parameterized. So I wonder If it is possible to use a. 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.

• Download Microsoft Query Analyzer
• Microsoft Query Sql
• Excel Microsoft Query Parameter Date
• Microsoft Query Criteria Parameters Excel
• Excel Sql Prompt For Parameter
• Excel Microsoft Query Too Few Parameters Expected 1

By: Junaith Haja Updated: 2015-01-16 Comments (54) Related: More >Microsoft Excel Integration

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.

Free new release movies streaming. 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.

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:

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

Download Microsoft Query Analyzer

Microsoft Query Sql

Excel Microsoft Query Parameter Date

About the author

Microsoft Query Criteria Parameters Excel

Excel Sql Prompt For Parameter

Excel Microsoft Query Too Few Parameters Expected 1