Use Cell Range As Where Values in a Data Connection to SQL Server

Question

I am working on a tool in Excel to rapidly validate data updates to a set of databases.

In Sheet 1, I will have the source data, which consists of 5 columns; Column A is the Primary Key. This is delivered as a CSV and copy/pasted into the sheet.

In Sheet 2, I would like to have a data connection to the database machine that filters on the values of Sheet 1, Column A, such that only those Primary Keys in Sheet 1 will be returned and displayed.

I have tried looking at setting Parameters in the Connection Properties dialog, but the button is disabled:

Thank you in advance.

EDIT: This is Excel 2010, and this image shows the method I used to create the connection on Sheet 2:

EDIT 2: Attempted to use Microsoft Query to create the connection, but it remains disabled.

How did you make the connection on Sheet2? and what version of Excel is this? — RBarryYoung
– RBarryYoung, Commented Jan 30, 2013 at 19:28

RBarryYoung · Accepted Answer · 2013-01-30 21:38:34Z

1

You can only add and manage Query Parameters in Excel by using Microsoft Query. So when you create the Connection, on the "From Other Sources" button-dropdown, select "From Microsoft Query" instead of "From SQL Server" (or any other source).

answered Jan 30, 2013 at 21:38

RBarryYoung

57k15 gold badges99 silver badges142 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Matthew Shea Over a year ago

I have created the connection using a Microsoft Query, but the Parameters button remains locked. i.imgur.com/lQPppqS.png

RBarryYoung Over a year ago

Did you edit the query text to add in the question marks("?") for your parameters first?

Matthew Shea Over a year ago

After doing this "where code = ?", the Parameters button appears, but it seems that it only allows me to source a parameter from a single cell, rather than a range. i.imgur.com/9EJSV1s.png

RBarryYoung Over a year ago

Yes, that is correct. Until very recently, SQL parameters had to be scalar values. I.E., single numbers, strings or dates, they could not be lists, arrays or any other data structures. And the new capabilities (to send a table/list as a parameter) is only accessible from ADO.Net, which Excel does not use. So yes, only single cells can be query parameters, not ranges.

RBarryYoung Over a year ago

To do this in Excel, you would have to write an Excel-VBA procedure that would extract all of the key values from the Range and then actually send them all to the database (using one kludgy method or another), so that SQL could compare them and return the matches.

Dead.Rabit · Accepted Answer · 2013-05-30 11:28:33Z

0

You can add query parameters to a SQL Query as well as Microsoft Queries, just place a ? in the SQL query and the parameters button will become active, or a dialog will display prompting for data the next time the connection is refreshed.

It looks like multiple parameters would be possible, however I haven't found a working syntax for them yet.

answered May 30, 2013 at 11:28

Dead.Rabit

1,9751 gold badge29 silver badges47 bronze badges

Collectives™ on Stack Overflow

Use Cell Range As Where Values in a Data Connection to SQL Server

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related