SQL query using range from worksheet?

Question

Hi,

Is it possible to query a SQL server's data using a range of cells in Excel?  I have a list of names and ID's in an Excel table and I need to get the address information from a SQL server.

I'm using Excel 2010 and the MS Query tool, here's what I have tried:

SELECT contact.contact_id, contact.last_name, contact.first_name, contact.middle_name,

contact.date_of_birth, contact.ssn, cnt_addresses.city, cnt_addresses.state_code, cnt_addresses.zip_code

FROM contact

JOIN cnt_addresses on cnt_addresses.contact_id = contact.contact_id

WHERE contact.contact_id IN (SELECT * FROM worksheets("Sheet1").Range("A4:A6054"))

When I edit the query and try to save it, it tells me that SQL Query can't be represented graphically.  Continue Anyway?  and I answer OK.

Then I get "Incorrect syntax near 'A4:A6054' Statement(s) could not be prepared."

Any suggestions?  Am I way off the mark or is there a better approach I should be using?  Can it even be done?

Thanks in advance,

Linn

Answer 1

To the best of my knowledge you can not do what you are attempting to do in the way that you have described. MS Query will not interpret the range into a csv string for you. You can do it Via VBA code using ADODB and a bit of effort. That being said your in statement has 6050 parameters. SQL is not going to like that at all. If I was going to do what you are attempting I would speak with your DBAs and have them set up a table for you where you can load your contacts. You can use an ADODB connection to write directly from XL if you want. Once the table is populated the query is pretty easy just using and inner join.