VBScript program to update Active Directory user accounts in bulk from the information in a Microsoft Excel spreadsheet. The first row of the spreadsheet indicates the names of the attributes to be updated. One attribute must uniquely identify the users. Each subsequent row provides information to update one user. If a cell has no value (is empty) the program will not alter the attribute for that user. The special value ".delete" means to remove any existing value for the attribute. Any other value is used to update the attribute. Values are only updated if they are different from the existing value.

Each column in the first row of the spreadsheet should be the LDAP display name (case insensitive) of a user attribute. One of the columns should be either distinguishedName or sAMAccountName to uniquely identify the user object. If there are columns for both distinguishedName and sAMAccountName, then distinguishedName is used to identify the objects and the value of the sAMAccountName can be updated. If a column for cn (Common Name) is included, the objects are renamed (if there is a value supplied and it is different from the existing Common Name).

Two versions of the program are provided. The first version, called UpdateUsers.vbs, only supports single-valued string attributes. Examples would include sn, givenName, TelephoneNumber, and streetAddress. Note that the description attribute is actually multi-valued, even though there is never more than one value, so it is not supported in this version. This version is shorter, handles most needs, and the code is easier to understand.

The second version, called UpdateUsers2.vbs, supports the following attribute syntaxes:

Several of the attribute syntaxes in the second version of this program allow you to specify more than one value in a spreadsheet cell; the multi-valued string and DN attributes. Each separate value in the spreadsheet cell should be delimited by semicolons. The program supports two modes in these cases, an update mode and an overwrite mode. If any of the semicolon delimited values is prefixed by either a "+" or "-" character, the program works in update mode. In update mode any value prefixed by "+" or with no prefix is added to the multi-valued attribute (if it is not already in the existing collection of values). Any value in the cell prefixed by "-" is removed from the attribute (if it is in the existing collection). If none of the values read from the spreadsheet has either the "+" or "-" prefix, the program operates in overwrite mode, where the values from the spreadsheet replace any existing values in the attribute. As usual, the special value ".delete" means to remove all values in the multi-valued attribute.

The special attributes pwdLastSet, accountExpires, userWorkstations, and memberOf are handled separately. The last two are multi-valued, so are handled in the way described above, except that there is no overwrite mode supported for memberOf.

The only values allowed for pwdLastSet are 0 and ".delete", both of which mean the user must change their password the next time they logon. Any valid date/time value can be used for accountExpires, for example "04/16/2009 15:30" or "05/12/2010". If the value ".delete" is used, accountExpires is set to 0, which means the account never expires.

The values supplied for the userWorkstations attribute should be semicolon (or comma) delimited lists of computer NetBIOS names. A comma delimited list is supported because the actual attribute is comma delimited and, unlike the other multi-valued attributes, none of the actual values can have commas. This attribute designates the computers the user is allowed to log into. As usual, the value ".delete" means to clear the attribute (so it has no value). Both the update and overwrite modes are supported.

Similarly, the values supplied for the memberOf attribute should be a semicolon delimited list of group names. This attribute designates the groups the user is a direct member of. This does not include the "primary group" of the user (usually the group "Domain Users"). The group names can be either Distinguished Names or NetBIOS names (pre-Windows 2000 name, which is the value of the sAMAccountName of the group). The user is added to any group name prefixed by a "+" or with no prefix. The user is removed from any group prefixed by "-". There is no overwrite mode where all group memberships are replaced. The special value ".delete" is also not recognized for this attribute.

Flag attributes, like userAccountControl, are updated using bit masks. The following bit masks are recognized by the program:

The value in the spreadsheet for a flag attribute can be one of the above bit mask strings, the equivalent hex value (as indicated by the prefix "&H"), or the equivalent decimal value. If the value in the spreadsheet is prefixed by "+" (or if the value has no prefix), the program sets the corresponding bit. If the value in the spreadsheet is prefixed by "-", the program clears the corresponding bit. For example, to enable a user account you could use any of the following values: -ADS_UF_ACCOUNTDISABLE, -&H02, or -2. To require that a user logon with a smartcard you could use any of the following values: +ADS_UF_SMARTCARD_REQUIRED, +&H4000, +262144, ADS_UF_SMARTCARD_REQUIRED, &H4000, or 262144. Each bit of the attribute that is to be modified must be specified in a different column of the spreadsheet, each with userAccountControl in the first row. If bit masks other than the ones in the table above are used, you must supply either the hex or decimal value.

If a DN attribute is specified, the value in the spreadsheet can be either a Distinguished Name or a "pre-Windows 2000" logon name (the value of the sAMAccountName attribute). The program validates the name by attempting to convert the value using the NameTranslate object. The corresponding Distinguished Name is used to update the attribute. If a DN() attribute is specified (a multi-valued DN attribute), the values in the spreadsheet can be either Distinguished Names or "pre-Windows 2000" logon names. The values must be delimited by semicolons.

Remember that the following characters must be escaped with the backslash escape character, "\", if they appear in Distinguished Names:

, \ # + < > ; " =

In addition, leading and trailing spaces in Common Names, Organizational Unit names, and domain component names must also be escaped. Do not escape forward slash characters, "/". Ordinarily, this character does not need to be escaped, unless you use ADSI (as VBScript programs do), so the program handles this character for you. For example, if you view attribute values with ADSI Edit or ldp, any of the characters mentioned above will be escaped, but not the forward slash.

Because several multi-valued cells in the spreadsheet are semicolon delimited, it becomes necessary to escape any semicolons that appear in the values. This is always necessary for DN values, but if any attributes have syntax String(), or the attribute is memberOf, remember to escape any embedded semicolon characters, so the program can properly parse the semicolon delimited values. Escape semicolons that are part of the values by replacing ";" with "\;". Semicolons in single-valued string attributes should not be escaped.

When entering a value in the spreadsheet that starts with either a "+" or "-" character, remember to enter an initial apostrophe character, "'", also called the single quote. This indicates to Excel that the string is not arithmetic. The initial apostrophe is not passed to the program.

The program writes a log file to document which users were updated and any errors encountered. Error messages in the log all begin with the string "## ", so you can quickly find them. The name and path of the spreadsheet file and the log file are both hard coded in the program.

The program should be run at a command prompt using the cscript host program. The program echoes the names of the users to the screen as they are processed, to indicate progress. This allows you to monitor how fast the program is running, but would result in annoying message boxes if you used the wscript host program.

As noted, the first version of the program, UpdateUsers.vbs, only supports single-valued string attributes.

UpdateUsers.txt <<-- Click here to view or download the program

Two example spreadsheets showing the format expected by this program are linked below. The first spreadsheet identifies the users by distinguishedName.

UpdateUsers1.xls

The second spreadsheet identifies users by sAMAccountName.

UpdateUsers2.xls

The second version of the program, UpdateUsers2.vbs, supports many more attributes, but is longer and more complex.

UpdateUsers2.txt <<-- Click here to view or download the program

Again, two example spreadsheets showing the format expected by the second version of the program are linked below. The first spreadsheet identifies the users by distinguishedName.

UpdateUsers3.xls

The second spreadsheet identifies users by sAMAccountName.

UpdateUsers4.xls

The programs were designed to modify user objects. However, they can be used to update any class of AD objects. For example, they can be used to update group or computer objects. You would specify attributes appropriate to the class of object. In each row you would identify the specific objects by either distinguishedName or sAMAccountName. Remember that the sAMAccountName of a computer object is the NetBIOS name with a trialing "$" character. If the Relative Distinguished Name of the object class is not the common name, then you cannot specify the cn attribute and cannot rename the objects. An example would be organizational unit objects. Also, some object classes do not have the sAMAccountName attribute, like contact and organizational unit objects. In that case you must specify the distinguishedName to identify the object.

A PowerShell Version 1.0 script has been developed similar to UpdateUsers1.vbs above. This script updates users in bulk from the information in a Microsoft Excel spreadsheet. Only single-valued string attributes are supported in this version. The script also demonstrates a method for handling errors, using the PowerShell Trap statement and checking the error descriptions. It also demonstrates a function to release the Excel objects from memory. Unfortunately, this doesn't always work and objects are left in memory. You may need to use Task Manager to end the Excel.exe process in memory. The script is linked below.

PSUpdateUsers.txt <<-- Click here to view or download the program