By David Wiseman (Administrator)published 08 May 2010
My Rating:
Vote
Rating:
Not Rated
Views:20099

Bulk AD Users - Edit In Excel

Introduction

Bulk AD Users allows you to edit user account data in Microsoft Excel, providing you with full access to all the powerful data manipulation features of Microsoft's spreadsheet application.  The Bulk Modify feature of Bulk AD Users can be used to perform quite a variety of updates, but Excel provides access to a much large number of functions that you can use to perform more complex updates.  There is also a good chance that you are already familiar with Microsoft Excel, so the learning curve is greatly reduced as you don't need to learn a new expression language.  The rest of this page will show you how easy it is to use the edit in excel feature to apply a bulk update to a large number of user accounts.
 
Instructions
 
  • Open Bulk AD Users
  • Add any attributes you want to modify to the search grid using the "Properties To Load" feature.
  • Select the user accounts you want to update
  • Select "Edit In Excel" from the update menu or click the toolbar icon.
  • Wait for the account data to be refreshed or click cancel if you are sure the cached data in the grid is already up-to-date.
    Note: This behaviour can be modified by changing the "RefreshDataBeforeExcelEdit" option in the "BulkADUsers.exe.config" file.
  • You should now see your user account data in Microsoft Excel.  Edit the spreadsheet as required, making use of any of Excel's formulas if required.  If the spreadsheet contains user accounts that you don't want to modify, feel free to remove those rows - The user accounts won't be deleted.  
    Note: The ADsPath column is used to identify the user accounts in Active Directory so don't modify the data in this column or remove the column from the spreadsheet.  Also, the column names in the spreadsheet are used to identify the attributes to update, so don't make any changes to the column names unless it is your intention to modify a different attribute.

  • After you have finished making changes to the spreadsheet, simply close Microsoft Excel and save changes to the spreadsheet file when prompted.
  • You will now be prompted to select the attributes that you want to update.  

    Check any attributes that you want to update and click "OK"
  • Bulk AD Users will now perform the update and display the "Bulk Modify Results" window when it's finished.  The results dialog allows you to check for any failed updates and enables you to rollback the update if required.  An XML log file is produced for the update and you can open this file at any time in the results dialog to review the update and perform a rollback.

Excel Compatibility

The edit in Excel feature has been tested with the following versions of  Microsoft Excel:
  • Excel 2003
  • Excel 2007 
The feature might also work with other versions of Excel, but it hasn't been QA tested.

Known Issues 

  • Data might be incorrectly converted to a number by Excel resulting in data loss.  e.g. Excel would interpret the value "01234" as a number by default and the zero is lost in this process leaving the value as "1234".  You can change the number format to "Text" to prevent Excel from converting text data to numeric.  
  • If you are running a non-English language version of Excel, you might get a error if the default name of the sheet tab is not "Sheet1".  You can rename the sheet to "Sheet1" as a workaround for this issue.
  • You might get a ""Old format or invalid type library" error message if you run an English version of Excel, but the language settings for the computer are for a non-English language.  See here for a workaround.
 These issues will be resolved in future versions of the application.
 
 
 
 






Bulk AD Users