Drop-Down List

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

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" wrote in message
...
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

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" wrote in message
...
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" wrote in message
...
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

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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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