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

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 select * from dba_tables. I have kept things simple, everything is hard coded, nothing can be parametrized, not even the select statement, username, password or database.
In order to embed the macro in your Excel file and execute it, proceed like so:
  1. Open a new workbook.
  2. Press Alt+F11 to run the Visual Basic Editor.
  3. On the Insert menu, click Module.
  4. Paste the code below into the module sheet.
  5. On the File menu, click Close and return to Microsoft Excel.
  6. Select Sheet1.
  7. On the Tools menu, point to Macro, and then click Macros.
  8. In the Macro dialog box, click DisplayDBATables, and then click Run. 
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: sqlplus rene/mysecret@XE.

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:
  1. Open the Visual Basic editor (Alt+F11)
  2. From the extras menu, click References
  3. From the list, select Microsoft ActiveX Data Objects 2.x Library. (x being a number)
  4. Click OK

Links

See also On importing data from Oracle into Excel with CopyFromRecordset.

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/01/24.php on line 314

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/01/24.php on line 314

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/01/24.php on line 314