How to bind variables from Excel sheet to an SQL query in Excel SQL connection with Oracle

Anonymous

HI,

In an Excel sheet I did an Oracle SQL Connection and i need to do display the following results:

Select * From test_table where rownum < ???

Where ??? is equal to a data present in cell D4 of sheet 1.

How do i write my SQL statement inside the data connection?

THanks in advance.

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

11 answers

Anonymous

2012-08-14T12:23:12+00:00

Voila

dim cn as connection

dim rs as recorset

strSql = "Select * From test_table where MyValue < ' " & sheet1!D4 & " ' ; "

rs.Open strSQL, cn, adOpenStatic, adLockReadOnly

Regards

JY
Was this answer helpful?

0 comments No comments
Anonymous

2012-08-14T11:55:22+00:00

You might also look into OO4O (Oracle Objects for OLE)

http://www.oracle.com/technetwork/database/windows/downloads/utilsoft-433581.html

I used it some years ago in working with an Oracle database and makes retrieving data quite easy. But looking at that page it looks like it hasn't been updated beyond a version for Excel 2003, which might or might not work with 2007/2010 32-bit, doubtful if it would work with 64-bit versions of Excel.
Was this answer helpful?

0 comments No comments
Anonymous

2012-08-14T11:36:20+00:00

Thank you for your help.

I think i'd need to write the whole thing in a macro..

thank you anywayz :D
Was this answer helpful?

0 comments No comments
Anonymous

2012-08-14T11:32:52+00:00

If is posible, change the conection type to Database Query..
Was this answer helpful?

0 comments No comments
Anonymous

2012-08-14T11:21:07+00:00

Hello,,,

the thing is that parameters is not appearing :(

i am using OLEDB to connect to oracle.

any hints?

http://imgf.tw/318949384.JPG
Was this answer helpful?

0 comments No comments