Retrieve Data to Excel with a Macro using ADO

2122009

The following Excel macro shows how to connect to an Oracle database using ADO, create a new worksheet in the current work book, and then fill in the returned data using a slow method (not recommended) and a fast method.

Actions

Information

19 responses

12122009

Steve Howard(12:26:32) :

Finally! Someone else who uses Excel to query an Oracle database :)

I have used Excel for this purpose since 1999, and constantly have colleagues trying to get me to change to use SQL Developer, TOAD, etc. While each has its merits (and I am sure functionality mine does not), I have found the ability to customize Excel to do exactly what I want always outweighs any loss in functionality (which normally isn’t that important to me anyway).

It is good to know that there is interest in blog articles related to using Excel for database access beyond what the Microsoft Query tool is able to provide. I agree that Excel is extremely flexible. Six months ago I demonstrated to a couple of regional user’s groups for an ERP package how to build things in Excel to extend the ERP system.

I’ve just started with Excel + Oracle combo.
Please tell me how strDatabase = “MyDB” is resolved ?
I’m using OID resloving method and that seems to not working with ADO.
Probably tnsnames file is what ADO needs ? Right ?
Regards.
Greg

However, it is my understanding that the above shortcut potentially leads to performance problems. If I recall correctly, ADO must repeatedly test whether or not snpData has been previously used, and therefore the object was previously created, every time it is accessed. The two line approach avoids this extra test.

Make certain that you add a reference to “Microsoft ActiveX Data Objects” in the Visual Basic References window, as stated in the above code.

Hi, this is interesting, the Excel collective I mean, and you may all be able to save me here. I’m a first time poster so any rules I have messed up please let me know where I should be post so I can.. I have been totally gazumped and really need support in some fashion.

I have an oracle database, and BIG (350 lines) query that has two WITH tables (one using the other) followed by 26 subsequent UNION ALL select statements that draw their data from the resultant WITH table. This runs fine in SQL developer tool. but then via the ADO (2.8) control I get nothing but zeros for all except the last union select statement

That is an interesting problem, a couple of thoughts:
* Are you using a “Provider=OraOLEDB.Oracle” in the ConnectionString? Please post the entire connection string (make certain that you replace the database username and password).
* When you establish the connection to the database, do you configure the connection to use client-side cursors? This is done with a command like this:

dbDatabase.CursorLocation = adUseClient

* ODBC connections can possibly re-write SQL statements before submitting those SQL statements to the database – the database may see an entirely different SQL statement. You can confirm that this is happening by enabling a 10046 trace for the session before submitting the SQL statement. A trace file will be created on the server that shows the exact SQL statement that is submitted. If I recall correctly, ODBC supports a “pass-through” option that does not allow the SQL statement to be rewritten before sending to the database – but that might be a DAO only concept, while the example on this page uses the ADO.
* What version of ADO did you reference in the project? You should be using at least ADO 2.8, which ships pre-installed on computers running Windows XP and later.
* What is the release version of the Oracle client? If you do not know, you can find out by opening a Windows command prompt and type:

tnsping

* What is the release version of the Oracle Database? You can determine the Oracle Database release version by connecting to the database using SQL*Plus (or another tool) and issuing the following SQL statement:

SELECT * FROM V$VERSION;

* Is it possible that the data types differ slightly between the different UNION ALL sections?

A SQL statement with a large number of UNION ALL statements will be used in the Excel program:

SELECT * FROM T1 WHERE C1 BETWEEN 1 AND 20
UNION ALL
SELECT * FROM T1 WHERE C1 BETWEEN 21 AND 40
UNION ALL
SELECT * FROM T1 WHERE C1 BETWEEN 41 AND 60
UNION ALL
SELECT * FROM T1 WHERE C1 BETWEEN 61 AND 80
UNION ALL
SELECT * FROM T1 WHERE C1 BETWEEN 81 AND 100
UNION ALL
SELECT * FROM T1 WHERE C1 BETWEEN 101 AND 120
UNION ALL
SELECT * FROM T1 WHERE C1 BETWEEN 121 AND 140
UNION ALL
SELECT * FROM T1 WHERE C1 BETWEEN 141 AND 160
UNION ALL
SELECT * FROM T1 WHERE C1 BETWEEN 161 AND 180
UNION ALL
SELECT * FROM T1 WHERE C1 BETWEEN 181 AND 200
UNION ALL
SELECT * FROM T1 WHERE C1 BETWEEN 201 AND 220
UNION ALL
SELECT * FROM T1 WHERE C1 BETWEEN 221 AND 240
UNION ALL
SELECT * FROM T1 WHERE C1 BETWEEN 241 AND 260
UNION ALL
SELECT * FROM T1 WHERE C1 BETWEEN 261 AND 280
UNION ALL
SELECT * FROM T1 WHERE C1 BETWEEN 281 AND 300
UNION ALL
SELECT * FROM T1 WHERE C1 BETWEEN 301 AND 320
UNION ALL
SELECT * FROM T1 WHERE C1 BETWEEN 321 AND 340
UNION ALL
SELECT * FROM T1 WHERE C1 BETWEEN 341 AND 360
UNION ALL
SELECT * FROM T1 WHERE C1 BETWEEN 361 AND 380
UNION ALL
SELECT * FROM T1 WHERE C1 BETWEEN 381 AND 400
UNION ALL
SELECT * FROM T1 WHERE C1 BETWEEN 401 AND 420
UNION ALL
SELECT * FROM T1 WHERE C1 BETWEEN 421 AND 440
UNION ALL
SELECT * FROM T1 WHERE C1 BETWEEN 441 AND 460
UNION ALL
SELECT * FROM T1 WHERE C1 BETWEEN 461 AND 480
UNION ALL
SELECT * FROM T1 WHERE C1 BETWEEN 481 AND 500
UNION ALL
SELECT * FROM T1 WHERE C1 BETWEEN 501 AND 520;

I am not sure how much help I will be able to provide to you. The error message that you received suggests one of three problems:
* If you are using the 32 bit version of Excel, you must have the 32 bit Oracle client installed (I believe that you will need the full client, not the Instant Client).
* When the Oracle client is installed, you will probably need to perform a custom installation and install all of the Windows interfaces (except the one with MTS in its name). In particular, you will need “Oracle Provider for OLE DB”.
* Finally, access rights on the computer need to be correct so that the user is able to access the Oracle client’s configuration files. If your user is not in the computer’s local administrator’s group, you may need to adjust the file permissions for the Oracle installation folder so that the user has at least read access to that folder and all of its contents. Additionally, User Access Control may need to be either set less restrictive or completely disabled.

In the script, where you see:

strDatabase = "MyDB"

MyDB is the name of the database from the computer’s tnsnames.ora file.

I have had to modify your code to use ODBC/DSN connection because Oracle OleDB is not available – I am connecting to an Oracle 10g database using the Instant Client. I get 34 rows of data back from the query. However when I run the same query in SQL Developer I get 138. I trust SQL Developer more than Excel VBA – so where should I look for the problem?

Please disregard. I used the following code to copy/paste the exact SQL query as formulated in my VBA and pasted/ran in SQL Developer. There was a problem in how I parsed variables and passed to SQL string.

There are a couple of potential causes for the problem that you experienced, such as:
* VPD (Virtual Private Database) – assuming that you were connecting as diffrent database users
* Referencing objects in different schemas – again, easy to be affected if you were connecting as different database users
* Referencing objects in different databases – easy to be affected if one program is using an ODBC connection while another is using an OLEDB or similar type connection that directly references the tnsnames.ora

Hi hooper thanks for your codding i used your code but i face some issue 1)Could not connect to the database. Check your user name and password. if i mentioned this code conn.CursorLocation = adUseClient sample from my code:

In the above, the variables strHost, strDatabase, pwd, strUser are declared as variants, while only strPassword is declared as a string. I prefer not to declare multiple variables on a single line, but if you must do that, you should change the above as follows:

Dim strHost As String, strDatabase As String, pwd As String, strUser As String, strPassword As String

—

conn.ConnectionString = "Driver={Microsoft ODBC for Oracle}; " & _

For me, the Microsoft ODBC driver for Oracle Database stopped working when my company switched to Oracle 8i (8.1.7.3) back in 2001 or 2002. There may be other issues with the contents of your connection string, but I did not spend much time examining the connection string.

The “conn.CursorLocation = adUseClient” line is returning an error because the adUseClient constant does not have a defined value. The adUseClient constant is given the correct defined value when you add a reference to the Microsoft ActiveX Data Objects Library as mentioned earlier in my comment.

snpData.MoveNext

You will want to change the above to the following because you named the recordset rs rather than snpData:

rs.MoveNext

I might have missed a couple of issues in your code, but the above should be enough to help you get started.

Hints for Posting Code Sections in Comments

********************
When the spacing of text in a comment section is important for readability (execution plans, PL/SQL blocks, SQL, SQL*Plus output, etc.) please use a <pre> tag before the code section and a </pre> tag after the code section:

<pre>

SQL> SELECT
2 SYSDATE TODAY
3 FROM
4 DUAL;
TODAY
---------
01-MAR-12

</pre>
********************
When posting test case samples, it is much easier for people to reproduce the test case when the SQL*Plus line prefixes are not included - if possible, please remove those line prefixes. This:

SELECT
SYSDATE TODAY
FROM
DUAL;

Is easier to execute in a test case script than this:

SQL> SELECT
2 SYSDATE TODAY
3 FROM
4 DUAL;

********************
Greater than and Less than signs in code sections are often interpretted as HTML formatting commands. Please replace these characters in the code sections with the HTML equivalents for these characters: