January 24, 2007: On importing data from Oracle into Excel with a Visual Basic for Application macro

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