Guy's Scripting Ezine 42 - Modify Users

Guy recommends:
VBScript Training Video on CD

Is your server running slowly?  Check with SolarWinds ipMonitor

Get a free evaluation copy of ipMonitor

Contents for Guy's Scripting Ezine 42 - Modify Users with Excel

• This week's secret
• Example 1 Simple script to open the spreadsheet
• Example 2 Creating a script to add telephone numbers
• Guy's Challenge
• Improvement by DA
• Summary

This week’s secret

CSVDE does not MODIFY existing Active Directory objects.  I mention this limitation because each week people write and ask me how to modify a user's attributes with CSVDE.  And it breaks my heart to have to explain that you need VBScript or LDIFDE to modify existing users.

Now do not get me wrong, CSVDE is a great utility if you need to bulk import 100s of new users from a spreadsheet.  I also love CSVDE because it taught me so much about LDAP attributes.  To paraphrase Clint Eastwood, ' A command must know his limitations'.

This week's project

Since we cannot use CSVDE to modify an existing user, we will employ VBScript instead.  Just like CSVDE feeds from information stored in a spreadsheet, so will our VBScript read those same cells of a spreadsheet.

I am worried because this script is difficult.  The solution is to go slowly and build up confidence by analysing each component of the script.

Active Directory Preparation

Firstly, please check Active Directory.  Can you identify the name of my test OU?  The answer is that I am using an OU called Cowbridge as a test bed for my script (See Line 13).  So, either create a Cowbridge OU in your domain, or else amend Cowbridge in the script to the name of your test OU.

Spreadsheet Preparation

Next you need a spreadsheet.  In the first row put the LDAP fields, for example in Row 1, Column B (b1) enter telephoneNumber.  telephoneNumber is a precise LDAP attribute that Active Directory understands.  In the second row under column B (Cell b2) enter the phone number you want to add to your users.  (We can pretend everyone in Cowbridge uses the same office phone number.)

Where is my spreadsheet saved?  Line 13 (22) tells us: strPathExcel = "c:\scripts\cowbridge.xls"   Now all you have to do is save your file to the same path, else change the name in line 13 (22) to reflect YOUR spreadsheet name and YOUR path.

Example 1 Simple Script to open the spreadsheet

Scenario.  In the build up to the main script, you want to check that the spreadsheet has been created properly, and the actual phone number is in the correct cell.

Learning Points

Note 0: Suggestion from DA - add objExcel.Quit.  Otherwise you will see zillions of Excel applications in task manager.  (Guy added objExcel.Quit to later scripts but forgot here).

Note 1:   Set objExcel = CreateObject("Excel.Application")  This is where we create a spreadsheet.  The idea is to adapt the CreateObject method to be Excel.Application, rather than a Notepad application or even a Network object.  (In other scripts we use:

Note 2:  Once we create the Excel object we put it to work to open the file at the end of the strPathExcel.

Note 3:  intRow =2 is vital to tell VBScript to look on the second row (not the top row)

Note 4:  objSheet.Cells(intRow,2).Value.  Question what does intRow,2 mean?  The answer is Column B, the second column.  So if you have other LDAP attributes amend this value according to the Column you place the values.

Example 2 Creating a script to add telephoneNumber values

Scenario.  All users in the Cowbridge OU need their telephoneNumber changing.

Learning Points

Note 0: Check out the user's properties, open Active Directory Users and Computers and navigate to your OU (equivalent of Cowbridge).

Note 1: This script amends ALL the users in the named OU Cowbridge.  Technically this is controlled by the For... Next Loop.  Logically, you would only choose values like physicalDeliveryOfficeName (Office), where all the user's genuinely needed the same value, for example they are all in the SWales office.

Note 2: Pay close attention to
objUser.Put "telephoneNumber", strPhone.  What this line does is assign strPhone to the LDAP field telephoneNumber.

Guy's Challenge

Once your basic script works, then you can experiment with other LDAP fields like, Description, department, Title, physicalDeliveryOfficeName (Office).  To accept this challenge,
1) Open up your spreadsheet and add extra LDAP fields with their values in the second row. 
2) Add extra:  strLDAPValues = "OfficeName" to the script. 
3) Append: objUser.Put "LDAP Property", strLDAPvalue

Summary

While CSVDE does not allow you to modify Active Directory objects directly, the secret is to store the attributes in a spreadsheet then use CSVDE to import into your Active Directory.  If your aim is to modify existing objects then VBScript (unlike CSVDE) will permit addition or alteration of LDAP properties.

Guy Thomas recommends Computer Training Software. 

Their topics and material are ideal for getting you started with VBScript.  The videos are easy to follow and you can control the pace.  Try their free demo material and then see if you want to buy the full package. See more about VB Script Training CD.

Home Copyright © 1999-2008 Computer Performance LTD All rights reserved

Please report a broken link, or an error.