If No Record Found

[jjpski]

Hey,

I have a problem with the following code. It does almost everything I need except when a user account is not found it puts nothing in column A. I tried isEmpty IsNull but maybe I am not putting them in the right place. Any help is always appreciated.


With ActiveSheet
Lastrow = .Cells(.Rows.Count, "G").End(xlUp).Row
End With


Set rootDSE = GetObject("LDAP://RootDSE")
Base = "<LDAP://" & rootDSE.Get("defaultNamingContext") & ">"

Set conn = CreateObject("ADODB.Connection")
conn.Provider = "ADsDSOObject"
conn.Open "Active Directory Provider"

Set cmd = CreateObject("ADODB.Command")
Set cmd.ActiveConnection = conn

'add other attributes according to your requirements
attr = "distinguishedName,sAMAccountName"
scope = "subtree"

For R_counter = 2 To Lastrow
UserName = Trim(Range("G" & R_counter).Value) & Trim(Range("H" & R_counter).Value)

'filter on user objects with the given account name
fltr = "(&(objectClass=user)(objectCategory=Person)" & _
"(sAMAccountName=" & UserName & "*))"

cmd.CommandText = Base & ";" & fltr & ";" & attr & ";" & scope
Set rs = cmd.Execute
Do Until rs.EOF

Range("A" & R_counter).Value = rs.Fields("distinguishedName").Value

rs.MoveNext
Loop
rs.Close
Next R_counter
conn.Close


v/r
Jared

 

[Norie]

Jared

I think you might need to check rs.EOF, if that is True then no records have been returned to the recordset and the code in this loop will never be executed.

Do Until rs.EOF
    Range("A" & R_counter).Value = rs.Fields("distinguishedName").Value

    rs.MoveNext
Loop