I think you are meaning dependent drop-downs. Take a look at
http://www.contextures.com/xlDataVal02.html and see if you get anywhere with
that.
--
HTH
Bob
(there's no email, no snail mail, but somewhere should be gmail in my addy)
"Doctorjones_md" <xxxDoctorjones_mdxxx[ at ]xxxyahoo.com> wrote in message
news:u$bsUDicHHA.5052[ at ]TK2MSFTNGP05.phx.gbl...
[Quoted Text] > Bob,
>
> Thank you for your prompt reply/assistance to my post. I looked at your
> example, and I'm wondering ...
>
> Will this allow the user to discriminate with recordsets to display?
>
> For Example, in my code I use an Input Box (ProdNumber) to narrow the
> query, but the users won't have access to the VBA code, and they won't
> know the cities/locales until the data is returned in the query to then.
> I thought there might be a way I could code a Drop-Down List (based on the
> return of the City/Locale) and allow the users to click on the City/Locale
> they want to display -- is this possible, and if so, could you be a bit
> more specific with how the code might look. ...
> =============
> 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
> ProdNumber = 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
> "Bob Phillips" <bob.ngs[ at ]somewhere.com> wrote in message
> news:Oc6fgvdcHHA.1240[ at ]TK2MSFTNGP04.phx.gbl...
>> Create a dynamic range to cover where you drop the data and use that in
>> the DD.
>>
>> =OFFSET($A$1,,,COUNTA($A:$A),1)
>>
>> if you have a header row, just subtract 1 from the COUNTA
>>
>> --
>> HTH
>>
>> Bob
>>
>> (there's no email, no snail mail, but somewhere should be gmail in my
>> addy)
>>
>> "Doctorjones_md" <xxxDoctorjones_mdxxx[ at ]xxxyahoo.com> wrote in message
>> news:u1mTqAXcHHA.4368[ at ]TK2MSFTNGP02.phx.gbl...
>>>I wasn't really sure how to phrase this one -- this isn't your Typical
>>>Drop-Down List.
>>>
>>> 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.
>>>
>>> Shane
>>>
>>
>>
>
>
|