Scripting School: Reader requests script to create office inventory

Scripting expert Christa Anderson offers advice for a reader who needs a script to create an inventory of his office's seating plan.

Editor's note: This is the 15th column in a continuing series on scripting that appears monthly on Feel free to send any scripting questions to the author, Christa Anderson, at

Many readers send emails either asking for scripting help or suggesting scripts you're looking for. Although I can't answer each individual email, I do read all of them and often use them as ideas for my column.

For instance, DougP recently requested a script to create an inventory of his office's seating plan, correlating user name and cubicle with computer name (and automatically grabbing the computer name to avoid mistakes). It's a good idea, especially since in large companies seating is often arranged by seniority, and the introduction of a new person to a team can result in a flutter of office reassignments.

While I don't know of such a script offhand, it wouldn't be hard to create. In fact, I'd like to refine Doug's design a bit. When I worked for a large company, we needed the office-location inventory. (Boy, did we need it! I got a lot of exercise looking for people whose offices had moved.)

Right after I started, I got an email from the group administrator asking me for my computer's serial number. I emailed it back to her, and she copied it into whatever form she stored this information in. A simpler method might have been for me to provide that information as part of an office inventory. An even simpler method might have been for me to update her record directly and save her the step.

In other words, let's go back to last month's column and take a look at that Excel spreadsheet we used to create user folders and update it with some more pertinent information about our user base.

What we want to do is gather a user's office number, computer name and the serial number from their computer. The requirements for such a script include:

  • It must gather the local computer name.
  • It must prompt users for their cubicle or office number.
  • It must make sure that the cubicle number is in the expected format.
  • It must make sure that the user double-checks the computer's serial number.
  • It must write the results of this inventory to a file, in the right place in that file.

Gathering local user and computer name

In the interest of reducing the error profile, let's automate this as far as we can. Although current VBScript programming interfaces are not capable of extracting information from a nameplate on the door, they can accurately get the name of the person who's running the script, as well as the name of the computer it's running on. We've used this interface (WshNetwork) before.

Two of this object's properties are UserName and ComputerName. We can create this object, read its properties, then assign those properties to variables. The UserName property will allow us to find the right row in our Excel spreadsheet of user data. The ComputerName property is the name of their computer—part of the requested inventory data.

Prompt user for information

The next step is to collect the requested data that we cannot get programmatically: the cubicle number and computer serial number. We've got a few requirements here. First, we want the data we're requesting, not an editorial opinion about the parentage of the employee's manager. In other words, we need to check the data type.

(It's not a perfect solution—after all, if we could verify the office number we wouldn't need to ask the user for it—but if our script only accepts input that looks like it could be an office number, then editorial opinions are less likely.) And since serial numbers are long, we'd better have the user enter the number twice, and then compare the results.

Incidentally, this is one of the few times our scripts will have a graphical interface. Since speed is less important than accuracy, we want to make sure that the script is as explicit as possible about what information is required. So we'll use the InputBox function to create a UI looking something like this:

As you can see, we're using that UserName property of WshNetwork to personalize the input box a bit.

The InputBox function doesn't allow us to include more than one text box in a dialog box, so this script will need to provide three: one to get the office/cubicle number, one to collect the serial number and one to confirm the serial number.

For the office number, our script will accept only integers, nagging the user for different input if the data type is incorrect. The nature of serial numbers makes it impossible to check the data type for that data, but we can make sure that the serial number is long enough (to make sure that the user hasn't misunderstood and provided the model number, or just put in garbage characters) and that the user has confirmed its accuracy.

Write the results to a file

Once we've gathered all the right information, our final step is to record it. For this to work, the script must:

  • Have write access to the Excel spreadsheet we're storing everyone's data in—which means that the user must have write access to this file.
  • Open the document only when the data we need has already been collected, not while waiting for Joe User to get out from under his desk copying the serial number off the computer. (Keeping the file open too long will lock out other people who need to edit it.)
  • Find the row in the sheet that corresponds to the current user and add the appropriate data.

Meeting the first requirement is a matter of putting the document on a file share. Yes, this means that users will have the ability to read this document if they go looking for it, but it will only contain information that's available to them anyway (presumably, your user base knows the first and last names of the people they work with). No sensitive data should be in this file.

To meet the second requirement, we'll create a subroutine that creates an instance of the File System Object and the Excel worksheet only after all the data is collected, then closes it as soon as the data is written. This will reduce the likelihood that two people will be attempting to write to the file at the same time. (It's possible, but a script can write data a lot faster than you can type, and you can automate the document's closing.)

For our third requirement, we're going to use the UserName property. We can know ahead of time which column contains a user's logon name, so we just need to look for the row in that column with a matching value. Once we have that, we can write the relevant data to the appropriate columns in that row.

Collecting inventory data is a simple job. It's also a pain in the neck. With an inventory script, you can automate the process of having users provide their own inventory information. This can get the information you need in a central location, without requiring you to personally visit every single person's office. In my next column, I'll walk you through how this script will work.

Access all of Christa Anderson's Scripting School columns here.

When Christa Anderson began working with Windows Server operating systems in 1992, she became increasingly interested in finding more efficient and flexible ways of performing routine tasks. Christa has written extensively about administrative scripting and taught technical sessions on the subject at conferences such as Comdex and CeBIT, helping people who had never done any scripting to write their own scripts in half a day. In addition to her interest in scripting Windows management, Christa is an authority on server-based computing and the program manager for Terminal Services licensing in Longhorn. If you have a scripting question for Christa, please e-mail her at

Dig deeper on Windows File Management



Enjoy the benefits of Pro+ membership, learn more and join.



Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: