Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2003
    Posts
    20

    Wink Unanswered: excel macro help!!

    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
    Last edited by alazarou; 07-03-03 at 02:32.

  2. #2
    Join Date
    Jul 2003
    Location
    London UK
    Posts
    14
    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).colu mn
    Worksheets("Dump").cells(1,intcol).value=rs.fields (intcol-1).name 'to dump the headers
    next

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •