Contents for Ezine 91 – Double Looping
- This Week’s Secret
- Example Double Looping VBScript
- Summary of Looping
- Download the Spreadsheet
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.
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.
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
- Decide upon the OU, this is vital. (I choose OU=Students, note the comma in line 8.)
- Research or create genuine users in your OU. Add logon (sAMAccountName) to column 1 of spreadsheet.
- Prepare the spreadsheet for LDAP properties. See online spreadsheet for an example layout. Check line 18 for the path to your spreadsheet.
- One advantage of a good script editor such as OnScript is that you can see the line numbers.
- Research LDAP values, for example, Department and PhysicalDeliveryOfficeName (not Office)
- Copy and paste the example script below into notepad or use a VBScript editor.
- Save the file with a .vbs extension, for example: 2LoopsExcelAD.vbs
- Double click 2LoopsExcelAD.vbs and check your Active Directory Users and Computers.
- 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.
- 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 VBScript to set Active Directory Properties
‘ Reads values from a spreadsheet.
‘ Employs two ‘For .. next’ loops
‘ Author Guy Thomas https://computerperformance.co.uk/
‘ Version 2.2 – October 2005
Dim objFSO, objExcel, objSheet, strPathExcel
Dim intBadSet, intRow, intAccSet, intAccValue
Dim objName, objOU, objUser, strName, strContainer, strBad
Dim objRootDSE, strDNSDomain, arrUser, strADname
Dim strDesc, strDisplay, strL, strOffice, strCompany
‘ Important Change strContainer and strPathExcel
strContainer ="OU=Students," ‘Change for your domain
strPathExcel = "E:\Scripts\ModifyUsers.xls"
intRow = 4 ‘ Row 1 contains headings, Row 3 has numbers
intBadSet = 0 ‘ Count Mistakes
‘ ======== Section A ==========
‘ Creates objects to handle spreadsheet and AD
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objRootDSE = GetObject("LDAP://RootDSE")
strDNSDomain = objRootDSE.Get("DefaultNamingContext")
strContainer = strContainer & strDNSDomain
set objOU =GetObject("LDAP://" & strContainer )
‘ Open the Excel spreadsheet – Just once
Set objExcel = CreateObject("Excel.Application")
Set objSheet = objExcel.Workbooks.Open(strPathExcel)
For Each objUser In objOU
objName = LCase(objUser.sAMAccountName)
arrUser =Split(objName,",") ‘ Get rid of CN=
strADname = arrUser(0)
call LDAPattrib() ‘ Goto second loop
intRow=4 ‘ Vital to make second loop work
‘ Wscript.Echo "Main " & strADname ‘ For testing
objExcel.Quit ‘ Closes Excel – check Task Manager
‘ ======== Section B ==========
‘ Here is the loop that cycles through the cells
‘ Expand or contract this section B to suit the task
‘ See how intRow 2 corresponds to Column B in spreadsheet
On Error Resume Next ‘ Incase of invalid LDAPattrib
Do Until objExcel.Cells(intRow,1).Value = ""
strName = Trim(objExcel.Cells(intRow, 1).Value)
strDesc = Trim(objExcel.Cells(intRow, 2).Value)
strDisplay = Trim(objExcel.Cells(intRow, 3).Value)
strL = Trim(objExcel.Cells(intRow, 4).Value)
strCompany = Trim(objExcel.Cells(intRow, 4).Value)
strOffice = Trim(objExcel.Cells(intRow, 5).Value)
‘ ======== Section C ==========
‘ Match section C below with the above section.
‘ Note Uncomment displayName, department as needed
‘ VBScript can be case sensitive
If LCase(strName) = LCase(strADname) Then
objUser.Put "description", strDesc
‘ objUser.Put "displayName", strDisplay
‘ objUser.Put "department", strL
‘ objUser.Put "company", strCompany
‘ objUser.Put "l", strOffice
‘ objUser.Put "PhysicalDeliveryOfficeName", strOffice
‘ Wscript.Echo strName & " Department = " & strL ‘ For Testing
intAccSet = intAccSet +1
intRow = 20000 ‘ Go to end ready for next
If err.number <> 0 Then ‘ e.g. null string
intBadSet = intBadSet + 1
intAccSet = intAccSet -1
WScript.Echo "Bad account set " & strADname
strBad = strADname
‘ Else WScript.Echo strName & " " & strADname ‘ Testing
intRow =intRow +1
WScript.Echo intAccSet & " accounts set" _
& vbCr & "Bad accounts " & intBadSet & " last " & strBad
objExcel.Quit ‘ Extra close excel. Check Task Manager
‘ End of Active Directory 2 Loops 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.)
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.
See more about VBScript