Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
drop down list based on other drop down list pick | Excel Discussion (Misc queries) | |||
Drop down list dependant on previous drop down list | Excel Discussion (Misc queries) | |||
Drop down lists that auto create and then filter the next drop down list | Excel Worksheet Functions | |||
Drop Down List choice selecting another drop down list | Excel Worksheet Functions | |||
multiple select from the drop down list in excel. list in one sheet and drop down in | Excel Discussion (Misc queries) |