'Excel drop down values from a SQL Server source

I am trying to get a cell drop-down values in Excel from a SQL Server. I don't want to use the method of putting all the data to another sheet and the use data validation to control the drop down values. That always give my a bunch of empty lines towards the end since I want to make sure I have room for any addition in the DB.

Is there a way to retrieve the drop-down values directly from SQL Server? Using a statement something like:

Select name from employees

Thanks for your help...

sqlsql-serverexceldrop-down-menu


Solution 1:[1]

Use ADODB to retrieve the values you want, and use the retrieved values to populate a dropdown shape in Excel which you can create dynamically.

In a similar situation, since the source data was basically static, I populated a global array from an ADODB recordset when the application started and used that array when populating the items in the dropdown. Here's a snippet of that code:

Dim InstrumentIDs() As String
Dim InstrumentIDReader As Integer
Dim InstrumentIDCount As Integer
Public PositionRange As String

Public Sub GetInstrumentIDs()
'
'Populate InstrumentIDs array from current contents of Instrument table in   EMS database
'
   Dim conn As New ADODB.Connection
   Dim rs As New ADODB.Recordset
   Dim sql As String
   Dim loader As Integer, sn As String

   InstrumentIDReader = 0
   On Error GoTo GetInstrumentError
   conn.ConnectionString = "Provider=sqloledb; Data Source=myServer; Initial Catalog=myDatabase; User ID=myUser;Password=myPassword"
   conn.Open
   sql = "Select Count([SerialNo]) As [Number] From [Instrument]"
   rs.Open sql, conn, adOpenStatic
   InstrumentIDCount = CInt(rs![Number])
   ReDim InstrumentIDs(InstrumentIDCount - 1)
   rs.Close
   sql = "Select [SerialNo] From [Instrument] Order By [SerialNo]"
   rs.Open sql, conn, adOpenForwardOnly
   loader = 0
   rs.MoveFirst
   Do While Not rs.EOF
       sn = CStr(rs![SerialNo])
       InstrumentIDs(loader) = sn
       loader = loader + 1
       rs.MoveNext
   Loop
   rs.Close
   conn.Close
   Set rs = Nothing
   Set conn = Nothing
   Exit Sub
GetInstrumentError:
   MsgBox "Error loading instruments: " & Err.Description
End Sub

You must set a reference to Microsoft ActiveX Data Objects m.n Library (latest version on my computer is 2.8) from Tools > References in VBA editor.

See article http://www.thespreadsheetguru.com/blog/2014/5/14/vba-for-excels-form-control-combo-boxes for tips on how to manage dropdown boxes in Excel.

Solution 2:[2]

You can use the MS Query Wizard in Excel to store a query and use it's data any time.

This this link for details http://www.techrepublic.com/article/use-excels-ms-query-wizard-to-query-access-databases/

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 IdiotSavant
Solution 2 Vlad

Related Questions