EXCEL. -use vba to collect user information from AD 2

[erichfosse]

Hi guys,

I've got a vba-app. that fills out a form in excel, and I need something that enables me to write a username in a text box and get the users first name, surname, phone number, and e-mail address outputed to lables or text boxes in the form.

Hope someone can help me

 

[xlbo]

I use summat like this:

Sub GetUsersFromAD()
  
  On Error Resume Next

  Const ADS_SCOPE_SUBTREE = 2

  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") = 1000
  objCommand.Properties("Searchscope") = ADS_SCOPE_SUBTREE

  objCommand.CommandText = _
      "SELECT Department, Title, sAMAccountName FROM 'LDAP://dc=corphq,dc=co,dc=uk' WHERE objectCategory='user' "
        '"AND Department !=''"
  Set objRecordset = objCommand.Execute
    
    With sht_AD
        .[A1] = "UserID"
        .[B1] = "Role"
        .[C1] = "Department"
        .Range("a2").CopyFromRecordset objRecordset
    End With
End Sub

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[erichfosse]

Hi Geoff,
Thank you for your reply!

My code looks like this:

Private Sub txtusernamen_Change()

On Error Resume Next

  Const ADS_SCOPE_SUBTREE = 2

  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") = 1000
  objCommand.Properties("Searchscope") = ADS_SCOPE_SUBTREE

  objCommand.CommandText = _
      "SELECT Department, Title, sAMAccountName FROM 'LDAP://dc=man,dc=ddd,dc=intern' WHERE objectCategory='user' "
        '"AND Department !=''"
  Set objRecordset = objCommand.Execute
    
    lblname = givenName
    lblsname = sn
    lblemail = mail
    lbltel = telephoneNumber
    


End Sub

But my lables just apear blank...

Are you able to see what I'm doing wrong?

Thank you!

 

[xlbo]

You havn;t changed the query to reference the fields you want to return and what is:

lblname = givenName
lblsname = sn
lblemail = mail
lbltel = telephoneNumber

meant to do? you havn't defined any of these variables and you havn't put them on the sheet:

In my code:
.[A1] = "UserID"

Enters "User ID" into Range A1 - you need to enter your headers onto the sheet to be able to see them...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[erichfosse]

lblname = givenName
lblsname = sn
lblemail = mail
lbltel = telephoneNumber

These are all lables in a user form. I would like the user form to query AD about the user name entered in a text box, then output the results to the lables..

Thank you

 

[xlbo]

Is this for ALL users or a specified one? if specified you need to add that to the where clause

You also need to access the recordset to get the data out of it:

lblname.caption = objrecordset.fields(1).value

or similar

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[erichfosse]

It is for all users..

 

[xlbo]

Well that kinda contradicts what you asked for in the 1st place...

enables me to write a username in a text box and get the users first name, surname, phone number, and e-mail address

You need to use the entry in your textbox as criteria in the query - you don't need to return everyone's details all at once. If you do, you would be better off writing the data to a worksheet and then reading it in from there

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[erichfosse]

Oh, sorry.
I just misunderstood your question..

So the whole clause looks like this:

 "SELECT givenName, sn, mail, telephoneNumber FROM 'LDAP://dc=man,dc=ddd,dc=intern' WHERE objectCategory='user' "

What do I add where?

 

[SkipVought]

oneeon,

Here you have wasted a whole day for yourself and for geoff, because you did not ask a precise question.

You have to understand that the members here at Tek-Tips that attempt to answer your questions, have absolutely no idea in the world what is in your head regarding an issue, unless you reveal it, clearly, concisely and completely.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[erichfosse]

I understand. And I am sorry.
I'll remember that for my next thread.


So here's the problem:

I've got a user form with a text box (txtusername) and four lables (lblname, lblsname, lblemail, lbltel).

I would like to be able to write a username in the text box, and get the user data for that user in the lables.



-better?



thank you for all your help

 

[xlbo]

SELECT givenName, sn, mail, telephoneNumber FROM 'LDAP://dc=man,dc=ddd,dc=intern' WHERE objectCategory='user' AND sAMAccountName = '" & Textboxname.Value & "'"

should work as your query

then to put values in your labels

lblname.caption = fields("givenName").value



Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[erichfosse]

Geoff, you're the greatest!

Thank you for all your help!

and sorry for the waste of your time..



-oneeon

 

[tsuji]

I think these changes are necessary.

>objectCategory='user'
[tt]objectCategory='person' and objectClass='user'[/tt]

>Department !=''
[tt]Department<>''[/tt]

 

[erichfosse]

Hi tsuji.

I haven't tested your proposition, but Geoff's solution also works

oneeon

 

[SkipVought]

oneeon,

Don't forget to...
[blue]
Thank Tek-Tip Contributor
for this valuable post!
[/blue]
with a little purple star if any of these posts were valuable to you.



Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[xlbo]

tsuji - I have found when querying AD before that <> is not accepted but that != is...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[tsuji]

Thanks, Geoff, for the note. I've never heard of that combination. This is the sql dialect grammar for comparison.
[tt]
comparison-operator ::= < | > | <= | >= | = | <>
ref

http://msdn.microsoft.com/en-us/library/aa746494(VS.85).aspx

[/tt]
I'll look into it a bit some other time. (Make sure on error resume next does not play a negative factor on this.)

 

[xlbo]

To be honest, I hadn't before I started playing around with AD...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[PHV]

A good tool to play with AD queries:

http://www.microsoft.com/technet/scriptcenter/tools/admatic.mspx

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886