Wednesday, July 4, 2018

set parameter MS.Query on Excel

From what you mentioned I under stand you are using MS Query and you need to follow the following steps
Steps to make a parameterized query in Excel use a cell value as parameter
  1. First go to the Data tab in Excel and Select MS Query under External Data Sources
MS Query
  1. A pop up appears asking you to choose data source. Select the data source or add a new data source and select it. Make sure to uncheck Use the query wizard to create or edit queriesit is not very useful in most cases
Data Source
  1. Then a pop up appears asking you to add table select any table for now. It is better to select a table with less rows.
add table
  1. Then a Query window appears with the table you have selected and its data
query window
  1. Then click on the view menu and uncheck tables. This removes the graphically representation in query window and makes it easier to alter you sql query
View menu
  1. Click on the SQL button below the format menu in query window. A pop up comes where you can edit the query as you wish. Make sure to add question mark(s) for the parameter(s) you want.
edit query
  1. Then a pop up appears ask you for parameter value(s) enter some valid value for now
parameter
  1. Then the query window shows the result of query based on the parameter entered
result
  1. Click on the exit button below the view menu in query window. Then the query window closes and Import Data pop up comes asking where in excel to display the result. Choose accordingly
import
  1. Then click on Properties button on left before you click OK. A new pop up appears with the default usage tab selected.
Properties
  1. Make any changes needed in this tab and click on Definition tab. Click on Parameters button in bottom of the pop up next to edit query button
Definition
  1. Parameters pop up comes displaying the parameters used in the query. Here you must select the Get the value from following cell radio button to select the value of cell as the parameter for the query. Click OK and you should be done.
    parameter value

This method is for people with no VBA experience. If you know VBA then refer to this answer to achieve the something similar using VBA

CR. https://stackoverflow.com/questions/26413092/how-to-use-parameterized-query-in-excel-using-column-as-parameter