Arnim Nay EDV-Beratung

EXCEL

Top

MyTables

Im VBA-Projekt wird ein neues Modul (modDb) angelegt. Die Namen aller Tabellen eines Users werden auf dem aktiven Blatt angezeigt. Der Aufruf der Prozedur "GetMyTables" kann z.B. auf einen Button gelegt werden.

Option Private Module
Public gOraSession As Object
Public gOraDatabase As Object
Public gOraDynaset As Object

Sub init_session()
On Error GoTo init_session_err
Set modDb.gOraSession = CreateObject("OracleInProcServer.XOraSession")
Set modDb.gOraDatabase = modDb.gOraSession.OpenDatabase("ORCL", "SCOTT/TIGER", 0&)
Exit Sub
init_session_err:
MsgBox "Error creating session " & Err.Description
End Sub

Sub GetMyTables()
Dim l_stat As String
Dim l_fields_cnt As Integer, l_row As Integer

init_session
l_row = 0
l_stat = "SELECT table_name FROM user_tables order by 1"
Set gOraDynaset = gOraDatabase.DbCreateDynaset(l_stat, 0&)
l_fields_cnt = gOraDynaset.fields.Count - 1
Do Until gOraDynaset.EOF
    l_row = l_row + 1
    For j = 0 To l_fields_cnt
      ActiveSheet.Cells(l_row, j + 1) = gOraDynaset.fields(j).Value
    Next j
    gOraDynaset.movenext
Loop
Set gOraDynaset = Nothing
Set gOraSession = Nothing
Set gOraDatabase = Nothing
End Sub

Version 1.0
04.09.2012