Excel: populateitems in data validation drop down list from SQL server table

1
votes

I want to have a drop down list that gets its members dynamically from a SQL server table. I don't want to create a source range or table somewhere else in the Excel application, but directly retrieve and populate the list from the SQL server table.

I will be generating validation objects on a range of cells in VBA (which I know how to do) but I don't know how to use the external data source (SQL server table) to populate the list. My model is ASPX, MS Access or Windows applications that allow setting the row source for such a list from a table or query in a data source such as SQL server.

1
sql-serverexcelvalidationdropdown
Your model doesn't port well to excel, especially data validation drop down lists. Your best bet is a querytable that you drop the data into and perform your validation on that. You can also use combobox com object and ADO in VBA to fill it, but that's quite a learning curve if you haven't done that sort of work before. – JNevill

1 Answers

0
votes

I ended up using a drop down shape and populating its entries using ADODB. The following article was most helpful with samples concerning this technique: http://www.thespreadsheetguru.com/blog/2014/5/14/vba-for-excels-form-control-combo-boxes.