[alazarou]

How can i connect to Oracle via Excel Macro?
The code of the macro command will contain
1. connection with Oracle Database using username, password
2. Select query (e.g. Select field1, field2 from table1 where …)
3. (how) The results of the select query above will be placed in a cell range (e.g. A2:C1000)
The code that I have made behaves curiously and is this:

Sub mymacro()
Dim OraSession As Object
Dim OraDatabase As Object
Dim EmpDynaset As Object
Dim flds() As Object
Dim fldcount As Integer
Set OraSession = CreateObject("OracleInProcServer.XOraSession")
Set OraDatabase = OraSession.OpenDatabase("mydb", "username/password", 0&)

Set EmpDynaset = OraDatabase.CreateDynaset("SELECT ROWNUM,field1,field2 FROM table WHERE DATE>sysdate", 0&)

Range("A3:C2000").Select
Selection.ClearContents
'Declare and create an object for each column.
'This will reduce objects references and speed
'up your application.
fldcount = EmpDynaset.Fields.Count
ReDim flds(0 To fldcount - 1)
For Colnum = 0 To fldcount - 1
Set flds(Colnum) = EmpDynaset.Fields(Colnum)
Next
'Insert Column Headings
'For Colnum = 0 To EmpDynaset.Fields.Count - 1
'ActiveSheet.Cells(1, Colnum + 1) = flds(Colnum).Name
'Next
'Display Data
For Rownum = 2 To EmpDynaset.RecordCount + 1
For Colnum = 0 To fldcount - 1
ActiveSheet.Cells(Rownum, Colnum + 1) = flds(Colnum).Value
Next
EmpDynaset.MoveNext
Next
Range("A3:A3").Select
End Sub