Hi folks,

I have a spreadsheet that queries a SQL Server. I would like to use the
values selected on some form controls as parameters to my queries. Eg: I will
query the server using in the where clause the values selected in a specific
listbox from the spreadsheet... the results will be showed in the same
spreadsheet in a destination pre-defined.

Did anybody do this before? Any hint or tip to help me?

Many thanks in advance and congratulations to every contributor that help to
make this community a high-level information source!

Leandro - BR

Hi dmoney,

I really appreciated your support but unfortunately I'm new to this stuff...

Could you please post the complete code, including the SQL connection, the
variable declaration and the defition of the destination?

It would help me a lot!

Thank you very much!

Cheers,
Leandro

"dmoney" wrote:

I do this dail -- just assign the values to a variable and include the
variable name between '" & variable & "' in the SQL statement

strsql = "SELECT xxx FROM yyyyyyyy" _
& "WHERE sssss='" & ActiveCell.Offset(columnoffset:=-5) & "'

"Excel interacting dinamically to SQL Srv" wrote:

Hi folks,

I have a spreadsheet that queries a SQL Server. I would like to use the
values selected on some form controls as parameters to my queries. Eg: I will
query the server using in the where clause the values selected in a specific
listbox from the spreadsheet... the results will be showed in the same
spreadsheet in a destination pre-defined.

Did anybody do this before? Any hint or tip to help me?

Many thanks in advance and congratulations to every contributor that help to
make this community a high-level information source!

Leandro - BR

I do this dail -- just assign the values to a variable and include the
variable name between '" & variable & "' in the SQL statement

strsql = "SELECT xxx FROM yyyyyyyy" _
& "WHERE sssss='" & ActiveCell.Offset(columnoffset:=-5) & "'

"Excel interacting dinamically to SQL Srv" wrote:

Hi folks,

I have a spreadsheet that queries a SQL Server. I would like to use the
values selected on some form controls as parameters to my queries. Eg: I will
query the server using in the where clause the values selected in a specific
listbox from the spreadsheet... the results will be showed in the same
spreadsheet in a destination pre-defined.

Did anybody do this before? Any hint or tip to help me?

Many thanks in advance and congratulations to every contributor that help to
make this community a high-level information source!

Leandro - BR

Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim strsql As String
Dim dbase As String
Dim listbox as string

dbase = "WhatIf" 'this is the catalog name on your sql server
listbox = listbox1.value

cn.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist
Security Info=False;" _
& "Initial Catalog='" & dbase & "';Data
Source=xxserverxx"
cn.Open
cn.CursorLocation = adUseClient

If OptionButton1.Value = True Then

strsql = "SELECT EAC FROM vblFccaSummary " _
& "WHERE xxdatabasefieldxx= '" & listbox & "'


"Excel interacting dinamically to SQL Srv" wrote:

Hi folks,

I have a spreadsheet that queries a SQL Server. I would like to use the
values selected on some form controls as parameters to my queries. Eg: I will
query the server using in the where clause the values selected in a specific
listbox from the spreadsheet... the results will be showed in the same
spreadsheet in a destination pre-defined.

Did anybody do this before? Any hint or tip to help me?

Many thanks in advance and congratulations to every contributor that help to
make this community a high-level information source!

Leandro - BR

There is a detail that makes that a little difficult...

The ListBox is MultiSelection enabled, so in the SQL Statement the WHERE
clause should be using the IN verb instead of equal to... how to do this? I'm
getting an error message because don't know how to use the '(' properly in
the statement.

Thanks!

"dmoney" wrote:

Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim strsql As String
Dim dbase As String
Dim listbox as string

dbase = "WhatIf" 'this is the catalog name on your sql server
listbox = listbox1.value

cn.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist
Security Info=False;" _
& "Initial Catalog='" & dbase & "';Data
Source=xxserverxx"
cn.Open
cn.CursorLocation = adUseClient

If OptionButton1.Value = True Then

strsql = "SELECT EAC FROM vblFccaSummary " _
& "WHERE xxdatabasefieldxx= '" & listbox & "'


"Excel interacting dinamically to SQL Srv" wrote:

Hi folks,

I have a spreadsheet that queries a SQL Server. I would like to use the
values selected on some form controls as parameters to my queries. Eg: I will
query the server using in the where clause the values selected in a specific
listbox from the spreadsheet... the results will be showed in the same
spreadsheet in a destination pre-defined.

Did anybody do this before? Any hint or tip to help me?

Many thanks in advance and congratulations to every contributor that help to
make this community a high-level information source!

Leandro - BR