?
Solved

Vb script to update AD Users tab from excel

Posted on 2010-01-12
16
Medium Priority
?
912 Views
Last Modified: 2012-06-21
Hi Experts,

I'm looking for a vbs that will allow me to change many fields in active directory base on a Excel or csv file.
I will need to change the description & title & department & manager ( if possible )


So far i'm doing this process manually  twice a year and its very long.

The excel looks like the following

A                            B                       C                                 D
Full Name               Title                   Department                Manager


My users are in different OU, and if the user is not found i would like to continu the update process for other users

I google a lot saw similar examples but i d'ont really get it, does anything can give me help ?

Its very appreciate !
thanks you
0
Comment
Question by:brular01
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
  • 2
  • +1
16 Comments
 
LVL 15

Expert Comment

by:Colosseo
ID: 26301507
Can your excel file not also include the users user id?

I can write some vbs to do it but having the users user id would help a lot.
0
 
LVL 27

Expert Comment

by:bluntTony
ID: 26301576
Hi there,

Below is a simple example of a script to do this. As colosseo says, you need a link to the object in AD. The most user firendly link will be the user login name (i.e. the 'username' bit from DOMAIN\username). If you can replace 'Full Name' with this, then the below will do it.

Change strCSV to the path to your CSV, and strDomain to the NetBIOS name of your domain (i.e. the 'DOMAIN' in DOMAIN\username).

Save the sheet as a CSV (the header order must be LOGIN,TITLE,DEPARTMENT,MANAGER) and run the script against it. As always test this before running out on production.

Give me a shout if you need this modifying.

Tony

Const ForReading = 1

strCSV = "C:\mycsv.csv"
strDomain = "DOMAIN"

Set objFso = CreateObject("Scripting.FileSystemObject")
Set objCSV = objFSO.OpenTextFile(strCSV,ForReading)


'USE THIS NEXT LINE IF THE CSV INCLUDES HEADERS!!!
objCsv.SkipLine 

Do Until objCsv.AtEndOfStream
	arrData = Split(objCsv.ReadLine,",")
	'Bind to user
	WScript.Echo arrData(0)
	Set objUser = GetObject("LDAP://" & getUserByNTName(strDomain & "\" & arrData(0)))
	
	'Set attributes
	objUser.Put "title", arrData(1)
	objUser.Put "department", arrData(2)
	objUser.Put "manager", getUserByNTName(strDomain & "\" & arrData(3))
	objUser.SetInfo
Loop



Function getUserByNTName(strNTName)
 Const ADS_NAME_INITTYPE_GC = 3
 Const ADS_NAME_TYPE_NT4 = 3
 Const ADS_NAME_TYPE_1779 = 1
 Set objNT = CreateObject("NameTranslate")
 objNT.Init ADS_NAME_INITTYPE_GC, ""
 objNT.Set ADS_NAME_TYPE_NT4, strNTName
 getUserByNTName = Replace(objNT.Get(ADS_NAME_TYPE_1779),"/","\/")
End Function

Open in new window

0
 
LVL 27

Expert Comment

by:bluntTony
ID: 26301579
Oh, this is VBScript by the way. Save it as VBS file and run from the command line via cscript, e.g.

cscript updateUsers.vbs
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 17

Expert Comment

by:Premkumar Yogeswaran
ID: 26301747
Hi BluntTony,
Please can you give a script to change the EMP ID with Title, Department and Manger as per the same process above.

Thanking you in advance
0
 
LVL 27

Expert Comment

by:bluntTony
ID: 26301879
Hi premglitz,

Is this request for you personally? I see no mention of employee ID in the original question?

If you want to add attributes to change, add an extra column to the end (so in your case they will be LOGIN,TITLE,DEPARTMENT,MANAGER,EMPLOYEE ID), then this data will be the next element in the array 'arrData', so in this case 'arrData(4)'. So in the section where I am writing the values (commented 'Set Attributes'), add the line...

objUser.Put "employeeID", arrData(4)

This should do it. I'd rather not go into too much detail as this might confuse matters for the asker of the original question.

Tony
0
 
LVL 17

Expert Comment

by:Premkumar Yogeswaran
ID: 26302543
Thanks BluntTony
0
 

Author Comment

by:brular01
ID: 26305606
Wow you guys are fast, I will try it today and let you know.

thanks
0
 

Author Comment

by:brular01
ID: 26306125
Hello,

I try on my dev domain with domain admin rights and i'm always getting

34,2 <null> Name Translation: Could not find the name or insufficient right to see name.

I change the strDomain  to my netbios domain name
In the CSV under A i enter my domain user

thanks
0
 
LVL 27

Expert Comment

by:bluntTony
ID: 26306287
It needs to be the user login name (sAMAccountName), i.e. the 'username' bit in 'DOMAIN\username'. Check the 'Account' tab for the user in ADUC. In the CSV, just enter the 'username' bit.

This error usually means it cannot find the name given.
0
 

Author Comment

by:brular01
ID: 26310419
Good I figure out,  

I need to put a valid AD login for the manager, is there a way to only put the full name ?

Another thing, even if the manager user id exists the script will put the name of the user that we are updating

Finnaly, if the used does not exist, I would like to have the script continu to update the others rows listed in the csv.

Let me know, and thanks a lot for your help !!!
0
 
LVL 27

Expert Comment

by:bluntTony
ID: 26312344
Hi there,

I need to put a valid AD login for the manager, is there a way to only put the full name ?

-- Yes, but Fullname isn't unique so isn't 100%. I'll post a script shortly that utilises fullname instead of login.

Another thing, even if the manager user id exists the script will put the name of the user that we are updating

-- The manager column should have the login of the manager in question. What do you want to enter in this column?

Finnaly, if the used does not exist, I would like to have the script continu to update the others rows listed in the csv.

-- Adding 'On Error Resume Next' at the top of the script will do this, but let me know about the other two questions and I'll post a new script.

Tony
0
 

Author Comment

by:brular01
ID: 26314484
For Manager i would like to use the Full Name if possible ( if not possible no problem i can deal with that )

thanks
0
 
LVL 27

Expert Comment

by:bluntTony
ID: 26315642
Hi there,

The script will now accept full names for both the user and the manager. See how this goes in testing. The CSV order is now:

User(Full Name),Title,Department,Manager(Full Name)

Tony

Const ForReading = 1

strCSV = "C:\mycsv.csv"

'Objects
Set oRootDSE = GetObject("LDAP://RootDSE")
Set objConn = CreateObject("ADODB.Connection")
Set objComm =   CreateObject("ADODB.Command")
Set objFso = CreateObject("Scripting.FileSystemObject")
Set objCSV = objFSO.OpenTextFile(strCSV,ForReading)

'ADODB Parameters
objConn.Provider = "ADsDSOObject"
objConn.Open "Active Directory Provider"
Set objComm.ActiveConnection = objConn
objComm.Properties("Page Size") = 1000
strBase   =  "<LDAP://" & oRootDSE.get("defaultNamingContext") & ">;"
strAttrs  = "distinguishedName;"
strScope  = "subtree"

'USE THIS NEXT LINE IF THE CSV INCLUDES HEADERS!!!
objCsv.SkipLine 

Do Until objCsv.AtEndOfStream
        arrData = Split(objCsv.ReadLine,",")
        'Bind to user
        strDN = getDNFromFullName(arrData(0))
        If strDN <> "" Then
	        Set objUser = GetObject("LDAP://" & strDN)
	        'Set attributes
	        objUser.Put "title", arrData(1)
	        objUser.Put "department", arrData(2)
	        strManagerDN = getDNFromFullName(arrData(3))
	        If strManagerDN <> "" Then objUser.Put "manager", strManagerDN
	        objUser.SetInfo
        End If
Loop


Set oRootDSE = Nothing
Set objConn = Nothing
Set objComm = Nothing

'END!! ##########


Function getDNFromFullName(strFullName)
 strFilter = "(&(objectclass=user)(objectCategory=person)(name=" & strFullName & "));" 
 objComm.CommandText = strBase & strFilter & strAttrs & strScope
 Set objRS = objComm.Execute
 If objRs.RecordCount = 1 Then getDNFromFullName = objRs.Fields(0).Value Else getDNFromFullName = ""
End Function

Open in new window

0
 

Author Comment

by:brular01
ID: 26316411
Awesome its pefect, 1 last thing and I leave you alone.

If there a way to output the error in a txt file when it occur, but continu the script to run.

thanks you !!! thanks you !!!
0
 
LVL 27

Accepted Solution

by:
bluntTony earned 2000 total points
ID: 26331515
Hi there,

The below script will now write to an error log file 'strLog' when either the fullname of the user is not recognised, or the fullname of the manager for a user is not recognised.

Hope this helps!

Tony

Const ForReading = 1
Const ForWriting = 2

strCSV = "C:\mycsv.csv"
strLog = "C:\errorlog.log"

'Objects
Set oRootDSE = GetObject("LDAP://RootDSE")
Set objConn = CreateObject("ADODB.Connection")
Set objComm =   CreateObject("ADODB.Command")
Set objFso = CreateObject("Scripting.FileSystemObject")
objFso.CreateTextFile strLog, True
Set objLog = objFso.OpenTextFile(strLog,ForWriting)
Set objCSV = objFSO.OpenTextFile(strCSV,ForReading)

'ADODB Parameters
objConn.Provider = "ADsDSOObject"
objConn.Open "Active Directory Provider"
Set objComm.ActiveConnection = objConn
objComm.Properties("Page Size") = 1000
strBase   =  "<LDAP://" & oRootDSE.get("defaultNamingContext") & ">;"
strAttrs  = "distinguishedName;"
strScope  = "subtree"

'USE THIS NEXT LINE IF THE CSV INCLUDES HEADERS!!!
objCsv.SkipLine 

Do Until objCsv.AtEndOfStream
        arrData = Split(objCsv.ReadLine,",")
        'Bind to user
        strDN = getDNFromFullName(arrData(0))
        If strDN <> "" Then
	        Set objUser = GetObject("LDAP://" & strDN)
	        'Set attributes
	        objUser.Put "title", arrData(1)
	        objUser.Put "department", arrData(2)
	        strManagerDN = getDNFromFullName(arrData(3))
	        If strManagerDN <> "" Then 
	        	objUser.Put "manager", strManagerDN
	        Else
	        	objLog.WriteLine "Error >> Could not add mananger field to user " & arrData(0) _
	        		& ", the manager '" & arrData(3) & "' could not be found in the directory"
	        End If
	        objUser.SetInfo
	    Else
	    	objLog.WriteLine "Error >> The user '" & arrData(0) & "' could not be found in the directory"	        
        End If
Loop

objLog.Close
objCsv.Close

Set oRootDSE = Nothing
Set objConn = Nothing
Set objComm = Nothing

'END!! ##########


Function getDNFromFullName(strFullName)
 strFilter = "(&(objectclass=user)(objectCategory=person)(name=" & strFullName & "));" 
 objComm.CommandText = strBase & strFilter & strAttrs & strScope
 Set objRS = objComm.Execute
 If objRs.RecordCount = 1 Then getDNFromFullName = objRs.Fields(0).Value Else getDNFromFullName = ""
End Function

Open in new window

0
 

Author Comment

by:brular01
ID: 26346463
Tony you are the best thanks you !!
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This process allows computer passwords to be managed and secured without using LAPS. This is an improvement on an existing process, enhanced to store password encrypted, instead of clear-text files within SQL
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
Suggested Courses

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question