I have a query in an Excel table. I followed this to do it.
But how can I add a parameter from a cell into my query?
I am looking for a way to do this:
select *from dbo.Customerswhere CustomerID = Cell.A2The Cell.A2 does not work. But how can I add in something like it, so that the contents of a cell are used in my query?
Solution:
Dunno why MS has made this so complicated, You will have to use Microsoft Query.
Click on Data -> From External Sources -> From Microsoft Query. CHoose Data source comes up. Select SQL Server, enter the Auth details, and select the table
Click on Next, don’t select any filtering criteria, choose sort by criteria, click on next. Now, click on View/Edit in MS Query instead of selecting Return to Excel
Click on Finish. Now in MS Query, Click on Criteria -> Add Criteria, choose the operator and let the value be []
Click on File -> return data to Excel. Now Excel should prompt you for the parameter, select the relevant cell
To edit the parameters, click on Data -> Properties -> Finger icon -> Definition -> parameters
You can also use the SQL query editor and type in the query with the joins and put a ? against the field where the parameter has to be fetched.
