6

I want to do exactly this: http://dailydoseofexcel.com/archives/2004/12/13/parameters-in-excel-external-data-queries/ but I want to do it in Excel 2010 and also I am querying SQL Server instead of MS Access. Is it possible?

To elaborate on what I want from that link exactly is to have query parameters that reference cells in the worksheet. At present I'm using VBA and a cell change trigger event. Is there a simpler way?


NOTE: Anyone who is using the method in the accepted answer, if you want your spreadsheet to work on a computer that does not have SQL Server installed on it, then you might find this helpful: https://dba.stackexchange.com/a/109221/46815

6
  • What is wrong with your approach/why don't you like it? You can do the same, as this is possible using Microsoft Query - the database *shouldn't matter! Did you try it yet and did it not work? Are you stuck at a certain point?
    – Dave
    Aug 15, 2013 at 12:33
  • @DaveRook Yes I've tried it, for OLE DB drivers it looks like there just isn't an option for parameters. The button is greyed out. My approach is fine but what a mission to write a bunch of macros when Excel can probably do it for me. Plus macros are less robust, if I move the cell then I have to go and manually change all the references in the code.
    – Dan
    Aug 15, 2013 at 12:42
  • I am sitting with the same problem - does anyone have a solution? May 23, 2014 at 9:44
  • @PeterMunnings I couldn't find any other way besides using VBA. Although in the end we stuck with a button rather than a cell change trigger to make it more obvious that there is VBA behind it
    – Dan
    May 23, 2014 at 13:29
  • I managed to do it with a simple query - you have to use the "From Mocrosoft Query" option in "From other sources", but as soon as I tried a more complicated query (using a pivot) it game me other errors. I am going to try invoking a Stored Proc which others have managed with. May 23, 2014 at 13:55

1 Answer 1

11

So I finally figured it out. Here are my steps.

  1. Open a new Excel worksheet (I'm using Excel 2010)
  2. Click on the Data tab on the ribbon
  3. Click the dop down on "From other sources"
  4. Select "From Microsoft Query"
  5. Select your datasource
  6. Pick any table to populate the Columns in your query box - this doesn't matter as we'll override it later - and click Next
  7. Keep clicking next until you get to the finish button, then click finish
  8. Click "Properties..."
  9. Click the "Definition" tab
  10. The connection type should say "Database Query" - if it says that, you can add parameters, if not, it doesn't seem to be able to add parameters
  11. Update your Command Text with the query you want to use to access the SQL database - haven't tried with other databases, but I'm sure it will work on others
  12. In order to create a parameter, simple put a ? in the where clause. e.g. select * from table1 where col1 = ?

There are some issues with adding parameters. They only work in the final where clause. i.e. if you are using subqueries, pivots, cte's etc you can't add parameters in those. Only at the end in the final where clause

  1. Click "Ok" twice - a pop up will appear asking you for the parameter

  2. Fill anything in - we'll change this later

  3. Click anywhere in the results and click "Properties" on the Data tab on the ribbon

  4. Click on the little hand property icon

  5. Click on the Definition Tab

  6. You will now see the "Parameters..." button is clickable. Click it and change the option from prompt for value to Get value from the following cell

If anyone finds a quicker, simpler way - I'm all ears, but this works consistently and with really big complicated queries. So for us, it is the right solution

1
  • Finally got a chance to do this, sorry it took so long! This works!!! My only suggestion would be to expand on point 3 as in my case I didn't have the connection set up and I'm not sure for example between SQL Server or SQL Server native client 11.0 etc. Otherwise great answer!
    – Dan
    Nov 17, 2014 at 12:25

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .