Excel drop down values from a SQL Server source

Question

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...

IdiotSavant IdiotSavant · Accepted Answer · 2016-06-24T20:15:57

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.

Excel drop down values from a SQL Server source

2 Answers