Partager via

Create User Accounts from Information in an Excel Spreadsheet

  • Article

 

Greg Stemp
Microsoft Corporation

April 14, 2004

Summary: Demonstrates how script writers can retrieve information stored in a Microsoft Excel spreadsheet and use that information to create new user accounts in Microsoft Active Directory. (13 printed pages)

System administrators, or so we are often told, are the last of the rugged individualists. System administrators don't use mice and dropdown lists; they type their commands at the command line. System administrators don't bother with graphical user interfaces; they like their data displayed in the command window. System administrators don't pull up to the drive-through window at a fast-food restaurant, they actually get out of the car and... well, okay, even rugged individualists have to draw the line somewhere. The point is, you can run a script by starting it from the command prompt and passing it a bunch of command-line arguments; in addition, you can output data to the command window or to a text file. What more could you possibly need?

The truth is, there are lots of times you don't need anything else. On the other hand, sometimes—just sometimes, mind you—system administrators look at applications like Microsoft® Excel and think about how nice it would be to harness some of the capabilities Excel for their own use. Consider, for example, the need to create a bunch of new user accounts in Microsoft® Active Directory®. Could you do that by passing command-line parameters to a script? Sure, if you don't mind typing in command strings similar to this:

cscript new_user.vbs /cn="Myer Ken" /sAMAccountName="kenmyer" /givenName="Ken" /SN="Myer"

Okay, maybe that's not such a good idea. But couldn't you read in all the information from a text file? You bet you could. Of course, the text file would have to look something like this:

"CN","sAMAccountName","givenName","SN"
"Myer Ken","kenmyer","Ken","Myer"
"Jones TiAnna","TiAnnajones","TiAnna","Jones"
"Smith Ben","Bensmith","Ben","Smith"

That works, too, but good luck getting your HR department to supply you with a text file that looks like that. Can you use these techniques? If you want to. But think about how easy it would be for your HR department (or whoever) to type all this information in Excel; in fact, there's a good chance that's what they already do. Yes, Excel is a graphical utility, and, yes, you're a rugged individualist, but just this once, wouldn't it be nice to be able to take an Excel spreadsheet, run a script against it, and then use the information gleaned from the spreadsheet to create a whole bunch of user accounts, all in one fell swoop?

Okay, sure, not for you. But maybe you have a friend who isn't as rugged an individualist as you are. If so, tell your friend that he or she is in luck. This month we're going to tell you—um, we're going to tell them how to use a script to read data directly from an Excel spreadsheet, and then use that data to create a bunch of new user accounts in Active Directory. And then next month we'll show them how to go the opposite route: How to grab data out of Active Directory (or some other location) and display it in a nicely-formatted spreadsheet. And listen: If you decide to try these scripts, we promise not to tell your fellow system administrators. Promise.

Creating and Terminating an Instance of Microsoft Excel

Let's start with the simplest possible script, one that creates an instance of Microsoft Excel and then adds a new workbook to that instance:

Set objExcel = CreateObject("Excel.Application")
objExcel.Workbooks.Add

That was easy enough, wasn't it? Save your new script as a .vbs file and run it; when you do, your desktop should look something like this (depending, of course, on the wallpaper you're using):

Figure 1. Creating an invisible instance of Excel

Just think, you've only been at this for a few minutes, and you've already written a script that does absolutely nothing. You're now qualified to be a Microsoft Scripting Guy!

But wait, don't go; it turns out that when you are scripting Microsoft® Office applications, what you see (or don't see) isn't necessarily what you get. The preceding script actually worked, and you really did create a brand-new instance of Microsoft Excel. (Don't believe me? Then press CTRL-ALT-DEL and take a look at the Processes tab in the Microsoft® Windows® Task Manager. You should see an instance of Excel.exe.) In other words, here is your first official Microsoft Office scripting lesson: By default, any time you use a script to create an instance of a Microsoft Office application, that application runs in a window that is not visible on screen. Excel is there; you just can't see it. (Sort of like the last raise they said you got.)

Now, believe it or not, this is actually a good thing. This is a real, live instance of Microsoft Excel. As you'll soon see, you can programmatically read data from it or, for that matter, do pretty much anything else you can do with Excel. The only functionality you lose when Excel runs in an invisible window is the ability to type something on the keyboard and have the application react to those keystrokes. And that's what makes the default behavior useful. Suppose you were running a script that created a report using Excel, and suppose Excel was visible the whole time the script was running. A user (even yourself) could accidentally hit a key on the keyboard and ruin the entire report. A user (even yourself) could simply close Excel, ruining not only the report, but also causing your script to blow up. (After all, the script will be trying to send commands to an instance of Excel that no longer exists.) By running Excel invisibly, you can sidestep problems like that. The truth is, scripting Microsoft Excel is no different than being a parent: sometimes it's better that**you don't see what's going on.

**Tip   **Okay, so what if you would like Excel to be visible on screen? No problem, just set the Visible property to True. This little script will create an instance of Microsoft Excel and—just like magic—make that instance appear on your screen:

Set objExcel = CreateObject("Excel.Application")
objExcel.Workbooks.Add
objExcel.Visible = True

As an introduction to Microsoft Office scripting lesson number 2, let's modify this script so it creates an instance of Excel, displays it on screen, pauses for 10 seconds, and then quits. (Something remarkably similar to my typical workday.) Here's our modified script:

Set objExcel = CreateObject("Excel.Application")
objExcel.Workbooks.Add
objExcel.Visible = True
Wscript.Sleep 10000
Wscript.Echo "The script is now complete."

What happens when you run this script? Well, an instance of Excel will be created, and it will appear on your screen. There will be a 10-second pause, and then a message will appear telling you that the script is now complete. When you click OK, the script will immediately terminate (as soon as Microsoft® VBScript reaches the end of a script, the script process terminates).

And here's what your screen will look like:

Figure 2. Excel running after a script terminates

As you can probably tell, even though the script is finished, Microsoft Excel is still running; it didn't terminate when the script terminated. Thus your second official Microsoft Office scripting lesson: Microsoft Office applications run "out-of-process." What does that mean? Well, some COM objects (such as the FileSystemObject) run in the same process as your script. That means that when the process in which the script is running is terminated, then the process in which the COM object is running is terminated too (seeing as how they're the same process). When the script process terminates, the FileSystemObject terminates as well.

Not willing to take my word for this? Then I'll prove it. Write a script that creates an instance of the FileSystemObject, open up Task Manager, and then run the script. When the script runs, you'll notice that only one new process is created (either Cscript.exe or Wscript.exe, depending on which script host you are using). That's because both the script and the FileSystemObject share that process.

Okay, stop fretting; we'll write a FileSystemObject script for you:

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder("C:\")
Wscript.Sleep 10000
Wscript.Echo "The script is now complete."

Now, write a script that creates an instance of Microsoft Excel, open up Task Manager, and then run that script. You should see two new processes: Wscript.exe (or Cscript.exe) and Excel.exe. That's because Excel runs in its own process. When your script finishes; the script process (Wscript.exe or CScript.exe) will disappear, but the Microsoft Excel process (Excel.exe) will continue chugging along.

This is, by the way, is very important: Unless you specifically quit Excel, Excel.exe will continue to run (and continue to use up memory). What if you run 5 scripts that each create an instance of Excel? In that case, your Task Manager is going to look something like this:

Figure 3. Multiple instances of Excel in Task Manager

Not a pretty sight, and a good way to quickly exhaust all the available resources on a computer. Remember: terminating a script will not automatically terminate a Microsoft Office application.

Sounds a little hopeless, doesn't it? But don't despair. Do you need to terminate an instance of Excel? Then just make sure that somewhere in your script you use the Quit command to terminate that instance. For example, here's a script that creates an instance of Excel, pauses for 10 seconds, uses the Quit command to close Excel, pauses for 10 more seconds, and then automatically terminates. If you run this script while Task Manager is up, you'll see two new processes get created—Wscript.exe (or Cscript.exe) and Excel.exe—and then, after a brief pause, you'll see first Excel.exe and then the script host process disappear.

Set objExcel = CreateObject("Excel.Application")
objExcel.Workbooks.Add
objExcel.Visible = True
Wscript.Sleep 10000
objExcel.Quit
Wscript.Sleep 10000

**Tip   **You'll sometimes see script writers set their object reference to Nothing, something like this:

Set objExcel = CreateObject("Excel.Application")
objExcel.Workbooks.Add
objExcel.Visible = True
Wscript.Sleep 10000
Set objExcel = Nothing

This releases the object reference (that is, objExcel will no longer refer to an instance of Microsoft Excel), but it doesn't terminate Excel; in fact, Excel.exe continues to run as though nothing happened, simply because nothing did happen. If you want to get rid of Microsoft Excel (um, in a scripting sense, that is), you have to use the Quit command.

Opening an Excel Spreadsheet

So far we've managed to create an instance of Microsoft Excel and then immediately terminate that instance. For some of you, that might be good enough. If so, feel free to grab your coat and head for home. Thanks for sticking with us this far. Others, however, might want to do something a bit more exotic, something like, say, actually reading data from a spreadsheet.

To do that, we need to do three things:

  1. Create an instance of Microsoft Excel. We've already mastered this part of the process.
  2. Open the desired spreadsheet.
  3. Read in the data.

Let's jump to step 2 and see if we can open the spreadsheet. In the Excel object model (for details, see the Excel Object Model Overview on MSDN), spreadsheets are contained within the Workbooks object. Believe it or not, you already knew that; in the first script we showed you. You used this line of code to add a new workbook to the collection:

objExcel.Workbooks.Add

To open a spreadsheet, we need to create an instance of the Workbooks collection, and then use the Open method to open the spreadsheet. That sounds complicated, but counting the CreateObject call that creates an instance of Excel, it takes just two lines of code:

Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("C:\Scripts\New_users.xls")

That's it: We create an instance of Excel, and then the line in boldface opens the file C:\Scripts\New_users.xls. Want to open a different .xls file? No problem. Just replace C:\Scripts\New_users.xls with the appropriate file path. Of course, needless to say, the script won't work unless you actually have a file named New_users.xls in the C:\Scripts folder. And it won't work, either, unless you have Excel installed on your computer. (If you thought you could avoid buying Microsoft Office simply by running this script, well, sorry to disappoint you.)

**Note   **Are there other ways to open a spreadsheet programmatically? Yes. In fact, there are other ways to do many of the tasks we'll accomplish in this column. In the interests of time and space, however, we'll use methods that are easiest for newcomers to Excel scripting. If you're interested in learning about others ways to carry out these same tasks, check out the Excel object model documentation.

Reading an Excel Spreadsheet

Now that we know how to open a spreadsheet, let's talk about what that spreadsheet should look like before we try retrieving data from it. You can do some pretty fancy programming with Excel. For example, you could take a spreadsheet that you knew absolutely nothing about and meticulously work your way around it, figuring out everything on the fly. That's fine, but it also requires a lot of additional code. Therefore, we're going to cheat a little when we create our spreadsheet, and I'm going to recommend that you cheat the same way when setting up your new user spreadsheet:

  • We're going to assume that column headings for the various user attributes are in row 1.

  • We're going to assume that data for the individual users begins in row 2.

  • We're going to assume that there are no blank rows anywhere in the data. In other words, if we have data for User A in row 2, we won't leave row 3 blank and then put the data for User B in row 4. Instead, the data for User B will appear in Row 3.

  • We'll make sure that a mandatory attribute appears in column 1. A mandatory attribute is an attribute that every new object must have; in the case of user account objects, that means a CN and a sAMAccountName. If we make, say, CN the first column, then every user must have an entry in that column; if there's no entry in row 13, column 1, we can assume we've reached the end of the data. (Or that we made a mistake when entering the data. But what are the odds of something like that happening?)

    By contrast, if we put an optional attribute (such as telephoneNumber) in column 1, then finding out that row 13, column 1 is empty wouldn't tell us much: it could mean that we've reached the end of the data, but it could also mean that this particular user simply doesn't have a phone number.

Here's what an abbreviated spreadsheet for creating user accounts might look like:

Figure 4. A sample spreadsheet for creating user accounts

If you take a close look at our sample spreadsheet, you'll see that we have four columns and four rows. You'll also see that the CN for our new user is stored in column 1, the SAM Account name in column 2, the first name in column 3, and the last name in Column 4.

**Note   **Like the preceding example, your spreadsheet probably lists columns as A, B, C, and D instead of 1, 2, 3, and 4. That won't make any difference; the script will work just fine regardless. However, if it would be easier for you to view column headings as numbers, do this: In Excel, click on Tools, then click Options. In the Options dialog box, on the General tab, select R1C1 reference style and then click OK. Voila! Column headings are now number rather than names. Or at least that's how you do this in Office 2003 and Office XP. If you have a previous version of Office, the steps might vary slightly.

So where is the data for our first user? Using the RF1C1 notation, it's right here:

CN SAM Account Name First Name Last Name
R2C1 R2C2 R2C3 R2C4

Notice the pattern? The row number remains the same, but the column number changes each time. What about the first two users in the spreadsheet?

CN SAM Account Name First Name Last Name
R2C1 R2C2 R2C3 R2C4
R3C1 R3C2 R3C3 R3C4

The only difference is that the data for User 1 is found in row 2, while the data for User 2 is found in the next row. That suggests that we could read the data in cells R2C1 through R2C4 and get all the available information for User 1. And what if we wanted the information for the next user? All we'd have to do is drop down a row and read the data found in columns 1–4. And to get the information for the next user, we'd just have to drop down another row. Elementary, my dear Watson.

In fact, we now know how to get all the data out of the spreadsheet, in part because we have some inside information. We don't know how many rows there are in the spreadsheet, but we do know that:

  • The first row containing data is row 2. (Row 1 is just a header row.)
  • The data is only found in columns 1–4. (And we even know which data is found in each column.)
  • If we encounter an empty cell in column 1, then we must be at the end of the data.

Without writing a single line of code, how would we go about grabbing data out of the spreadsheet? Well:

We'd start in row 2, column 1 and get the CN for the user. We'd then grab the data in row 2, column 2 (SAM Account Name); row 2, column 3 (first name); and row 2, column 4 (last name).

We'd then drop down to row 3, column 1 and get the CN for the next user. What if there wasn't a CN for the next user (that is, what if the cell is empty)? In that case, we're done; we've got all the data, and it's time to go do something else. If there is a value, we grab it, then retrieve the data from columns 2, 3, and 4. We then drop down to row 4, and start the process all over again.

Sounds pretty easy, doesn't it? Surprisingly enough, the actual code is just as easy. Here's a script that reads through an Excel spreadsheet, and echoes the values found in each cell. Let's take a look at the code, and then talk about what it does:

Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open _
    ("C:\Scripts\New_users.xls")
intRow = 2
Do Until objExcel.Cells(intRow,1).Value = ""
    Wscript.Echo "CN: " & objExcel.Cells(intRow, 1).Value
    Wscript.Echo "sAMAccountName: " & objExcel.Cells(intRow, 2).Value
    Wscript.Echo "GivenName: " & objExcel.Cells(intRow, 3).Value
    Wscript.Echo "LastName: " & objExcel.Cells(intRow, 4).Value
    intRow = intRow + 1
Loop
objExcel.Quit

We've already discussed the first couple lines of code, so let's skip to this one: intRow = 2. As you might expect, intRow is a variable that represents the current row in the spreadsheet. Why did we set intRow to 2 rather than to 1? That's easy: row 1 contains our column headings. The actual data doesn't start until row 2.

Next we create a Do Loop that runs until the value in cell (intRow, 1) is equal to nothing. What does that mean? Well, when we start the script, intRow is equal to 2; what we do right off the bat is check to see what the value of row 2, column 1. If it's nothing, that is, if no CN has been entered, we exit the loop. We're assuming we've reached the end of the data. But what if there is data in row 2, column 1? No problem. We enter the loop and run the rest of the code.

Incidentally, in this sample script all we do is echo the values found in columns 1, 2, 3, and 4. In a minute we'll show you how to do something a bit more interesting, but for our first crack at Excel scripting we'll keep it simple.

And so**what happens next? (I know, the suspense is killing you, isn't it?) Well, first we increment intRow by 1. Why? Remember, when we first entered the loop, intRow was set to 2 to ensure that we grabbed all the data from row 2. Having done that, we now want to grab all the data from row 3. If we increment intRow by 1, it will magically transform from a 2 to a 3, and we'll be ready to read data from row 3. After we do that, the intRow = intRow + 1 statement will change intRow to 4, and we'll be ready to loop around and read data from row 4. Make sense?

We then loop back to the Do Until statement to see if (intRow, 1)—which now means row 3, column 1—is empty. If it's not, we enter the loop and read the data from row 3. If it is empty, we exit the loop and go to the last line of code, which simply terminates Excel.

If you run the script against the sample spreadsheet we looked at earlier, you should get output similar to this:

CN: Myer Ken
sAMAccountName: KenMyer
GivenName: Ken
LastName: Myer
CN: Jones TiAnna
sAMAccountName: TiAnnaJones
GivenName: TiAnna
LastName: Jones
CN: Smith Ben
sAMAccountName: BenSmith
GivenName: Ben
LastName: Smith

Could there be anything more exciting?

Okay, now that you mention it, I suppose using this data to create some user accounts would be a bit more exciting, wouldn't it? Fine. Let's grab some data from the spreadsheet, then use that data to create some new user accounts. That might sound a bit daunting, but believe it or not, we've already taken care of the hard part. Now we just need to add in a little ADSI code to create the accounts.

Creating a User Account

I'm not going to talk about ADSI in this column. For more information on ADSI, see either the ADSI SDK or the Windows 2000 Scripting Guide. For now, just take my word for it that this code will create a user account in the Finance OU in fabrikam.com:

Set objOU = GetObject("ou=Finance, dc=fabrikam, dc=com")
Set objUser = objOU.Create("User", "cn=Myer Ken")
objUser.sAMAccountName = "myerken"
objUser.GivenName = "Ken"
objUser.SN = "Myer"
objUser.AccountDisabled = FALSE
objUser.SetInfo

What we're going to do next is take this code and merge it with our read-from-Excel script. How do we do that? Well, for starters, we simply replace hard-coded values in the ADSI portion of the script with the appropriate value from our spreadsheet. For example, take this line of ADSI code:

objUser.GivenName = "Ken"

We know that the user's first name (the GivenName attribute) can be found in column 3 of whatever row we're looking at. All we have to do, then, is toss out the hard-coded value "Ken" and replace it with the appropriate location on the spreadsheet:

objUser.GivenName = objExcel.Cells(intRow, 3).Value

After that, we just take our original script and throw out all the lines that echo things to the screen, replacing those lines with these new ones that set the value of user account attributes. Let's combine our Excel script with our ADSI script and see what we end up with:

Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open _
    ("C:\Scripts\New_users.xls")
intRow = 2
Do Until objExcel.Cells(intRow,1).Value = ""
    Set objOU = GetObject("ou=Finance, dc=fabrikam, dc=com")
    Set objUser = objOU.Create _
        ("User", "cn=" & objExcel.Cells(intRow, 1).Value)
    objUser.sAMAccountName = objExcel.Cells(intRow, 2).Value
    objUser.GivenName = objExcel.Cells(intRow, 3).Value
    objUser.SN = objExcel.Cells(intRow, 4).Value
    objUser.AccountDisabled = FALSE
    objUser.SetInfo
    intRow = intRow + 1
Loop
objExcel.Quit

This is way too easy. Previously our Excel script echoed values to the screen:

Wscript.Echo "LastName: " & objExcel.Cells(intRow, 4).Value

Now, about all we've done is replace Wscript.Echo with the appropriate ADSI command:

objUser.SN = objExcel.Cells(intRow, 4).Value

That's all there is to it.

Where Do We Go From Here?

Admittedly, this was a pretty simple example: only four attributes, and all the users were being created in the same OU. But if you want to include additional attributes (department, telephoneNumber, homeDirectory, whatever), then just create additional columns in your spreadsheet and add the appropriate lines of code. For example, if you put the user's phone number on column 5, then add this line of code to your script:

objUser.telephoneNumber = objExcel.Cells(intRow, 5).Value

And what if you want to create users somewhere other than the Finance OU? Again, no problem. Put the OU name in a column (let's say column 6), and then adjust the line of code that connects to the desired OU:

Set objOU = GetObject _
    ("ou=" & objExcel.Cells(intRow, 6).Value & _
        ", dc=fabrikam, dc=com")

Is that cool or what?

Will things always work this smooth? In general, yes, but not always. After all, ADSI does have a few eccentricities (things like multi-attribute values and bitmask values). If you need to set multi-value or bitmask attributes, those particular lines of code aren't as straightforward as doing this:

objUser.otherTelephone = objExcel.Cells(intRow, 10).Value

Instead, you'll have to do something along the lines of this:

objUser.PutEx ADS_PROPERTY_UPDATE, "otherTelephone",
    Array(objExcel.Cells(intRow, 10).Value)

But you'll figure it out without too much trouble. Just check the ADSI SDK and the Windows 2000 Scripting Guide for details.

Next month we'll show you how to put data into an Excel spreadsheet. In the meantime, write to us at scripter@microsoft.com if you have any questions or comments about this. And don't worry: Just because you're using Excel in your scripts doesn't mean you have to start drinking Chai tea or using lace doilies; you're still as rugged an individualist as ever. Although a Chai tea would taste good right about now ...

 

Scripting Clinic

Greg Stemp has long been acknowledged as one of the country's foremost authorities on scripting, and has been widely acclaimed as a world-class... huh? Well, how come they let football coaches make up stuff on their resumes? Really? He got fired? Oh, all right. Greg Stemp works at... Oh, come on now, can't I even say that? Fine. Greg Stemp gets paid by Microsoft, where he tenuously holds the title of lead writer for the System Administration Scripting Guide.