|René Nyffenegger's collection of things on the web|
René Nyffenegger on Oracle - Most wanted - Feedback
- Follow @renenyffenegger
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.