Excel + Oracle

[volcano]

Hi all, I would like to write a Excel macro program to connect to Oracle
DB and retrieve data from it. I found from some websites that I should
contain a sentance shown below in the source code:

Set OraSession = CreateObject("OracleInProcServer.XOraSession&quot

However when I execute the program, it shows error 429 and said ActiveX
can't create object. Do I need to do anything so that I can continue my
programming?

Thanks

 

[xlbo]

probably need to set your references in VBE
Tools>References
look for any oracle references and tick them

Rgds, Geoff
[blue]Quantum materiae materietur marmota monax si marmota monax materiam possit materiari?[/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[sfvb]

If I remember right, OraSession is part of Oracle's OO4O (Oracle Objects for OLE) activeX object.

For most common Oracle queries through Excel, the following example should serve as starting point:

Sub RetrieveOracleStuff()
   Dim myQry         As String
   
   Sheets("Sheet1").Select
   Range("A1").Select
   
   myQry = "SELECT SUM(myColumn1), myColumn2, myColumn3 " & _
           "FROM myTable " & _
           "WHERE myColumn2 IN ('Blah', 'Blah2', 'Blah3') " & _
           "GROUP BY myColumn2, myColumn3 " & _
           "ORDER BY myColumn3"
    
   With ActiveSheet.QueryTables.Add(Connection:= _
      "ODBC;DRIVER={Microsoft ODBC for Oracle};", _
      Destination:=Range("A1"))
      .Sql = myQry
      .Name = "Query from Oracle"
      .FieldNames = False
      .RowNumbers = False
      .FillAdjacentFormulas = False
      .PreserveFormatting = True
      .RefreshOnFileOpen = False
      .BackgroundQuery = True
      .RefreshStyle = xlOverwriteCells
      .SavePassword = True
      .SaveData = True
      .AdjustColumnWidth = True
      .RefreshPeriod = 0
      .PreserveColumnInfo = True
      .Refresh BackgroundQuery:=False
   End With
End Sub