使用ADODB检索所需的值，并使用检索到的值填充Excel中的下拉形状，您可以动态创建该形状。
在类似的情况下，因为源数据基本上是静态的，所以我在应用程序启动时从ADODB记录集中填充了一个全局数组，并在填充下拉列表中的项目时使用该数组。

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

您必须从VBA编辑器中的“工具”〉“引用”设置对Microsoft ActiveX数据对象m.n库的引用（我的计算机上的最新版本是2.8）。
有关如何在Excel中管理下拉框的提示，请参阅文章http://www.thespreadsheetguru.com/blog/2014/5/14/vba-for-excels-form-control-combo-boxes。

您可以使用Excel中的MS Query向导来存储查询并随时使用其数据。
点击此链接了解详情http://www.techrepublic.com/article/use-excels-ms-query-wizard-to-query-access-databases/