VBA SQL query using input values from worksheet pull-down

[dcg38524]

Hi,

I am attempting (with no luck ) to build a SQL query that would accept an input value from a pull-down menu from existing workbook.

The input value would change per. pull down selection via. "Sheet1 - E7".

Example:

SELECT MODEL
FROM SQL_Table
WHERE
MODEL in ("Sheet1"[e7])



Any help would be greatly appreciated.

Regards,
Don

 

[Gary McMaster]

Hi Don,

Maybe something you can use here:

Query 5

Gary

 

[Fazza]

or Daily Dose of Excel » Blog Archive » Parameters in Excel external data queries

 

[dcg38524]

Thanks Fazza & Mark, for responding and posting the links.

Unfortunately, the links did not shed any light on what I was really looking for. Good news, with a lot of trial and error I was able to stumble across a solution to my original posted question.


Solution:
SELECT MODEL
FROM SQL_Table
WHERE
MODEL in '" & Sheets("Sheet1").Range("e7").Value & "'

With the above entry a user can select from a pull down menu a valid "model name" located in workbook "Sheet1.e7" and a database query will provide the results.

Regards,
Don

 

[Fazza]

Good work, Don.

Some comments, maybe these help

It is preferred to use a worksheet's code name instead of actual name - the actual name might be changed by a user but the code name less likely. If the user changes the name of "Sheet1" to "Report" then the VBA reference to the non-existent "Sheet1" will give an error. You can see the code name in the visual basic editor, or could even debug.print it something like
? sheets("Sheet1").codename

Range "E7" might move if rows or colums are added/deleted in the worksheet: then the cell you want to refer to will no longer be "E7", it might move say to "F8". With "E7" hard coded in the VBA, the code will still refer to "E7" and hence not have the right value. Normal practice is to use a defined name. So give cell "E7" a name, CTRL-F3, like "SelectedModel" and then in the code use sheetcodename.range("SelectedModel").value

Normal SQL WHERE clause syntax options, for info

WHERE MODEL = 'this text'
WHERE MODEL = 'this text' OR MODEL = 'other text'
WHERE MODEL = 123
WHERE MODEL IN ('some text')
WHERE MODEL IN ('this text', 'other text', 'further text', 'more text')
WHERE MODEL NOT IN ('this text', 'other text', 'further text', 'more text')
WHERE MODEL IN (123, 234, 345, 456)

cheers

 

[dcg38524]

Fazza,

I love it, excellent advice. I begin will implementing your suggestions ASAP thank you.

Kind regards,
Don