Drop-Down List
 

Shane,

The logic would be:

Perform the Query
Extract the Unique Cities from the list
Build the Drop-Down list from the unique list

You could build a hidden range somewhere of Unique Cities with a
simple loop that runs down your query, then you could point the Drop-
Down list to your hidden Range and use the Listobx.ListFillRange
property. [Alternately, as you find a unique City, use the .Additem
property to build the list as you work - As long as your original
query is sorted by City then the resulting drop-down will be sorted
too].

I don't know if that is too simple for your needs though.
Greg

- - - - - - - - - - -

I have a SELECT statement which queries SQL Server 2005 and displays
the
data in Excel.


Let's say that the data queried shows products in 4 different
cities,
without knowing (in advance) how could I create an input/drop-down
field to
allow the user to specify a particular city?


Below is the code I'm using --
===================
Option Explicit
Sub DataExtractSpecific()
' Create a connection object.
Dim cnExcel As ADODB.Connection
Set cnExcel = New ADODB.Connection


' Provide the connection string.
Dim strConn As String


'Use the SQL Server OLE DB Provider.
strConn = "PROVIDER=SQLOLEDB;"


'Connect to the DWS_Sales database on the Products Server.
strConn = strConn & "DATA SOURCE=xx.x.xx.xx;INITIAL CATALOG=Products;"
& _
"User Id=xxxxxxx;" & _
"Password=xxxxxx"


'Now open the connection.
cnExcel.Open strConn


On Error Resume Next


' Create a recordset object.
Dim OppNumber As String
Dim sqlCommand As String
'Dim CloseDate As Date
Dim rsExcel As ADODB.Recordset
Set rsExcel = New ADODB.Recordset
OppNumber = InputBox("Please Re-Enter Product Number for the 2nd
lookup
query.")
'CloseDate = InputBox("Enter the Purchase Date -- like 2/26/2007
11:44")
sqlCommand = "SELECT * FROM Tracking_Specific WHERE [Product Number] =
" +
ProdNumber
With rsExcel
' Assign the Connection object.
.ActiveConnection = cnExcel
' Extract the required records.
.Open sqlCommand
' Copy the records into cell A1 on Sheet1.
Sheet1.Range("A3").CopyFromRecordset rsExcel


' Tidy up
.Close
End With


cnExcel.Close
Set rsExcel = Nothing
Set cnExcel = Nothing


End Sub
===============
Many Thanks (in advance) for any assistance on this.