Solved: Re-writing my VBS into VBA Excel... minor bug

[itipu]

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

[mvidas]

Hi Mike,

At quick glance, the problem seems to be your line:For i = 1 To rsobj.RecordCount And logdynaset.RecordCountBy using And like that, you're really comparing the binary values of the two record counts, and returning which binary flags they have in common. Based on the logic of your original VBS, I re-wrote the end of your sub (and noted the changes), which assumes logdynaset will only return one record: i = 2 '**** ADDED
While Not rsobj.EOF
Set logdynaset = OraDatabase.DbCreateDynaset("SELECT Assignment_Code, " & _
"assignment_Date, " & "assignment " & "from EPSS_Asset_Status " & _
"where AssetTag = upper('" & rsobj(0).Value & "')", 0)
' For i = 1 To rsobj.RecordCount And logdynaset.RecordCount '**** REMOVED
Cells(i + 1, 1).Value = rsobj.Fields(0).Value
If logdynaset.EOF And logdynaset.BOF Then '**** ADDED
'no record found in 2nd database
'removed reference to 'objExcel' since you're running this from excel as it is
Cells(i + 1, 2).Value = "Not in 2nd DataBase" '**** ADDED
Else '**** ADDED
Cells(i + 1, 2).Value = logdynaset.Fields(0).Value
Cells(i + 1, 3).Value = logdynaset.Fields(1).Value
Cells(i + 1, 4).Value = logdynaset.Fields(2).Value
End If '**** ADDED
rsobj.MoveNext
i = i + 1
' Next i '**** REMOVED
WendGive it a try, see how it works for you.
Also, though really only cosmetic, I changed the beginning of your routine:' 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
Range("A1:D1").Value = Array("AD Asset", "Status1", "Status2", "Status3")
Range("A1:D1").Font.Bold = TrueJust showing you another way you could write that :)

Matt

[itipu]

Thanks a lot Matt, though it is not quite working ;( As usual it dumps nicely everything from AD so there are no problems with the rsobj... However Column B marks everything as "Not in 2nd DataBase" It does not seem to loop though the logdynaset

However logdynaset.... can return different things, look at Select statement.... it returns Assignment_Code, " & _
"assignment_Date, " & "assignment " so Column B, Column C & Column D....

I attached an excel sheet of what the VBS script above produces....

I also noticed that the original logic is no longer in the latest version...

The logic is, to dump a machine out of DataBase1(AD) put it into Column A, check this machine in DataBase2, if it does not exist put in Column B "Not Found", else in Column B put first item out of the logdynaset, Column C & Column D 2nd and 3rd items from the logdynaset. Move to the next machine from AD...

Thx