Consulting

Results 1 to 2 of 2

Thread: My macros connects to Oracle and writes to file but no Excel

Threaded View

Previous Post Previous Post   Next Post Next Post
  1. #1
    VBAX Contributor
    Joined
    Feb 2007
    Posts
    126
    Location

    Unhappy My macros connects to Oracle and writes to file but no Excel

    I think this is something minor, so I would really appreciate your help as I can't figure it out...

    So I wrote this code:

    [vba] Set OraSession = CreateObject("OracleInProcServer.XOraSession")
    Set OraDatabase = OraSession.OpenDatabase("(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP) (Host=glsc39.europe.com)(Port=1521)))(CONNECT_DATA=(sID=SCRP)))", "SCREP_EP/SCREP_EP", 0)
    Set LogDynaset = OraDatabase.DbCreateDynaset("SELECT * from EPSS_Assets ", 0)
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set f1 = fso.CreateTextFile("c:\EPSS.txt", True)
    logDynaset.movefirst
    Do until LogDynaset.eof
    f1.WriteLine(LogDynaset(0).value)
    logDynaset.moveNext
    Loop
    f1.close[/vba]

    This works fine and creates a file as attached...

    I want to write it to an Excel sheet instead of a file so I wrote this:

    [vba]Sub test()
    Sheets.Add after:=Sheets(Sheets.Count)
    Set objExcel = ActiveSheet
    ActiveSheet.Name = "Computer " & Format(Date, "DD.MM.YYYY") & " at " & Format(Time, "hh.mm")

    Set OraSession = CreateObject("OracleInProcServer.XOraSession")
    Set OraDatabase = OraSession.OpenDatabase("(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP) (Host=glsc34.europe.shell.com)(Port=1521)))(CONNECT_DATA=(sID=SCRP)))", "SCREP_EP/SCREP_EP", 0)
    Set logDynaset = OraDatabase.DbCreateDynaset("SELECT * from EPSS_Assets ", 0)


    'Set fso = CreateObject("Scripting.FileSystemObject")
    'Set f1 = fso.CreateTextFile("c:\EPSS.txt", True)

    Set oraRecordSet = logDynaset

    oraRecordSet.MoveFirst
    Do Until oraRecordSet.EOF
    'Do Until logDynaset.EOF
    'f1.WriteLine (logDynaset(0).Value)
    objExcel.Cells(x, 1).Value = oraRecordSet.fields(0).Value
    oraRecordSet.moveNext
    'logDynaset.moveNext
    Loop
    End Sub[/vba]

    But this is not working, something to do with ordSet.fields(0).Value

    Please any tip is much much appreciated!

    Thanks a lot

    Mike
    Last edited by itipu; 03-14-2007 at 12:20 PM.

Posting Permissions

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