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
2条答案
按热度按时间4nkexdtk1#
使用ADODB检索所需的值,并使用检索到的值填充Excel中的下拉形状,您可以动态创建该形状。
在类似的情况下,因为源数据基本上是静态的,所以我在应用程序启动时从ADODB记录集中填充了一个全局数组,并在填充下拉列表中的项目时使用该数组。
您必须从VBA编辑器中的“工具”〉“引用”设置对Microsoft ActiveX数据对象m.n库的引用(我的计算机上的最新版本是2.8)。
有关如何在Excel中管理下拉框的提示,请参阅文章http://www.thespreadsheetguru.com/blog/2014/5/14/vba-for-excels-form-control-combo-boxes。
hrysbysz2#
您可以使用Excel中的MS Query向导来存储查询并随时使用其数据。
点击此链接了解详情http://www.techrepublic.com/article/use-excels-ms-query-wizard-to-query-access-databases/