Solved: Dumping data from AD If statement issue

[itipu]

Dear All...

I am using the following piece of code to dump from AD (Active Directory):

'Activates connection to AD:
Set objConnection = CreateObject("ADODB.Connection")
Set objCommand = CreateObject("ADODB.Command")
objConnection.Provider = "ADsDSOObject"
objConnection.Open "Active Directory Provider"
Set objCommand.ActiveConnection = objConnection
objCommand.Properties("Page Size") = 100
objCommand.Properties("Searchscope") = ADS_SCOPE_SUBTREE


'Below "cn" means machine name, if you need to populate further AD attributes, you put as many as you need using "," note there is no comma after the last attribute:
objCommand.CommandText = "SELECT cn FROM " & "'LDAP://xxx.xxx.com/dc=xxx,dc=xxxl,dc=com' WHERE " & "objectCategory='computer'"
Set objRecordSet = objCommand.Execute

objRecordSet.moveFirst
x = 2
Do Until objRecordSet.EOF
miss = objRecordSet.Fields("cn").Value

If miss = "mike" Then
objRecordSet.MoveNext
Else

'Below are attributes that are being populate, remember to add these names to SELECT clause above:
objExcel.Cells(x, 1).Value = objRecordSet.Fields("cn").Value
x = x + 1
objRecordSet.MoveNext
End If
Loop

So basically I want to skip every CN="mike" but this is not working...

my variable miss is not being set to anything... How can I do this?

Thanks a lot

Mike

[mdmackillop]

Do you need a semi-colon at the end of your Select statement?

[itipu]

The part that does not work is:

miss = objRecordSet.Fields("cn").Value

If miss = "mike" Then
objRecordSet.MoveNext
Else

[Ebrow]

Hi.

I am not experianced at ADO but I would normally get a field value by using the ![field name]. Give it a shot, I might work.


miss = objRecordSet![cn].value


you may need to dim objRecordSet as recordset.

[mdmackillop]

I can use the latter part of your code in querying a simple database. I suspect that you are not getting any data in your recorset. Try incorporating the following to check
objRecordSet.moveFirst
Do Until objRecordSet.EOF
MsgBox objRecordSet.Fields("cn").Value
objRecordSet.MoveNext
Loop

[itipu]

Strange....

It has got to be possible somehow!

Br.....

[mdmackillop]

Here's the code I'm using in my Database test
Option Explicit
Sub Access_Data()
'Requires reference to Microsoft ActiveX Data Objects xx Library

Dim Cn As ADODB.Connection, Rs As ADODB.Recordset
Dim MyConn, sSQL As String

Dim Rw As Long, Col As Long, c As Long
Dim MyField, Location As Range, x, miss

'Set source
MyConn = "C:\AAA\db1.mdb"
'Create query
sSQL = "SELECT Table1.cn FROM Table1;"
'Create RecordSet
Set Cn = New ADODB.Connection
With Cn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Open MyConn
Set Rs = .Execute(sSQL)
End With

Rs.moveFirst
Do Until Rs.EOF
MsgBox Rs.Fields("cn").Value
Rs.MoveNext
Loop


x = 2
Do Until Rs.EOF
miss = Rs.Fields("cn").Value
If miss = "mike" Then
Rs.MoveNext
Else
'Below are attributes that are being populate, remember to add these names to SELECT clause above:
Cells(x, 1).Value = Rs.Fields("cn").Value
x = x + 1
Rs.MoveNext
End If
Loop
Set Cn = Nothing
End Sub

[itipu]

This works for me fine.. I get a Column A of about 5000 values... Non the less I still don't know how can I say that if the value = "1" I want this record to be skipped.... Very starnge this is...

'Activates connection to AD:
Set objConnection = CreateObject("ADODB.Connection")
Set objCommand = CreateObject("ADODB.Command")
objConnection.Provider = "ADsDSOObject"
objConnection.Open "Active Directory Provider"
Set objCommand.ActiveConnection = objConnection
objCommand.Properties("Page Size") = 100
objCommand.Properties("Searchscope") = ADS_SCOPE_SUBTREE


objCommand.CommandText = "SELECT cn FROM " & "'LDAP://xxx.xxx.com/dc=xxx,dc=xxxl,dc=com' WHERE " & "objectCategory='computer'"
Set objRecordSet = objCommand.Execute

objRecordSet.moveFirst
x = 2
Do Until objRecordSet.EOF

objExcel.Cells(x, 1).Value = objRecordSet.Fields("cn").Value
x = x + 1
objRecordSet.MoveNext
End If
Loop

[mdmackillop]

I can't see why this should be creating a problem.
Can you post a small sample of your column results so I can see the data type?

[itipu]

You are correct... it works with cn. I used cn as an example, while actually I am dumping a different custom attribute of my schema... I now simplified code.. and it appears that at stage:

mike = objRecordSet.Fields("shellLNXBillingItem").Value

If mike = "Sun-Desktop" Then

I get a Type Mismatch...the out is attached...

[mdmackillop]

I dimmed Miss as Variant and don't get mismatch problems with your output data.

[itipu]

Dim mike As Variant
'Activates connection to AD:
Set objConnection = CreateObject("ADODB.Connection")
Set objCommand = CreateObject("ADODB.Command")
objConnection.Provider = "ADsDSOObject"
objConnection.Open "Active Directory Provider"
Set objCommand.ActiveConnection = objConnection
objCommand.Properties("Page Size") = 100
objCommand.Properties("Searchscope") = ADS_SCOPE_SUBTREE
'Below "cn" means machine name, if you need to populate further AD attributes, you put as many as you need using "," note there is no comma after the last attribute:
objCommand.CommandText = "SELECT shellLNXBillingItem FROM " & "'LDAP://xxx.xxx.com/dc=xxx,dc=xxx,dc=com' WHERE " & "objectCategory='computer'"
Set objRecordSet = objCommand.Execute

objRecordSet.moveFirst
x = 2
Do Until objRecordSet.EOF
'adoLastLogon = objRecordSet.Fields("lastLogonTimestamp")
'On Error Resume Next
'Err.Clear
'Set longDate = adoLastLogon
'If Err.Number <> 0 Then
'Err.Clear
'logonDate = "No Local Logon"
'Else
'longDateHigh = longDate.HighPart
'longDateLow = longDate.LowPart
'If (longDateLow = 0) And (longDateHigh = 0) Then
'logonDate = "No Local Logon"
'Else
'If longDateLow < 0 Then longDateHigh = longDateHigh + 1
'logonDate = #1/1/1601# + (((longDateHigh * (2 ^ 32)) + longDateLow) / 600000000 / 1440)
'End If
'End If

mike = objRecordSet.Fields("shellLNXBillingItem").Value
If mike = "8010004795" Then
'Below are attributes that are being populate, remember to add these names to SELECT clause above:
'objExcel.Cells(x, 1).Value = "1100" & "13" & "99" & objRecordSet.Fields("shellGIDSoldToCode").Value & "2100" & "P340700060" & objRecordSet.Fields("shellGIDSalesMaterialCode").Value & "ZFO"
'objExcel.Cells(x, 2).Value = "006" & objRecordSet.Fields("cn").Value
'objExcel.Cells(x, 3).Value = "'" & Format(1#, "#.000")
'objExcel.Cells(x, 4).Value = logonDate
objExcel.Cells(x, 5).Value = objRecordSet.Fields("shellLNXBillingItem").Value
x = x + 1
objRecordSet.MoveNext
Else
objRecordSet.MoveNext
End If

Loop
'Sets AutoFilter on All Columns:
'With ActiveSheet
'.Range("A1:E1").AutoFilter
'End With
'Auto formats all columns:
'Cells(x, x).Select
'Cells.EntireColumn.AutoFit
ThisWorkbook.Save
End Sub

Type Mismatch at:

If mike = "8010004795" Then

Am I not usign quotes properly?

Thanks a lot again for all your help!

[mdmackillop]

I still don't see the problem. Does the code work if mike is a text string?
Try
mike = cstr(objRecordSet.Fields("shellLNXBillingItem").Value)

[itipu]

So.. no I do as follows:
If IsNull(objRecordSet.Fields("shellLNXBillingItem").Value) Then
objExcel.Cells(x, 3).Value = objRecordSet.Fields("cn").Value
x = x + 1
objRecordSet.MoveNext
Else
objExcel.Cells(x, 3).Value = objRecordSet.Fields("cn").Value
miss = objRecordSet.Fields("shellLNXBillingItem").Value
objExcel.Cells(x, 4).Value = miss
objExcel.Cells(x, 6).Value = CVar("8010004793")
If miss = CVar("8010004793") Then
objRecordSet.MoveNext
Else

Initial problem was with empy values, so now I am skipping those... However the main issue appears to be that when I just dump the value it looks like
objExcel.Cells(x, 6).Value = CVar("8010004793")

it looks like 8.01E+09 but if I expend the column it becomes 8010004793

Finally in the code above:

objExcel.Cells(x, 3).Value = objRecordSet.Fields("cn").Value

I get a machine name.

objExcel.Cells(x, 4).Value = miss

I get: Linux-Desktop

objExcel.Cells(x, 6).Value = CVar("8010004793")

I get: 8010004793 (after I expand the column.

And type mismatch when I try to compare Linux-Desktop to 8010004793...

I tried Cstr/Cvar/Cdbl all sorts of quotes etc... but with no luck at all!

Thanks a lot

Mike

[itipu]

So I had to treat it as an array and take out first item of the array!


Thanks a lot!!!