René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback -
 

February 01, 2007: On importing data from Oracle into Excel with CopyFromRecordset

In my last blog entry, I have written a little Excel macro. This macro opens a connection to an Oracle database, issues a select statement and then iterates over each record within the result set. For each record, it also iterates over each attribute within this record.
Now, there is CopyFromRecordset which does this iteration. I only have to specify the top left cell where I want my result set to appear.
Here's the macro: 
Sub DisplayDBATables()

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset

cn.Open ( _
    "User ID=rene"        & _
    ";Password=my_secret" & _
    ";Data Source=XE"     & _
    ";Provider=MSDAORA.1" )

rs.Open "select * from dba_tables", cn

Cells(1, 1).CopyFromRecordset rs

End Sub

More on Oracle

This is an on Oracle article. The most current articles of this series can be found here.

Warning: require(): open_basedir restriction in effect. File(/var/www/virtual/adp-gmbh.ch/forum/comment.inc) is not within the allowed path(s): (/home/httpd/vhosts/renenyffenegger.ch/:/tmp/) in /home/httpd/vhosts/renenyffenegger.ch/adp-gmbh.ch/blog/2007/02/01.php on line 255

Warning: require(/var/www/virtual/adp-gmbh.ch/forum/comment.inc): failed to open stream: Operation not permitted in /home/httpd/vhosts/renenyffenegger.ch/adp-gmbh.ch/blog/2007/02/01.php on line 255

Fatal error: require(): Failed opening required '/var/www/virtual/adp-gmbh.ch/forum/comment.inc' (include_path='.:/home/httpd/vhosts/renenyffenegger.ch') in /home/httpd/vhosts/renenyffenegger.ch/adp-gmbh.ch/blog/2007/02/01.php on line 255