Guy's Scripting Ezine 91 - Double Looping

Contents for Ezine 91 - Double Looping

Guy recommends:
VBScript Training Video on CD

Is your server running slowly?  Check with SolarWinds ipMonitor

Get a free evaluation copy of ipMonitor

• This Week's Secret
• Scenario
• Example Double Looping VBScript
• Summary of Looping
• Download the Spreadsheet

This Week's Secret

My site is becoming a victim of its own success.  As a result, I am being snowed under with requests for help.  What I want to do is explain my philosophy.  The best use of my time is creating good scripts, which many people can read, modify and enjoy.

For everyone's sake, I need to know about errors and corrections, so I welcome such emails and am only too happy to reply in the normal (free) way.  However, for individual help in creating a custom script I charge $25.  The grey area is the 'quick question', which turns into enough emails to fill the small ads of Computer Weekly.  So here is the deal, I will respond to one 'quick question' for free, after that I will charge $10 to research a more comprehensive solution.  Now I am not looking for work, I want to spend my time writing ebooks and web pages, but I feel a responsibility to support readers and especially those who buy my ebooks.

Double Looping VBScript

This week's ezine features one of my most complex scripts.  The only way to truly understand it is to immerse yourself in the scenario, then work through the three sections described in the script.  In particular, I urge you to isolate then trace each loop.

The Scenario

Let us assume that you wish to add data to your Active Directory users.  Now, if you wanted all the users to have the same values then we could employ a simpler script.  But being awkward devils, each of these users need us to set different values for a variety of LDAP properties, for example, Fred: Office Worcester, Department Sales.  Joe, Office Birmingham, Department Tech Support.  Fortunately, we have a spreadsheet with the user's logon names in column 1.  Also in this spreadsheet are further columns holding the values for more users' LDAP properties: for example, Office, Department and DisplayName.  Two more points about this famous spreadsheet, firstly, I have a demonstration version that you can download online, secondly, you need to identify the line in the script where VBScript calls for this spreadsheet. Download Spreadsheet here

Purpose of the two loops

If as I hinted earlier, if all we needed to do was add the same data to every user then we would only need one loop.  In a single loop script, you could cycle through all the Active Directory objects and add the same value.  For example Office = Birmingham.  However, if you had to script, If Fred then Office = Worcester, Else If Joe Office = Birmingham, else If Mary.... It would be very tedious and poor scripting technique.  The answer is script with two loops.

To understand the script, you have to imagine that the first loop has obtained one of your users from Active Directory, we will call this user Fred.  Pretend that you are holding Fred between your finger and thumb.  Now you compare Fred to each name in your spreadsheet.  When you find his name, you read off his Office from the adjacent column.  I hope that you can now see the job of the second loop (Do Until), to cycle through the names in the spreadsheet looking for an exact match in column 1.

How to Develop a Double Loop Script.

The concept is easy, but the nitty gritty is tricky.  Get the outer loop working first.  Create a script to change the Department of everyone in your test, repeat test, OU.  Next build a spreadsheet with the LDAP fields, if necessary, employ ADSI Edit to research the particular names for the LDAP properties.  Create a script which can read the spreadsheet.  Bolt the two Loops / Scripts together with an instruction such as: If LCase(strName) = LCase(strADname) Then....

Instructions for modifying Users properties in Active Directory

1. Decide upon the OU, this is vital.  (I choose OU=Students, note the comma in line 8.)
2. Research or create genuine users in your OU.  Add logon (sAMAccountName) to column 1 of spreadsheet.
3. Prepare the spreadsheet for LDAP properties.  See online spreadsheet for an example layout.  Check line 18 for the path to your spreadsheet.
4. One advantage of a good script editor such as OnScript is that you can see the line numbers.
5. Research LDAP values, for example, Department and PhysicalDeliveryOfficeName (not Office)
6. Copy and paste the example script below into notepad or use a VBScript editor.
7. Save the file with a .vbs extension, for example: 2LoopsExcelAD.vbs 
8. Double click 2LoopsExcelAD.vbs and check your Active Directory Users and Computers.
9. If you run the script for a second or third time and then cannot find the changes in Active Directory Users and Computers, don't rely on F5, right click the OU and select Refresh from the short cut menu.
10. Tip in Active Directory Users and Computers, from View Menu adjust fields with Add / Remove Columns, for example add extra columns for Office, Department.

Example Double Looping VBScript

Guy's Learning Points

Note 0:  You really need an editor such as OnScript.

Note 1:  Read the comments in the scripts.  Uncomment the LDAP properties.  For example
' objUser.Put "displayName", strDisplay - remove the apostrophe and run the script again.

Note 2:  Trace the outer for ... next loop.

Note 3:  Investigate how For.. Next loop uses the sub routine LDAPattrib to call the second Do Until... loop.

Note 4:  Keep working with the spreadsheet.  Make adjustments to the names and their values

Note 5:  Beware calling null values, especially blank columns.  In fact, the script cries out for an error correcting section for example, if strDepartment ="" then xyz.  (I did not want to make it any longer.)

Summary of Double Looping

There will be times when you need a VBScript with two loops.  As ever, the secret is to break down a complex task into a series of stages; get each part working then bolt it all together.  Remember the old sage, 'Yard by Yard and its hard, Inch by Inch and its a cinch.

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.