Convert username to display name using a script

[patstone]

HI,

Can anyone tell me the code I could use in a VBA script that will run in Excel that would convert the username to the display name.

Are usernames are our staff numbers(i.e 12345) and I have a spreadsheet with thousands of usernames in colum A, I would like a script to run to read AD and enter the display name(i.e Joe Bloggs) in colum B

If anyone has any ideas can they please post

Thanks

 

[kmcferrin]

Does it have to run within Excel? If you can just pull the column with the username and put it in a simple text file, you could have a script that parses that, runs an AD query for user account, returns the display name, and puts it into a CSV alongside the username.

I suppose it's possible to do it from within Excel using VBA, but I don't know how to tell you how to do it.

 

[patstone]

Yeah it needs to be run from Excel because I will be handing the spreadsheet over to a Helpdesk and they will be running this query every week.

 

[markdmac]

Easy. This code assumes you already have the Excel spreadsheet open and that it is the only spreadsheet open.

on error resume next

set x = getobject(,"excel.application")
r = 2
do until len(x.cells(r, 1).value) = 0
	UserName = x.cells(r, 1).value
	DisplayName = SearchDisplayName(UserName)
	x.cells(r, 2).value = DisplayName
	r = r + 1
Loop

MsgBox "Done"

Public Function SearchDisplayName(ByVal vSAN)
    Dim oRootDSE, oConnection, oCommand, oRecordSet

    Set oRootDSE = GetObject("LDAP://rootDSE")
    Set oConnection = CreateObject("ADODB.Connection")
    oConnection.Open "Provider=ADsDSOObject;"
    Set oCommand = CreateObject("ADODB.Command")
    oCommand.ActiveConnection = oConnection
    oCommand.CommandText = "<LDAP://" & oRootDSE.get("defaultNamingContext") & _
        ">;(&(objectCategory=User)(samAccountName=" & vSAN & "));displayName;subtree"
    Set oRecordSet = oCommand.Execute
    On Error Resume Next    
    SearchDisplayName = oRecordSet.Fields("displayName")
    On Error GoTo 0
    oConnection.Close
    Set oRecordSet = Nothing
    Set oCommand = Nothing
    Set oConnection = Nothing
    Set oRootDSE = Nothing
End Function

I hope you find this post helpful.

Regards,

Mark

Check out my scripting solutions at

http://www.thespidersparlor.com/vbscript

 

[patstone]

Fair play, will test now ...

Thanks Mark

 

[markdmac]

Any problems Pat?

I hope you find this post helpful.

Regards,

Mark

Check out my scripting solutions at

http://www.thespidersparlor.com/vbscript