2

So I've been stuck working on this problem for the last several days, and would really like a solution to it.

What I am doing is going into the data tab in excel and getting external data from my SQL server (I will provide a link to my database). And I select the Products table, and now have the table showing in my spreadsheet. Now what I am trying to do is open the connections tab, and click on the table that I am connected to, and go to the table properties and definition tab. Under Command Type I change that to SQL. In the Command Text I want to enter a dynamic query. What this dynamic query does is in Sheet2 cell a1 you are supposed enter a category and in cell a2 you enter some property for that category. So for example with the data I have you enter categoryid for the category and for the property you enter 1 and then sheet 1 is queried so it shows the productid, productname, supplierid, categoryid, unitprice, and discontinued for all products with categoryid 1.

I have written up something that I want to enter into the Command Text, but it is not working: SELECT * FROM "TSQL2012"."Production"."Products" WHERE [sheet2$a1] = [sheet2$a2];

database: File

Unzip it, and it is the TSQL2012 DB

If there is anything else that you need or have questions about, please post. This is the way I need to solve this problem so unfortunately other solutions will not be helpful.

Mitchell Walker
  • 211
  • 3
  • 8
  • 18
  • 1
    You say that line of code isn't working. What happens and what's the rest of the code you have to run that `Parameter Query`? – Skip Intro Jul 02 '13 at 14:57
  • I changed the code to: SELECT * FROM "TSQL2012"."Production"."Products" WHERE categoryid IN (SELECT * FROM [sheet2$a1:a2]) and i'm getting an invalid object name now. – Mitchell Walker Jul 02 '13 at 15:21
  • I think what I'm trying to do is very similar to the answer to this question: 'http://stackoverflow.com/questions/1285686/excel-use-a-cell-value-as-a-parameter-for-a-sql-query' – Mitchell Walker Jul 02 '13 at 18:44
  • I don't think any kind of quotes are needed around `TSQL2012`, `Production` or `Products`. Just `TSQL2012.Production.Products` should work or `[TSQL2012].[Production].[Products]` should work too – barrowc Jul 02 '13 at 23:00
  • barrowc, that still does not work unfortunately. It is still doing the same thing. The error I was getting was invalid column ID there, and when I changed my code I was getting an invalid object ID – Mitchell Walker Jul 03 '13 at 14:11
  • Looking at other SO questions - e.g. [this one](http://stackoverflow.com/q/12537629/53614) - suggests that the column name (represented by `[sheet2$a1]` in your original example) has to be a fixed value and not a parameter. You could get around this by building the query string yourself and concatenating in the actual value of `[sheet2$a1]` but SQL injection may become a concern - see [here](http://xkcd.com/327/) – barrowc Jul 04 '13 at 02:55

1 Answers1

0

Hmm try with vba execution on cell change maybe?

https://msdn.microsoft.com/en-us/library/office/ff821552.aspx

.CommandText = _ "Select ProductID From Products Where" & Range("A1").value & " = " Range("A2").value

Siemasz
  • 45
  • 7