Dynamic List Box using VBA

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 a database lists products that have been delivered to 4
different cities, on 3 seperate days -- without knowing (in advance) the
delivery dates, how could I create an input/drop-down field to allow the
user to specify a particular date? I envision something like this:

VBA code produces a list box of delivery dates based on a particular product
and city. The user selects a delivery date from the list-boxe, and this
selection gets passed to a SQL Select Statement to return the data to an
EXCEL spreadsheet. I just don't know of any way to do this ...

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

Bob
Mon Apr 16 03:44:47 CDT 2007

Do you have to do a subsequent SQL query, or could you just filter the data
that you already have?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Doctorjones_md" <xxxDoctorjones_mdxxx@xxxyahoo.com> wrote in message
news:e0cRxO9fHHA.4692@TK2MSFTNGP03.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 a database lists products that have been delivered to 4
> different cities, on 3 seperate days -- without knowing (in advance) the
> delivery dates, how could I create an input/drop-down field to allow the
> user to specify a particular date? I envision something like this:
>
> VBA code produces a list box of delivery dates based on a particular
> product and city. The user selects a delivery date from the list-boxe,
> and this selection gets passed to a SQL Select Statement to return the
> data to an EXCEL spreadsheet. I just don't know of any way to do this ...
>
> 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
>
>
>

doctorjones_md
Mon Apr 16 04:01:50 CDT 2007

Bob,

It appears to me (but I'm open to alternative suggestions) that I'll need to
perform an initial SQL query to create the list box, then pass the selection
from this list box to a subsequent SELECT statement for the final query.
I'm using a Date/Time stamp for the delivery date, so there isn't a way for
the user to know the exact delivery Date/Time without selecting it from a
list box.

I'm not certain that what I'm trying to do here is even possible -- any
thoughts on this?

Thanks for your prompt reply.

Shane
==================================
"Bob Phillips" <bob.ngs@somewhere.com> wrote in message
news:eSeLcNAgHHA.4188@TK2MSFTNGP02.phx.gbl...
> Do you have to do a subsequent SQL query, or could you just filter the
> data that you already have?
>
> --
> HTH
>
> Bob
>
> (there's no email, no snail mail, but somewhere should be gmail in my
> addy)
>
> "Doctorjones_md" <xxxDoctorjones_mdxxx@xxxyahoo.com> wrote in message
> news:e0cRxO9fHHA.4692@TK2MSFTNGP03.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 a database lists products that have been delivered to 4
>> different cities, on 3 seperate days -- without knowing (in advance) the
>> delivery dates, how could I create an input/drop-down field to allow the
>> user to specify a particular date? I envision something like this:
>>
>> VBA code produces a list box of delivery dates based on a particular
>> product and city. The user selects a delivery date from the list-boxe,
>> and this selection gets passed to a SQL Select Statement to return the
>> data to an EXCEL spreadsheet. I just don't know of any way to do this
>> ...
>>
>> 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
>>
>>
>>
>
>

Bob
Mon Apr 16 05:01:04 CDT 2007

If you do issue another query, you could issue it with SQL such as

sqlCommand = "SELECT * FROM Tracking_Specific " & _
"WHERE [Product Number] = " & ProdNumber & " AND " &
_
" [Date] = #" & Range("A1").Text & "#"

where A1 is the cell with the drop-down date selected.


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"doctorjones_md" <doctorjonesxxxxx_mdxxxxx@yahoo.com> wrote in message
news:ubfJ0WAgHHA.5044@TK2MSFTNGP05.phx.gbl...
> Bob,
>
> It appears to me (but I'm open to alternative suggestions) that I'll need
> to perform an initial SQL query to create the list box, then pass the
> selection from this list box to a subsequent SELECT statement for the
> final query. I'm using a Date/Time stamp for the delivery date, so there
> isn't a way for the user to know the exact delivery Date/Time without
> selecting it from a list box.
>
> I'm not certain that what I'm trying to do here is even possible -- any
> thoughts on this?
>
> Thanks for your prompt reply.
>
> Shane
> ==================================
> "Bob Phillips" <bob.ngs@somewhere.com> wrote in message
> news:eSeLcNAgHHA.4188@TK2MSFTNGP02.phx.gbl...
>> Do you have to do a subsequent SQL query, or could you just filter the
>> data that you already have?
>>
>> --
>> HTH
>>
>> Bob
>>
>> (there's no email, no snail mail, but somewhere should be gmail in my
>> addy)
>>
>> "Doctorjones_md" <xxxDoctorjones_mdxxx@xxxyahoo.com> wrote in message
>> news:e0cRxO9fHHA.4692@TK2MSFTNGP03.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 a database lists products that have been delivered to 4
>>> different cities, on 3 seperate days -- without knowing (in advance) the
>>> delivery dates, how could I create an input/drop-down field to allow the
>>> user to specify a particular date? I envision something like this:
>>>
>>> VBA code produces a list box of delivery dates based on a particular
>>> product and city. The user selects a delivery date from the list-boxe,
>>> and this selection gets passed to a SQL Select Statement to return the
>>> data to an EXCEL spreadsheet. I just don't know of any way to do this
>>> ...
>>>
>>> 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
>>>
>>>
>>>
>>
>>
>
>