excel macro help!! [Archive]

alazarou

07-01-03, 02:49

I want to make a macro command on Excel which will be connected with oracle db.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

thanks
@lazarou

nedcase

07-28-03, 05:49

Don't understand what the problem is here - what goes wrong? It's probably a navigation problem. Does Excel recognise you have a full dataset? Get out of it by dumping the whole dynaset. I'm guessing here as I don't know what your problem is.

Must admit I'm wedded to DAO, and would use something like:

dim DB as database
dim RS as recordset
dim intCol as integer

set DB=opendatabase.....
set RS=db.openrecordset("SELECT * FROM TABLE", dbopensnapshot...)
rs.movelast
rs.movefirst
[not really necessary in this case but important to get an accurate recordcount. With your method could use 'do until RS.EOF' to move through the file rather than recordcount.]

Worksheets("Dump").range("a2").copyfromrecordset RS 'to dump the data

for intCol=1 to Worksheets("Dump").range("a2").end(xltoright).column
Worksheets("Dump").cells(1,intcol).value=rs.fields(intcol-1).name 'to dump the headers
next