Enable Users from an Excel File

You might find this hard to believe, but there have been times when people have actually criticized Microsoft software. It's true: not every product that we've released has been universally acclaimed as the greatest software product ever released. But that's the nature of the software business: sometimes you have a smash hit, and sometimes you have Microsoft Bob.

Microsoft Bob Trivia Notes. One of the people who worked on the Microsoft Bob project was Melinda French, who later became Melinda Gates. And no, we're not making any wisecracks about that.

Considering how quickly the product was dropped, Microsoft was initially so psyched about Microsoft Bob that we registered the domain name bob.com. Later, we traded that domain name for the domain windows2000.com.

And a sack of magic beans.

Oh, and one more thing: according to Raymond Chen, the code name for Microsoft Bob was "Utopia;" however, Microsoft's marketing department decided to call it "Microsoft Bob" instead. And no, we're not making any wisecracks about that, either. We think it speaks for itself.

The point is, you win a few, you lose a few. (For example, Time magazine called Microsoft Bob one of the 50 worst inventions of all time! That hurts.) But one other thing we discovered while researching this topic is this: no one ever criticizes Microsoft Excel. Sure, there might have been one or two people out there who didn't like Microsoft Bob, but everybody loves Excel. Perhaps more importantly, everybody uses Excel. When one of the authors worked at the University of Washington, he soon learned that Excel was the software of choice not only for spreadsheet applications but for pretty much everything else you could do with a computer: databases, scheduling, posters, you name it. You know how, when people are trying to sell something, they hang up a flier that has those little phone number tabs you're supposed to tear off and take with you? Without Excel, those fliers simply would not exist.

But don't hold that against Excel: it's still a great product.

At any rate, as a Microsoft Lync Server 2010 system administrator you might be thinking, "That's great, but I don't need to make up any fliers with phone number tabs on them. What I need to do is enable a bunch of users for Lync Server. And there's no way that Microsoft Excel can help me with that task." Well, you wouldn't think so, would you? But, then again, you wouldn't think that you could play Space Invaders using Microsoft Excel either. And yet …

Now, if you're puzzled about just how we're going to enable users for Microsoft Lync Server by using Microsoft Excel, well, you can stop puzzling: we aren't. What we are going to do is show you how you can read in user information from an Excel spreadsheet, then use that information to simultaneously enable a whole bunch of user accounts. In another article we did something similar: we showed you how to perform such tasks as enabling all the users in a given department, or enabling all the users with a specified job title. That was, and still is, pretty cool, and it's a great way to show off the capabilities of Lync Server's implementation of Windows PowerShell.

However, we're not sure how often you'll need to do something like enable all the users in the Finance department, or all the users who have the job title Help Desk Technician. In our view, anyway, a more likely scenario would be something like this: your organization has just bought out a smaller company, and new Active Directory user accounts have been created for all the employees who have been brought over from the smaller company. In turn someone has handed you a spreadsheet containing information about those new employees, and asked you to enable each of those users for Lync Server. These people have nothing in common; that is, they aren't from the same department, they don't have the same job title, they don't work in the same building. They're just a seemingly-random group of people who all need to be enabled for Lync Server.

Note. Before we go any further, we should note that, from time-to-time, you'll see articles similar to this one, articles in which PowerShell is used to do something like bulk enable a bunch of user accounts. The difference between this article and those articles is that, in those other scenarios, you're usually handed a comma-separated values (CSV) file instead of a spreadsheet. There's no doubt that working with a CSV file is easier than working with a spreadsheet. But let's be honest here: how often has someone (especially someone from the Human Resources department) handed you a comma-separated values file compared to the number of times someone has handed you a spreadsheet? That's what we thought. And that's why we went with the spreadsheet rather than the CSV file.

Of course, if you'd prefer to see an example of using data read from a CSV file, well, you came to the right place.

For starters, let's take a look at the spreadsheet we've been handed:

As you can see, there's nothing fancy here: it's just a spreadsheet with 3 columns and 4 rows. In the first row we have column headings: Name, Registrar Pool, and Sip Address. In rows 2 through 4, we have the user data. And that's pretty much it.

Note. Although we hate to tell people what to do (well, one of us hates to tell people what to do) it's important that you set your spreadsheet up the same way we have; that means you need to start listing user data in row 2, and not insert any blank row between users. Why no blank rows? Well, our script is designed to keep reading rows until it encounters a blank cell in column A; at that point, the script assumes it's read in all the data and automatically terminates. If you have a blank row between users, the script is going to terminate as soon as it encounters that blank row, and will never get around to enabling any users who appear after the blank row.

And yes, there are ways to work around that issue. But because we wanted to keep this script as simple as possible, we decided to skip the workarounds and not allow you to put blank rows in your data.

As for the script that's going to perform all the magic, it looks a little something like this:

$objExcel = New-Object -ComObject Excel.Application

$objExcel.Visible = $True

$objWorkbook = $objExcel.Workbooks.Open("C:\Scripts\NewUsers.xlsx")

$row = 2

do

    {

        $Name = $objExcel.Cells.Item($row,1).Value()

        $RegistrarPool = $objExcel.Cells.Item($row,2).Value()

        $SipAddress = $objExcel.Cells.Item($row,3).Value()

        Enable-CsUser -Identity $Name -RegistrarPool $RegistrarPool -SipAddress $SipAddress

        $row++

    }

until ($Name -eq $Null)

$objExcel.Quit()

$Null = & {

    [Runtime.Interopservices.Marshal]::ReleaseComObject($objExcel)

    [Runtime.Interopservices.Marshal]::ReleaseComObject($objWorkbook)

    }

[GC]::Collect()

As a matter of fact we can explain how the script works. Here's how the script works: We start things off by creating an instance of the Excel.Application object; that's the name of the COM object used for working with Microsoft Excel. This object reference is given the name $objExcel.

Note. Although you already know this, we should still mention that you must have Microsoft Excel installed on your computer in order for this script to work; if you don't have Excel installed you won't be able to instantiate the Excel.Application object. Wouldn't it be cool if we could figure out a way for you to use Excel without having to actually buy Excel? Well, considering that we have mortgages to pay and kids to put through school, there's an easy answer to that: no.

After we've created our instance of Excel, we then use this line of code to make that instance visible onscreen:

$objExcel.Visible = $True

Admittedly, that line of code is optional: the script will work just fine if Excel remains invisible. However, it can be very … challenging … to try and debug a script that uses Excel if you can't even see Excel in the first place. We recommend that, when you're first testing your script, you make Excel visible. After you've got the script working, well, at that point it's up to you.

Note. And yes, we do recommend that you test your script before you actually begin using it. Remember, this code is written by the same people who write a daily Lync Server PowerShell Haiku. We'd be a little nervous about that, too.

At this point, our screen will look something like this:

Nice, but there's at least one thing missing: the spreadsheet containing all of our user data. But don't worry; we already thought of that. This line of code opens the spreadsheet C:\Scripts\NewUsers.xlsx:

$objWorkbook = $objExcel.Workbooks.Open("C:\Scripts\NewUsers.xlsx")

Note. For the sake of simplicity we did hardcode in the script name. And yes, we could have set things up so that you could open any Excel spreadsheet using this script. One easy way to do that is to enter the path to the spreadsheet as a command-line argument when starting the script. For example, if the path to your script is C:\Scripts\EnableUsers.ps1 you could start the script using this command:

C:\Scripts\EnableUsers.ps1 "C:\Scripts\NewUsers.xlsx"

In turn, your command for opening the spreadsheet would then look like this, with $args[0] representing your command-line argument:

$objWorkbook = $objExcel.Workbooks.Open($args[0])

But that was too much trouble, so we decided to just leave it out altogether.

Now we get to the fun part: actually reading the spreadsheet cells and making use of those values. (Hey, we're technical writers at Microsoft: this is about as much fun as we ever get to have.) In order to do that, the first thing we do is set the value of a variable named $row to 2:

$row = 2

We're going to use this variable to keep track of the current row in the spreadsheet. So why do we set $row to 2 instead of to 1? You got it: because the data we're interested in starts in row 2. Row 1 is just a bunch of column headings and those headings, at least for this script, are something we couldn't care less about.

That brings us to this block of code:

do

    {

        $Name = $objExcel.Cells.Item($row,1).Value()

        $RegistrarPool = $objExcel.Cells.Item($row,2).Value()

        $SipAddress = $objExcel.Cells.Item($row,3).Value()

        Enable-CsUser -Identity $Name -RegistrarPool $RegistrarPool -SipAddress $SipAddress

       $row++

    }

until ($Name -eq $Null)

This is where all the magic happens. What we've done here is set up a do until loop that runs until a variable named $Name is equal to a null value:

until ($Name -eq $Null)

What does that mean? Well, as you'll see in just a second, $Name is the variable we use to store the name (or, more correctly, the Identity) of the user account being enabled. When we first start the loop, $Name will be equal to the name (technically, the Active Directory display name) of the first user: Ken Myer. When the loop runs through its second iteration, $Name will be equal to the name of the second user: Pilar Ackerman. Eventually, we’ll hit a blank row, where there won't be a user name. At that point, $Name will be equal to a null value, and our loop will automatically terminate.

And yes, that is how the script knows that it has cycled through all the users in the spreadsheet and can now call it a day.

Inside the loop, and as we implied, the first thing we do is retrieve the name of the initial user in the spreadsheet; that's what this line of code is for:

$Name = $objExcel.Cells.Item($row,1).Value()

How does this work? Well, as you might recall, $objExcel is the object reference to our instance of Microsoft Excel. The syntax $objExcel.Cells.Item($row,1).Value() simply says that we should retrieve the value found in the cell row 2 ($row), column 1. Needless to say, that's the cell containing the name of the first user in the spreadsheet.

And then we use these two lines of code to: 1) store the name of the user's Registrar pool in a variable named $RegistrarPool; and, 2) store the SIP Address for the user in a variable named $SipAddress:

$RegistrarPool = $objExcel.Cells.Item($row,2).Value()

$SipAddress = $objExcel.Cells.Item($row,3).Value()

And then, at long last, we use this line of code to enable the first user in the spreadsheet for Microsoft Lync Server:

Enable-CsUser -Identity $Name -RegistrarPool $RegistrarPool -SipAddress $SipAddress

As you might expect, there's nothing fancy here, either: this is just a simple command built around the Enable-CsUser cmdlet. For the user's Identity we use, well, the user's Identity: the value stored in $Name. For the Registrar pool we use the value stored in $RegistrarPool, and for the SIP Address – well, you get the idea. Short and sweet, just like all good scripts should be.

Of course, having said that, it's true that we could have made this script even shorter and even sweeter. For example, if we only have one Registrar pool in our organization we could leave out the Registrar pool column in the spreadsheet, and hardcode the name of the pool in our call to Enable-CsUser:

Enable-CsUser -Identity $Name -RegistrarPool atl-cs-001.litwareinc.com -SipAddress $SipAddress

Likewise, we could have asked Lync Server to construct the SIP addresses for us; that would mean we wouldn't have to pre-construct SIP addresses, and wouldn't have to read in those addresses from the spreadsheet. For example, if we wanted SIP addresses that were the same as the users' email addresses we could have used code similar to this:

Enable-CsUser -Identity $Name -RegistrarPool atl-cs-001.litwareinc.com –SipAddressType EmailAddress

So why didn't we go this simpler route? Two reasons. First, we wanted to show off a little. We knew you wouldn't be impressed by a script that read in data from just one column in a spreadsheet; heck, Microsoft Bob could probably do that. But a script that could read data from three columns in a spreadsheet? Even Clippy the Paperclip would be impressed by that.

Clippy the Paperclip Trivia. Clippy's real name is, or was, Clippit. Clippy is just a nickname.

Second, we wanted to make sure that we showed you how you could read data from three columns in a spreadsheet. (Just in case Clippy ever comes by and says, "You can't do anything that would ever impress me.") Let's take a look at the line of code that retrieves the name of the Registrar pool, the data stored in column 2 in the spreadsheet:

$RegistrarPool = $objExcel.Cells.Item($row,2).Value()

Notice the address of the cell in this command: ($row,2). The 2, of course, is the column number. Based on that, when we retrieve the value of the SIP address, which is stored in column 3, the cell address should be $row, 3. And, lo and behold, it is:

$SipAddress = $objExcel.Cells.Item($row,3).Value()

OK, so we've now enabled the first user in the spreadsheet; time to go back to the top of the loop and enable the second user, right?

Wrong. There's still one important line of code we need to execute:

$row++

Yes, we know: it doesn't look like an important line of code, does it? But looks can be deceiving. Remember, $row is the variable that contains the number of the current row. The first time through the loop $row is equal to 2; that means we're going to retrieve data for, and then enable, the user in row 2 of the spreadsheet. That's good; that's just exactly what we want to do.

But if we jump back to the top of the loop right now, $row will still be equal to 2. That's no big deal, except for the fact that it will once again read user data from row 2 in the spreadsheet, and once again try to enable the user in the row 2. And then, when that fails, it will loop around and – that's right – read user data from row 2 in the spreadsheet, and try once again to enable the user in the row 2. And when that fails – well, you get the idea. It's like the movie Groundhog Day, except we don’t think they actually enabled users for Microsoft Lync Server in Groundhog Day.

Note. Although, then again, they might have. We don't know for sure; some people have a phobia about movies that star Bill Murray.

Editor’s Note. And others actually like that movie. Those who like it can confirm that no users were enabled for Lync Server in the movie.

Fortunately, by the time we go back to the top of the loop $row will no longer be equal to 2; instead, it will be equal to 3. How do we know that? Because the command $row++ is PowerShell's way of incrementing the value of $row by 1; the ++ operator simply adds 1 to the aforementioned variable. As a result, the second time through the loop $row will be equal to 3. The third time through the loop $row will be equal to 4. And so on and so on.

Of course, all good things must come to an end, which means that, sooner or later, all the users in the spreadsheet will be enabled for Lync Server and we'll automatically exit the loop. That leaves us with this block of code:

$objExcel.Quit()

$Null = & {

    [Runtime.Interopservices.Marshal]::ReleaseComObject($objExcel)

    [Runtime.Interopservices.Marshal]::ReleaseComObject($objWorkbook)

    }

[GC]::Collect()

We're not going to explain this code in any detail; suffice to say that we use these lines to ensure that when we quit Excel (using the command $objExcel.Quit()) we really do quit Excel. When calling COM objects from PowerShell it's possible for the application to disappear from the screen, but continue to run in the background; in this case, that means you could start Task Manager and see the process excel.exe still chugging merrily along. To short-circuit that problem, we use some .NET Framework methods to null out all of our Excel object references ($objExcel and $objWorkbook), then use the .NET Framework Garbage Collector to free up all the memory that had been allocated to our dearly-departed instance of Excel.

And there you have it: reading in user account data from a spreadsheet, then using that data to enable users for Microsoft Lync Server. Oh, if only Microsoft Bob was here to see this!

Note. In case you're wondering, March 31, 1995 was the first "Microsoft Bob Day." If you have stories about all the wild and crazy times you had on Microsoft Bob Day, please send them to cspshell@microsoft.com and we'll – you know, on second thought, never mind. If you really did have wild and crazy times on Microsoft Bob Day, well, we're not so sure we want to hear about those after all.

P.S. If you're counting down the days until the next Microsoft Bob Day, as most of us are, well, this PowerShell command should help:

((Get-Date "3/31/2011") - (Get-Date)).Days

Enjoy!