itipu
04-11-2007, 04:57 AM
Hi, all I have some VBS code which used to write to a file... like this:
So it looks computers in Active Directory (AD), and looks up the same computer in "2nd DataBase", if computer name is not found in writes "Not in 2nd DataBase" else it outputs a couple of corresponding values from the 2nd DataBase.... So far so simple...
'Option Explicit
'Open Connection to AC shadow databse
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/vendaface", 0)
'Create text file
Set fso = CreateObject("Scripting.FileSystemObject")
Set f1 = fso.CreateTextFile("c:\lnxStatus.csv", True)
'Open a connection to AD
Set objConnection = CreateObject("ADODB.Connection")
objConnection.Open "Provider=ADsDSOObject;"
Set ADOconnObj = CreateObject("ADODB.Command")
ADOconnObj.ActiveConnection = objConnection
ADOconnObj.CommandText = "<GC://dc=XXX, dc=XXX, dc=XXX>;(objectclass=Computer);cn;subtree"
adoConnObj.Properties("Page Size")=500
Set RSObj = ADOconnObj.Execute
rsobj.movefirst
While not rsobj.eof
'
'This is the bit that does the lookup into 2nd DataBase
Set LogDynaset = OraDatabase.DbCreateDynaset("SELECT Assignment_Code, " _
&"assignment_Date, " _
&"assignment " _
&"from EPSS_Asset_Status " _
&"where AssetTag = upper('" & rsobj(0).Value & "')" , 0)
'
If logDynaset.EOF and logDynaset.BOF Then
' No record found in 2nd DataBase
f1.writeLine(rsobj(0).value & ", Not in 2nd DataBase")
Else
'Is in 2nd DataBase so report status
f1.writeLine(rsobj(0).value & ", " & logdynaset(0).value & ", " & logdynaset(1).value & ", " & logdynaset(2).value)
End If
rsobj.moveNext
Wend
f1.close
WScript.Echo "All Done.:"
The output looks like this in 4 columns
CBJ-S-03019 0 30/09/2005 In Use
AMSDC1-S-03010 0 12/08/2005 In Use
LUT-N-D00015 0 10/09/2005 In Use
phc-n-d00100 Not in AssetCenter
MUS-N-D99951 Not in AssetCenter
RIJKES-N-D99865 0 18/08/2005 In Use
kseu1279 2 21/02/2004 Retired (or Consumed)
No I am re-writing this into VBA like this:
Private Sub CommandButton9_Click()
Sheets.Add After:=Sheets(Sheets.count)
Set objExcel = ActiveSheet
ActiveSheet.Name = "AC " & Format(Date, "DD.MM.YYYY") & " at " & Format(Time, "hh.mm")
Cells(1, 1).Value = "AD Asset"
Cells(1, 1).Font.Bold = True
Cells(1, 2).Value = "Status1"
Cells(1, 2).Font.Bold = True
Cells(1, 3).Value = "Status2"
Cells(1, 3).Font.Bold = True
Cells(1, 4).Value = "Status3"
Cells(1, 4).Font.Bold = True
frmExtract.Hide
On Error Resume Next
'Open a connection to AD
Set objConnection = CreateObject("ADODB.Connection")
objConnection.Open "Provider=ADsDSOObject;"
Set ADOconnObj = CreateObject("ADODB.Command")
ADOconnObj.ActiveConnection = objConnection
ADOconnObj.CommandText = "<GC://dc=linux, dc=Shell, dc=com>;(objectclass=Computer);cn;subtree"
ADOconnObj.Properties("Page Size") = 500
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/vendaface", 0)
Set rsobj = ADOconnObj.Execute
rsobj.moveFirst
While Not rsobj.EOF
'This is the bit that does the lookup into AssetCenter
Set logdynaset = OraDatabase.DbCreateDynaset("SELECT Assignment_Code, " & "assignment_Date, " & "assignment " & "from EPSS_Asset_Status " & "where AssetTag = upper('" & rsobj(0).Value & "')", 0)
'DIFFICULTIES
'If logdynaset.EOF And logdynaset.BOF Then
'objExcel.Cells(rsobj() + 1, 1) = rsobj.fields(0).Value
'objExcel.Cells(rsobj() + 1, 2) = "Not in AssetCenter"
'rsobj.moveNext
'Else
For i = 1 To rsobj.RecordCount And logdynaset.RecordCount
objExcel.Cells(i + 1, 1) = rsobj.fields(0).Value
objExcel.Cells(i + 1, 2) = logdynaset.fields(0).Value
objExcel.Cells(i + 1, 3) = logdynaset.fields(1).Value
objExcel.Cells(i + 1, 4) = logdynaset.fields(2).Value
'End If
rsobj.moveNext
Next i
Wend
End Sub
But I am having difficulties with Loops, as I cant seem to be able to figure out how to write it to objExcel.Cells(X, X) format!
Your help is as always very much appreciated!!!!
Thx
Mike
So it looks computers in Active Directory (AD), and looks up the same computer in "2nd DataBase", if computer name is not found in writes "Not in 2nd DataBase" else it outputs a couple of corresponding values from the 2nd DataBase.... So far so simple...
'Option Explicit
'Open Connection to AC shadow databse
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/vendaface", 0)
'Create text file
Set fso = CreateObject("Scripting.FileSystemObject")
Set f1 = fso.CreateTextFile("c:\lnxStatus.csv", True)
'Open a connection to AD
Set objConnection = CreateObject("ADODB.Connection")
objConnection.Open "Provider=ADsDSOObject;"
Set ADOconnObj = CreateObject("ADODB.Command")
ADOconnObj.ActiveConnection = objConnection
ADOconnObj.CommandText = "<GC://dc=XXX, dc=XXX, dc=XXX>;(objectclass=Computer);cn;subtree"
adoConnObj.Properties("Page Size")=500
Set RSObj = ADOconnObj.Execute
rsobj.movefirst
While not rsobj.eof
'
'This is the bit that does the lookup into 2nd DataBase
Set LogDynaset = OraDatabase.DbCreateDynaset("SELECT Assignment_Code, " _
&"assignment_Date, " _
&"assignment " _
&"from EPSS_Asset_Status " _
&"where AssetTag = upper('" & rsobj(0).Value & "')" , 0)
'
If logDynaset.EOF and logDynaset.BOF Then
' No record found in 2nd DataBase
f1.writeLine(rsobj(0).value & ", Not in 2nd DataBase")
Else
'Is in 2nd DataBase so report status
f1.writeLine(rsobj(0).value & ", " & logdynaset(0).value & ", " & logdynaset(1).value & ", " & logdynaset(2).value)
End If
rsobj.moveNext
Wend
f1.close
WScript.Echo "All Done.:"
The output looks like this in 4 columns
CBJ-S-03019 0 30/09/2005 In Use
AMSDC1-S-03010 0 12/08/2005 In Use
LUT-N-D00015 0 10/09/2005 In Use
phc-n-d00100 Not in AssetCenter
MUS-N-D99951 Not in AssetCenter
RIJKES-N-D99865 0 18/08/2005 In Use
kseu1279 2 21/02/2004 Retired (or Consumed)
No I am re-writing this into VBA like this:
Private Sub CommandButton9_Click()
Sheets.Add After:=Sheets(Sheets.count)
Set objExcel = ActiveSheet
ActiveSheet.Name = "AC " & Format(Date, "DD.MM.YYYY") & " at " & Format(Time, "hh.mm")
Cells(1, 1).Value = "AD Asset"
Cells(1, 1).Font.Bold = True
Cells(1, 2).Value = "Status1"
Cells(1, 2).Font.Bold = True
Cells(1, 3).Value = "Status2"
Cells(1, 3).Font.Bold = True
Cells(1, 4).Value = "Status3"
Cells(1, 4).Font.Bold = True
frmExtract.Hide
On Error Resume Next
'Open a connection to AD
Set objConnection = CreateObject("ADODB.Connection")
objConnection.Open "Provider=ADsDSOObject;"
Set ADOconnObj = CreateObject("ADODB.Command")
ADOconnObj.ActiveConnection = objConnection
ADOconnObj.CommandText = "<GC://dc=linux, dc=Shell, dc=com>;(objectclass=Computer);cn;subtree"
ADOconnObj.Properties("Page Size") = 500
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/vendaface", 0)
Set rsobj = ADOconnObj.Execute
rsobj.moveFirst
While Not rsobj.EOF
'This is the bit that does the lookup into AssetCenter
Set logdynaset = OraDatabase.DbCreateDynaset("SELECT Assignment_Code, " & "assignment_Date, " & "assignment " & "from EPSS_Asset_Status " & "where AssetTag = upper('" & rsobj(0).Value & "')", 0)
'DIFFICULTIES
'If logdynaset.EOF And logdynaset.BOF Then
'objExcel.Cells(rsobj() + 1, 1) = rsobj.fields(0).Value
'objExcel.Cells(rsobj() + 1, 2) = "Not in AssetCenter"
'rsobj.moveNext
'Else
For i = 1 To rsobj.RecordCount And logdynaset.RecordCount
objExcel.Cells(i + 1, 1) = rsobj.fields(0).Value
objExcel.Cells(i + 1, 2) = logdynaset.fields(0).Value
objExcel.Cells(i + 1, 3) = logdynaset.fields(1).Value
objExcel.Cells(i + 1, 4) = logdynaset.fields(2).Value
'End If
rsobj.moveNext
Next i
Wend
End Sub
But I am having difficulties with Loops, as I cant seem to be able to figure out how to write it to objExcel.Cells(X, X) format!
Your help is as always very much appreciated!!!!
Thx
Mike