In Excel sollen Daten via SQL aus einer Oracledatenbank eingelesen werden.

Für dieses Codebeispiel muss das Oracle Objects for OLE (OO4O) installiert sein.

 

 

Public Sub Zugriff_Excel_Oracle()

'Oracle Objects for OLE(OO4O)ist installiert!

'Unter Extras -> Verweise ist "OracleInProcServer 5.0 TypeLibrary" eingebunden!

Dim oraSession As Object

Dim oraDatabase As Object

Dim oraDynaset As Object

Dim strSQL As String

Dim intS As Integer

Dim lngZ As Long

 

Set oraSession = CreateObject("OracleInProcServer.XOraSession")

Set oraDatabase = oraSession.OpenDatabase("XE", "system/system", 0&)

strSQL = "select * from hr.employees"

Set oraDynaset = oraDatabase.DBCreateDynaset(strSQL, 0)

lngZ = 1

 

oraDynaset.MoveFirst

 

'Überschriften eintragen

For intS = 0 To oraDynaset.Fields.Count - 1

    Cells(lngZ, intS + 1).Value = oraDynaset.Fields(intS).Name

Next

lngZ = 2

 

'Daten eintragen

Do Until oraDynaset.EOF = True

  For intS = 0 To oraDynaset.Fields.Count - 1

    Cells(lngZ, intS + 1).Value = oraDynaset.Fields(intS)

  Next

  oraDynaset.MoveNext

  lngZ = lngZ + 1

Loop

 

'Aufräumen

oraDynaset.Close

oraDatabase.Close

Set oraDynaset = Nothing

Set oraDatabase = Nothing

Set oraSession = Nothing

End Sub