|René Nyffenegger's collection of things on the web|
René Nyffenegger on Oracle - Most wanted - Feedback
- Follow @renenyffenegger
January 24, 2007: On importing data from Oracle into Excel with a Visual Basic for Application macro
I have been playing with Excel and its Visual Basic for Application (VBA) macros recently. As much as I loathe the syntax of VBA, I do like what can be done with it. Especially, I found it quite easy to connect to an Oracle Database with ADODB, execute a select statement and import the result into an Excel worksheet. Let me share my joy with you...
The following macro does exactly this: it executes a
In order to embed the macro in your Excel file and execute it, proceed like so:
Sub DisplayDBATables() Dim cn As ADODB.Connection Dim rs As ADODB.Recordset Dim col As Integer Dim row As Integer Set cn = New ADODB.Connection Set rs = New ADODB.Recordset cn.Open ( _ "User ID=rene" & _ ";Password=mysecret" & _ ";Data Source=XE" & _ ";Provider=MSDAORA.1") rs.Open "select * from dba_tables", cn col = 0 ' First Row: names of columns Do While col < rs.Fields.Count Cells(1, col + 1) = rs.Fields(col).Name col = col + 1 Loop ' Now actual data as fetched from select statement row = 1 Do While Not rs.EOF row = row + 1 col = 0 Do While col < rs.Fields.Count Cells(row, col + 1) = rs.Fields(col).Value col = col + 1 Loop rs.MoveNext Loop End Sub
This macro assumes that my username is rene, my password mysecret and the net service name XE, that is, that I'd have to connect with SQL*Plus like so:
Update January 31
In order to prevent the ADODB.Connection user defined type not defined error message, a reference to the ActiveX Data Objects Library must be made:
More on Oracle
This is an on Oracle article. The most current articles of this series can be found here.