Run database queries and update cells in excel

If you want to import whole tables from a database into excel then you can go to the data tab in Excel and use a connection to an Access database or another type of database using an ODBC connection.

If you need to query the database then you can connect to the database and include the query in your code using  visual basic to define the connection database and then the query as well as where you want the results to go in your excel worksheet.

see http://www.dicks-clicks.com/excel/ExternalData3.htm for a discussion and example.

Thanks snail cat,

If I need to run a simple query like "select systedate from dual" into a database and put the value in a cel of excel , do I need a Visual basic or is there any other simpler way ?

Best
bond77

I am linking to oracle database.
I am trying to know if  there is any simple way besides visual basic

You could create a connection to the Oracle database using ADO and use it to run queries.

You should be able to return single values but I'm actually wondering why you would need a query to do something like that.

What sort of queries do you want to run in the database?

I need to take count of rows from a group of tables at intervals of time.
Could you please elaborate on teh ADO option in excel.

Best
Bond7

Also take a look at this past question with regard to ADO and ORACLE, just change your SQL to your needs:

https://www.experts-exchange.com/questions/21751278/querying-data-from-oracle-database-from-excel-vba-using-ADO.html

SELECT COUNT(*) FROM YOURTABLE

NG,

Hello,

I tried this approach, first it gave error for data source not found and no default driver found.
Then i changed driver to Oracle in OraDb10g_home1, now it is error specified driver could not be loaded due to system error 998 (Oracle in OraDb10g_home1)

I am suspecting this is due to drivers not found in my system.
I am using excel 2007. oracle client on my laptop is ora10g, destination oracle server = 8.1.7.4

Please suggest.

Hello,

Approach is
https://www.experts-exchange.com/questions/21751278/querying-data-from-oracle-database-from-excel-vba-using-ADO.html.

Two drivers were tried
a) Microsoft driver for oracle :- data source not found and no default driver found
b) Oracle in OraDb10g_home1 :- specified driver could not be loaded due to system error 998 (Oracle in OraDb10g_home1)

Aftre failing to get through , I tried installing Microsoft driver from microsoft.com but no change in error.

I tried creating dsn names but the errors were similar.
My summary so far:-
I do not have Microsoft driver for oracle
My Oracle in OraDb10g_home1 is having some issues, either permission issue or some other.

Looking at http://forums.oracle.com/forums/thread.jspa?threadID=433688 , I will check path again.

Any ideas will be great.

The only thing I can say is what you seem to already know - you've not got the right drivers.

Have you tried on any other computer?

Hello,

I agree permissions issue or a system path issue, i will check and inform tomorrow.


Best
bond7