ODBC ControlSource
"Dkso" wrote ...
172,000 records on
this table (and obviously only 65,532 will fit onto an Excel spreadsheet).
I have used a ComboBox in MS Access to achieve what I want to do but don't
know how to do it in Excel.
I want to click on the ComboBox or start typing an item code and the item be
displayed in the dropdown box.
This in a userform with a combo named ComboBox1 (check the connection
string for northwind):
Option Explicit
Private Sub UserForm_Activate()
Dim Con As Object
Dim rs As Object
Dim strConJet As String
Dim strSql1 As String
' Amend the following constants to suit
Const CONN_STRING_JET As String = "" & _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Program Files\" & _
"Microsoft Visual Studio\VB98\NWIND.mdb"
strConJet = CONN_STRING_JET
' Build sql statement
strSql1 = "SELECT LastName FROM Employees"
' Open connection
Set Con = CreateObject("ADODB.Connection")
With Con
.Open strConJet
Set rs = .Execute(strSql1)
' Write data to combo
ComboBox1.List = _
Excel.Application.Transpose(rs.GetRows)
.Close
End With
End Sub
Jamie.
--
|