Question (Quick and simple I hope)

[KRKComputers]

I was wondering if someone can tell me if Excel has a way to pull a user name from AD using a text box input and an MSGBOX output and if so what the code may be to attempt this?

I have attempted it but each time I try to do so I fail and would greatly appreciate if anyone may know the code for this.

Thanks
Kevin

 

[wideboydixon]

Hi Kevin,

Still on watcher on this thread

If it fails on that line of code, I'm not sure how your "working" code could ever work because it has the same line of code which should generate the same failure.

WBD

 

[KRKComputers]

Wideboy,

I am in total agreement hence the reason I am stumped as well. It is truly odd that it works "only" when I run the information into excel as in my last code. However when I try to run it any other way I get failures.

I wish I knew the answer to it.

As you can see I have tried to troubleshoot the heck out of it and it does not matter how I try to run the code it still fails once it is changed to anything other then that to which I posted.

Go figure.

Regards
Kevin

Hi Kevin,

Still on watcher on this thread

If it fails on that line of code, I'm not sure how your "working" code could ever work because it has the same line of code which should generate the same failure.

WBD

 

[KRKComputers]

Something to add that I have just found that is truly odd and maybe someone has some input as to why I am getting this result.

When running VBS and I get to this part in the code "SET objRoot = GETOBJECT("LDAP://RootDSE")" all is good and the code will continue to run however when I get to this part "Set oRoot = GetObject("ldap://RootDSE")" in VBA the code fails.

My question:
Is this not the exact same function in different format one is VBA and one is VBS but they both do the exact same thing so why would it fail in one instance and work in another?

and it is on the same domain as well as the same computer and no variables have changed.

Regards
Kevin

 

[RoryA]

It appears to start to fail at "Set oRoot = GetObject("ldap://rootDSE")"

What exactly does this mean? Does it error there, or something else?

Edit: just noticed - that should be:

Set oRoot = GetObject("LDAP://rootDSE")

The provider name (LDAP here) is case-sensitive for ADSI.

 

[wideboydixon]

Can you try running Excel as an administrator? Right click the application shortcut and select "Run as Administrator" (possibly under More ...)

WBD

 

[KRKComputers]

Rory,

You hit the nail on the head!!!!!

Thanks to all the issue is now resolved and all is good and operational.

I failed to even see that myself until you had pointed it out but sometimes as we know it takes a fresh set of eyes to look at things from a whole new perspective.

Thanks for pointing that out and thanks to Ed for sticking with me on this and trying to get through it. All you guys have been very helpful in trying to get this resolved.

Best Regards
Kevin

What exactly does this mean? Does it error there, or something else?

Edit: just noticed - that should be:

Set oRoot = GetObject("LDAP://rootDSE")

The provider name (LDAP here) is case-sensitive for ADSI.

 

[KRKComputers]

One last thing to add to this and I hope it will be simple and not make life difficult for anyone.

I want to add the fields of information to pull from AD to the following code that ED helped me with. How and where do I add this information I know I can add items I need to the code if someone can just point me in the right direction by adding a couple of lines it would be greatly appreciated.

Public Sub LookupUser()


Dim cn As String
cn = InputBox("Enter the common name", "AD Lookup")


MsgBox GetAdsProp("cn", cn, "mail"), vbOKOnly + vbInformation, "Email"


End Sub




Function GetAdsProp(ByVal SearchField As String, ByVal SearchString As String, ByVal ReturnField As String) As String
    ' get domain
    Dim oRoot
    Set oRoot = GetObject("LDAP://RootDSE")
    Dim sDomain
    sDomain = oRoot.Get("defaultNamingContext")
    Dim strLDAP
    strLDAP = "LDAP://" & sDomain
    
    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") = 10000
    objCommand.Properties("Searchscope") = ADS_SCOPE_SUBTREE
        
    ' Search the AD recursively, starting at root of the domain
    objCommand.CommandText = "SELECT " & ReturnField & " FROM '" & strLDAP & "' WHERE objectCategory='person'AND objectClass='user' and " & SearchField & "='" & SearchString & "'"
    
    ' RecordSet
    Dim objRecordSet As Object
    Set objRecordSet = objCommand.Execute
         
    
    If objRecordSet.RecordCount = 0 Then
        GetAdsProp = "not found"  ' no records returned
    Else
        GetAdsProp = objRecordSet.Fields(ReturnField)  ' return value
    End If
     
    ' Close connection
    objConnection.Close
    
    ' Cleanup
    Set objRecordSet = Nothing
    Set objCommand = Nothing
    Set objCommand = Nothing
End Function

 

[RoryA]

You can repeat the call to GetAdsProp and change "mail" to whatever field it is you want each time. Alternatively, you could alter the SELECT statement to retrieve all the relevant fields and skip passing the field name as an argument. In that case, you'd either want to return say an array of values, or a delimited string that you can parse afterwards.

 

[KRKComputers]

Rory,

Can you give me an example so I know what I am looking for and at!

for example I want to get the following oUser.GivenName/oUser.TelephoneNumber etc etc how would I go about this? I have an idea of the items I want as well as how to get them just not knowing where to place them to get the results I want!

Best Regards
Kevin

You can repeat the call to GetAdsProp and change "mail" to whatever field it is you want each time. Alternatively, you could alter the SELECT statement to retrieve all the relevant fields and skip passing the field name as an argument. In that case, you'd either want to return say an array of values, or a delimited string that you can parse afterwards.