jcourtes
asked on
VBA (outlook)
Hi there,
Trying to make something to return the canonical name when given a username.
So far each msgbox keeps returning the username
here's what i got:
Set CNUsers = GetObject ("LDAP://OU=users,OU=city, DC=company ,DC=com")
CNUsers.Filter = Array("user")
For Each User in CNUsers
chkLoginname = User.sAMAccountName
If chkLoginname = HDUsername Then
msgbox "chkLoginname = " & chkLoginname
msgbox "HDUsername = " & HDUsername
DisplayName = User.displayName
msgbox "DisplayName = " & DisplayName
' at this point, User.displayName still returns their short username, ie: jdoe instead
' of John Doe
txfullname.text = DisplayName
Exit For
End If
Next
Please help!
Jamie
Trying to make something to return the canonical name when given a username.
So far each msgbox keeps returning the username
here's what i got:
Set CNUsers = GetObject ("LDAP://OU=users,OU=city,
CNUsers.Filter = Array("user")
For Each User in CNUsers
chkLoginname = User.sAMAccountName
If chkLoginname = HDUsername Then
msgbox "chkLoginname = " & chkLoginname
msgbox "HDUsername = " & HDUsername
DisplayName = User.displayName
msgbox "DisplayName = " & DisplayName
' at this point, User.displayName still returns their short username, ie: jdoe instead
' of John Doe
txfullname.text = DisplayName
Exit For
End If
Next
Please help!
Jamie
ASKER
I found my answer actually. here's what's working now:
Set CNUsers = GetObject ("LDAP://OU=Users,OU=Montr eal,DC=com pany,DC=co m")
CNUsers.Filter = Array("user")
For Each User in CNUsers
chkLoginname = User.sAMAccountName
If chkLoginname = HDUsername Then
txfullname.text = User.givenName & " " & User.sn
txtelephone.text = User.telephoneNumber
txlocation.text = User.l
Exit For
End If
Next
The only problem is that it's in form load and it makes it take 4-5 seconds to open as it searches AD.
Any recomendations on something that would be much quicker, or something that runs after load? i dunno... still kinda new at this.
Set CNUsers = GetObject ("LDAP://OU=Users,OU=Montr
CNUsers.Filter = Array("user")
For Each User in CNUsers
chkLoginname = User.sAMAccountName
If chkLoginname = HDUsername Then
txfullname.text = User.givenName & " " & User.sn
txtelephone.text = User.telephoneNumber
txlocation.text = User.l
Exit For
End If
Next
The only problem is that it's in form load and it makes it take 4-5 seconds to open as it searches AD.
Any recomendations on something that would be much quicker, or something that runs after load? i dunno... still kinda new at this.
What is it you're trying to accomplish?
ASKER
BluedevilFan,
I'm basically trying to make a helpdesk style form which has txt boxes with some of the information of the user.
their IP address, hostname, username, telephone number+location+fullname(a ctive directory)
I perhaps need a more direct way of getting the user properties than going through the list until it finds one...
(the last section)
'''''
'
''''''
Public ipaddarr()
Public nCounter
Function Item_Open()
Dim HDUsername 'Declare everything
Dim HDHostname
Dim WshNetwork
Dim Testvalue
Dim txhostname
Dim txusername
Dim txipaddress1
Dim transipaddress
Dim CNUsers
Dim pthUsername
Dim chkLoginname
Dim xfertest
Set WshNetwork = CreateObject("WScript.Netw ork") 'Object decs.
Set oPage = Item.GetInspector.Modified FormPages
Set txhostname = oPage("Message").Controls( "txhostnam e")
Set txusername = oPage("Message").Controls( "txusernam e")
Set txipaddress1 = oPage("Message").Controls( "txipaddre ss1")
Set txipaddress2 = oPage("Message").Controls( "txipaddre ss2")
Set txfullname = oPage("Message").Controls( "txfullnam e")
Set txtelephone = oPage("Message").Controls( "txtelepho ne")
Set txlocation = oPage("Message").Controls( "txlocatio n")
Set IPConfigSet = GetObject("winmgmts:").Exe cQuery("se lect IPAddress from Win32_NetworkAdapterConfig uration where IPEnabled=TRUE")
txhostname.text = ""
txusername.text = ""
txipaddress1.text = ""
txipaddress2.text = ""
txfullname.text = ""
txtelephone.text = ""
txlocation.text = ""
HDHostname = WshNetwork.ComputerName
HDUsername = WshNetwork.UserName
Set WshNetwork = Nothing
nCounter = 0
For Each IPConfig In IPConfigSet
If Not IsNull(IPConfig.IPAddress) Then
For I = LBound(IPConfig.IPAddress) To UBound(IPConfig.IPAddress)
nCounter = nCounter+1
Redim Preserve ipaddarr(nCounter)
ipaddarr(nCounter) = IPConfig.IPAddress(I)
Next
End If
Next
txusername.text = HDUsername 'Write to fields
txhostname.text = HDHostname
txipaddress1.text = ipaddarr(1)
txipaddress2.text = ipaddarr(2)
'
Set CNUsers = GetObject ("LDAP://OU=Users,OU=Montr eal,DC=com pany,DC=co m")
CNUsers.Filter = Array("user")
For Each User in CNUsers
chkLoginname = User.sAMAccountName
If chkLoginname = HDUsername Then
txfullname.text = User.givenName & " " & User.sn
txtelephone.text = User.telephoneNumber
txlocation.text = User.l
Exit For
End If
Next
'
End Function
I'm basically trying to make a helpdesk style form which has txt boxes with some of the information of the user.
their IP address, hostname, username, telephone number+location+fullname(a
I perhaps need a more direct way of getting the user properties than going through the list until it finds one...
(the last section)
'''''
'
''''''
Public ipaddarr()
Public nCounter
Function Item_Open()
Dim HDUsername 'Declare everything
Dim HDHostname
Dim WshNetwork
Dim Testvalue
Dim txhostname
Dim txusername
Dim txipaddress1
Dim transipaddress
Dim CNUsers
Dim pthUsername
Dim chkLoginname
Dim xfertest
Set WshNetwork = CreateObject("WScript.Netw
Set oPage = Item.GetInspector.Modified
Set txhostname = oPage("Message").Controls(
Set txusername = oPage("Message").Controls(
Set txipaddress1 = oPage("Message").Controls(
Set txipaddress2 = oPage("Message").Controls(
Set txfullname = oPage("Message").Controls(
Set txtelephone = oPage("Message").Controls(
Set txlocation = oPage("Message").Controls(
Set IPConfigSet = GetObject("winmgmts:").Exe
txhostname.text = ""
txusername.text = ""
txipaddress1.text = ""
txipaddress2.text = ""
txfullname.text = ""
txtelephone.text = ""
txlocation.text = ""
HDHostname = WshNetwork.ComputerName
HDUsername = WshNetwork.UserName
Set WshNetwork = Nothing
nCounter = 0
For Each IPConfig In IPConfigSet
If Not IsNull(IPConfig.IPAddress)
For I = LBound(IPConfig.IPAddress)
nCounter = nCounter+1
Redim Preserve ipaddarr(nCounter)
ipaddarr(nCounter) = IPConfig.IPAddress(I)
Next
End If
Next
txusername.text = HDUsername 'Write to fields
txhostname.text = HDHostname
txipaddress1.text = ipaddarr(1)
txipaddress2.text = ipaddarr(2)
'
Set CNUsers = GetObject ("LDAP://OU=Users,OU=Montr
CNUsers.Filter = Array("user")
For Each User in CNUsers
chkLoginname = User.sAMAccountName
If chkLoginname = HDUsername Then
txfullname.text = User.givenName & " " & User.sn
txtelephone.text = User.telephoneNumber
txlocation.text = User.l
Exit For
End If
Next
'
End Function
Nice idea. You can speed this up some by retrieving the desired user's account directly rather than having to read through all the AD accounts until you find the one you want. Are you familiar with reading AD via ADO?
ASKER
nope, i've undertook this project educating myself on using LDAP, I take it you suggest ADO ?
Yes, because it allows you to query AD using SQL syntax. Here's an example. This is a bit of VB Script, but it shows how this works. It allows you to treat AD like a database table, the lone exception being that this is read only. In this example we'd retrieve a single AD account, the one belonging to the username jdoe.
Set rsDetails = Wscript.CreateObject("ADOD B.Recordse t")
rsDetails.ActiveConnection = "Provider=ADSDSOObject"
'Replace DomainName on the following line with the name of your domain
rsDetails.Source = "SELECT ADsPath, displayName, Company, Department, Division FROM 'LDAP://DomainName' WHERE objectClass='user' AND objectCategory='Person' AND samAccountName="jdoe'"
rsDetails.CursorType = 0
rsDetails.CursorLocation = 2
rsDetails.LockType = 1
rsDetails.Open()
If Not rsDetails.EOF Then
With rsDetails
Wscript.Echo "Name=" & .Fields("displayName") & " Company=" & .Fields("Company") & " Department=" & .Fields("Department") & " Division=" & .Fields("Division")
End With
Wend
rsDetails.Close
Set rsDetails = Nothing
Set rsDetails = Wscript.CreateObject("ADOD
rsDetails.ActiveConnection
'Replace DomainName on the following line with the name of your domain
rsDetails.Source = "SELECT ADsPath, displayName, Company, Department, Division FROM 'LDAP://DomainName' WHERE objectClass='user' AND objectCategory='Person' AND samAccountName="jdoe'"
rsDetails.CursorType = 0
rsDetails.CursorLocation = 2
rsDetails.LockType = 1
rsDetails.Open()
If Not rsDetails.EOF Then
With rsDetails
Wscript.Echo "Name=" & .Fields("displayName") & " Company=" & .Fields("Company") & " Department=" & .Fields("Department") & " Division=" & .Fields("Division")
End With
Wend
rsDetails.Close
Set rsDetails = Nothing
ASKER
BlueDevilFan,
That code is much faster, I like! </borat>
I've incorporated it into my code, and it works.
I do have a problem with it tho
on the line:
rsDetails.Source = "SELECT ADsPath, displayName, Company, Department, Division FROM 'LDAP://DomainName' WHERE objectClass='user' AND objectCategory='Person' AND samAccountName='jdoe'"
I'm trying to provide it with the username from a variable (HDUsername).
I havent found the correct way to reference the variable so the samAccountName=HDUsername
Can you assist?
Thanks,
Jamie
That code is much faster, I like! </borat>
I've incorporated it into my code, and it works.
I do have a problem with it tho
on the line:
rsDetails.Source = "SELECT ADsPath, displayName, Company, Department, Division FROM 'LDAP://DomainName' WHERE objectClass='user' AND objectCategory='Person' AND samAccountName='jdoe'"
I'm trying to provide it with the username from a variable (HDUsername).
I havent found the correct way to reference the variable so the samAccountName=HDUsername
Can you assist?
Thanks,
Jamie
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
BlueDevilFan,
Thank you so much, you're really good!
Here is the end result. You'll notice the extra if's, it seems I needed to validate that the fields weren't empty before trying to write their values to my objects on the form. If I didn't, it would generate errors.
But it works great, practically instantaneous, and definately a valuable piece of code to keep near.
I have no SQL experience, but I can work around things. Great assistance on another one of my questions.
Thank you again,
Jamie
===============
Set rsDetails = CreateObject("ADODB.Record set")
rsDetails.ActiveConnection = "Provider=ADSDSOObject"
rsDetails.Source = "SELECT ADsPath, displayName, l, givenName, sn, telephoneNumber FROM 'LDAP://server' WHERE objectClass='user' AND objectCategory='Person' AND samAccountName='" & HDUsername & "'"
rsDetails.CursorType = 0
rsDetails.CursorLocation = 2
rsDetails.LockType = 1
rsDetails.Open()
If Not rsDetails.EOF Then
With rsDetails
txfullname.text = .Fields("givenName") & " " & .Fields("sn")
If Not IsNull(.Fields("telephoneN umber")) Then
txtelephone.text = .Fields("telephoneNumber")
End If
If Not IsNull(.Fields("l")) Then
txlocation.text = .Fields("l")
End If
End With
end if
rsDetails.Close
Set rsDetails = Nothing
Thank you so much, you're really good!
Here is the end result. You'll notice the extra if's, it seems I needed to validate that the fields weren't empty before trying to write their values to my objects on the form. If I didn't, it would generate errors.
But it works great, practically instantaneous, and definately a valuable piece of code to keep near.
I have no SQL experience, but I can work around things. Great assistance on another one of my questions.
Thank you again,
Jamie
===============
Set rsDetails = CreateObject("ADODB.Record
rsDetails.ActiveConnection
rsDetails.Source = "SELECT ADsPath, displayName, l, givenName, sn, telephoneNumber FROM 'LDAP://server' WHERE objectClass='user' AND objectCategory='Person' AND samAccountName='" & HDUsername & "'"
rsDetails.CursorType = 0
rsDetails.CursorLocation = 2
rsDetails.LockType = 1
rsDetails.Open()
If Not rsDetails.EOF Then
With rsDetails
txfullname.text = .Fields("givenName") & " " & .Fields("sn")
If Not IsNull(.Fields("telephoneN
txtelephone.text = .Fields("telephoneNumber")
End If
If Not IsNull(.Fields("l")) Then
txlocation.text = .Fields("l")
End If
End With
end if
rsDetails.Close
Set rsDetails = Nothing
You're welcome, Jamie.
"Thank you so much, you're really good! "
And when you're really good, they call you Cracker Jack!
Haha...hi 'neighbor' ;)
Nice work here...
Sincerely,
LongTimeFan
And when you're really good, they call you Cracker Jack!
Haha...hi 'neighbor' ;)
Nice work here...
Sincerely,
LongTimeFan
Thanks, SB!
To get the CanonicalName you'll need to retrieve the CanonicalName property. I'm not sure it's part of the property cache, so you may have to use GetEx. Sorry for not being sure, but prefer reading AD via ADO and not LDAP. I should also point out that CanonicalName may be returned as an array.
Cheers!