Drop-Down List

D

Doctorjones_md

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
 
G

Greg Glynn

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.
 
B

Bob Phillips

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)
 
D

Doctorjones_md

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 said:
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 said:
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
Click to expand...
Click to expand...
 
B

Bob Phillips

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 said:
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 said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Dynamic List Box using VBA 3
Code to Query SQL Server with a parameter value (entered into an Input Box), and have that value als 1
Drop down list control value in vba 2
Import data from Microsoft SQL Server into Microsoft Excel 0
Help!!! Database lookup from excel 2
appending data 3
Access Query from Excel VBA 5
Executing Stored Procedure 1

Top