PDA

View Full Version : My macros connects to Oracle and writes to file but no Excel



itipu
03-14-2007, 12:09 PM
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:

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

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:

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

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

Please any tip is much much appreciated!

Thanks a lot

Mike

JimmyTheHand
03-14-2007, 03:16 PM
Hello Mike,

I can't test your code because of Oracle being involved, but I noticed something.
Unless X is a function that was not posted here, it is not defined prior to use. So
objExcel.Cells(x, 1).Valueis actually objExcel.Cells(0, 1).Value which in itself gives an error.

Jimmy