Using SQL commands in MS Excel 2007

Question

Hi. How to run SQL query from the Excel 2007 worksheet without using Microsoft Query and VBA? I tried to use SQL.REQUEST function but it only works on MS Excel 2002. What function I need to use in Excel 2007? Thank you.

Answer 1

Correct.

In VBA you can either automate Get External Data (you can record a macro while doing it manually to get a good start), or you could use ADO or DAO functions to create a recordset and then use CopyFromRecordset.

I recall using SQL.REQUEST in the early 1990s when I was writing Excel 4 macros.  I seem to remember that it was quite an expensive function and difficult to use, with difficulties whenever strings exceeded 255 characters.  

Excel 97 introduced better methods of importing external data and they have evolved into what we have now as Get External Data.  I wouldn't be surprised if the xlodbc.xla add-in that included SQL.REQUEST would still work if you were able to find a copy.

however, I think the facilities we have today are vastly superior.

Answer 2

I'm not really sure why he wants it that way. Sorry..

I've found this link on youtube. May I know how it is done?...

https://www.youtube.com/watch?v=L\_WqmWM6MKU

Thank you.

Answer 3

I know no way to do that.  Sorry.

To do anything with the connection string and the SQL statement in the worksheet would require VBA which you have also ruled out.

I would again ask why Data > Get External Data, or using VBA, is not a good solution...

Answer 4

Yes, but my supervisor does not want to use the Data > Get External Data. He wants to run the query from the worksheet. I have the connection string and sql statement written on the worksheet.

Connection string: DRIVER={Microsoft ODBC for Oracle};UID=xxxxxxx;SERVER=xxxxxx;

Sql statement = SELECT * FROM table_name

How do I run this on Excel?

Thank you.

Answer 5

I'm assuming that your aim is to run a SELECT query and get data into worksheet cells.

What is the source of the data?

Why would you not want to use the Data > Get External Data method appropriate to that source?

(No VBA would be required).