I need to create a button in Excel that when clicked will query an
Oracle database using a value stored in cell E1 as a restriction in the
query. Also I need the results of the query to be listed in a range of
cells in the workbook on the same sheet the button exists.

example:

If cell E1 = 10, then I need a button that when pressed runs a query
across an Oracle database;

Select name, number
from Panel
where number = '10'

I'm expecting 30 to 200 names to be returned. I would like these names
from the oracle db to be populated in cells IT4 - It 300.

Thanks,
Dan


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements

drs****** wrote in
:


I need to create a button in Excel that when clicked will query an
Oracle database using a value stored in cell E1 as a restriction in
the query. Also I need the results of the query to be listed in a
range of cells in the workbook on the same sheet the button exists.

example:

If cell E1 = 10, then I need a button that when pressed runs a query
across an Oracle database;

Select name, number
from Panel
where number = '10'

I'm expecting 30 to 200 names to be returned. I would like these names
from the oracle db to be populated in cells IT4 - It 300.

Thanks,
Dan


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from
http://www.ExcelForum.com/

~~Now Available: Financial Statements.xls, a step by step guide to
creating financial statements


You'll most likely want to do this using ADO. Add the button to your
sheet and in the code behind it you'd open the connection & recordset,
then loop through the recordset adding values to cells as you iterate
through.

Private Sub CommandButton1_Click()
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim RowCurr As Long
Dim ColCurr As String
Dim ws As Excel.Worksheet

RowCurr = 4
ColCurr = "I"
Set ws = CommandButton1.Parent

cnn.Open YourConnectionStringToOracle
rst.Open YourSqlStatement, cnn

Do Until rst.EOF
ws.Range(colCurr & RowCurr).Value = rst.Fields("YOUR_FIELD").Value
RowCurr = RowCurr +
rst.MoveNext
Loop
rst.Close
cnn.Close
End Sub

--
Andrew Mauer

To reply directly, remove .nospam from address.