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
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