Scripting School: Writing a script for an office inventory system |
 |
By Christa Anderson, Contributor
31 Jul 2006 | SearchWinComputing.com |
|
|
Editor's note: This is the 16th column in a continuing series on scripting that appears monthly on SearchWinSystems.com. Feel free to send any scripting questions to the author, Christa Anderson, at editor@searchwincomputing.com.
In last month's column, we outlined the requirements for an inventory system that would correlate the user name, computer name and cubicle name when run, and then add all this information to an Excel spreadsheet stored on a network share. This month, we're going to write it.
As stated in the previous column, the script has five requirements:
- 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.
Now that I look at this again, I'm going to add one more requirement: This script should start by telling the user what information they will need to supply to the script and what information the script will collect on its own. Giving this information to the user will both alert them to the likelihood that they'll need to crawl on the floor to get the required information and let them know exactly what information the script will collect automatically (in case anyone is worried about Big Brother).
Providing information to the user
This doesn't have to be complicated; we're just going to create a message box with the necessary information. The following privacy notice will do nicely. What's shown here is simply a dialog box with an OK button on it.
MsgBox("This script will match your login name and computer name with your PC serial number and cubicle number."&Chr(13)&Chr(10)&Chr(10)&"No other information will be gathered.", vbOKOnly, "Privacy Notice")
The character numbers here add line feeds (Chr(10)) and carriage returns (Chr(13)) to improve the readability of the dialog box.
Gathering the user name and computer name
Gathering the user name and computer name are tasks we've done before when mapping printers to a particular user or computer. We don't need to ask the user for anything, we just need to collect the information so it's ready to input to the spreadsheet.
Set oNet = Wscript.CreateObject("Wscript.Network")
sUser = oNet.UserName
sComputer = oNet.ComputerName
Without any action from the user, these variables are now populated. (Strictly speaking, we didn't have to assign these values to variables; I just prefer doing that because it saves on typing errors.)
Prompting the user for data
In this stage, we not only need to prompt the user for data, we need to double-check it for accuracy. Although we can't know if the user is providing the wrong office number, we can know if they provide an entry that's either too long or too short, or one that contains characters not found in your office numbering system. For our purposes here, we'll use a couple of input boxes, since a VBScript input box cannot take more than one string of input at a time. This example uses the Do…Until conditional statement to ensure that the values of the orginal prompt and the confirmation are equal.
Do
s1 = InputBox("What is your cubicle number?", "Location")
s2 = InputBox("Please confirm your cubicle number.", "Location")
Loop Until s1 = s2
Because the statement to be tested for is at the end of the Do…Until loop, the conditions within that loop will have to occur at least once. If we phrased it so that the While s1 = s2 was at the beginning, that portion of the script would never run. s1 and s2 are equal at the beginning—they have no value. Cancelling one box will result in an unequal value, so the loop will continue until the values are equal. The exact same format will work for the serial number: just use new variables for the serial number inputs (so you don't override the cubicle number inputs) and test to be sure that they're equal.
Do
s3 = InputBox("What is your computer's serial number?", "Hardware Inventory")
s4 = InputBox("Please confirm your computer's serial number.", "Hardware Inventory")
Loop Until s3 = s4
Writing the data to a file
The last step is to write the data to a file stored on a network share. At this point, our task is to find the first empty row in the file, then write the data there in the appropriate rows. For example:
Sub WriteData
Set oExcel = CreateObject("Excel.Application")
Set oSheet = oExcel.Workbooks.Open("C:scriptsnewtest.xls")
Do
'oExcel.Cells(iRow,1).Value
iRow = iRow+1
Loop Until oExcel.Cells(iRow,1).Value = ""
oExcel.Cells(iRow,1)= sUser
oExcel.Cells(iRow,2) = sComputer
oExcel.Cells(iRow,3) = s1
oExcel.Cells (iRow,4) = s3
oExcel.Quit
End Sub
Access all of Christa Anderson's Scripting School columns here.
| ABOUT THE AUTHOR: |
|
Christa Anderson
A Terminal Services MVP, Christa Anderson is the
strategic technology manager for visionapp She formerly was program manager for the Microsoft Terminal Services team. She is an
internationally known authority on scripting, the author of Windows Terminal Services, The Definitive Guide to MetaFrame
XP, and co-author of the book Mastering Windows 2003 Server. If you have a scripting question for Christa, please e-mail her at editor@SearchWincomputing.com. She often uses these emails as fodder for her scripting columns.
|
|
|
|
|
|
