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

    Unanswered: How can i connect to Oracle via Excel Macro

    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

  2. #2
    Join Date
    Aug 2011
    Posts
    1

    Red face I am getting error using the above code.

    Quote Originally Posted by alazarou View Post
    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

    I am getting error that the Licencse Information for this component not found.
    Please tell what all do i need to do run the above code.

Posting Permissions

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