Mr. Script

Addressing the Issue, Part 2

This month, Chris shows you how to use an Excel spreadsheet to read and apply IP addresses and get your network connected.

Last month I looked at a modified version of a script that was sent to me by a reader. The purpose of the script was to change the IP address of the local machine. At the end of the column, I listed some ways to further improve the script by specifying additional IP settings, entering addresses from a pre-defined list, and enabling it to perform this operation on remote machines. This month’s script tackles the last two items:

<?xml version="1.0" ?>
This script allows you to set a static IP address on the local computer


       This script allows you to set static IP addresses
       for a list of computers

       C:\cscript SetRemoteIP.wsf

       helpstring="The name of the Excel file containing the
       list of computers and IP addresses"


       <object id="objXL" progid="EXCEL.Application"/>
       <object id=

       <script language="VBScript">
       Option Explicit
       Dim objService, objEnumerator, objInstance, intStatus,
         strIPAddr, strSNMask, strXLFile, strComputer

       If WScript.Arguments.Named.Exists("File")
       End If

       objXL.Visible=True strXLFile
       objXL.sheets("IP Addresses").Activate

       Do While objXL.ActiveCell.Value<>""
       Set objEnumerator=objService.ExecQuery(
        "Select * From Win32_NetworkAdapterConfiguration _
        Where DatabasePath IS NOT NULL")


       objService.Security_.impersonationlevel = 3

       For Each objInstance in objEnumerator
         intStatus = objInstance.EnableStatic(
         strIPaddr, strSNMask)




[To download this script, right-click this link and Save As....—Ed.]

Hmm… Different, But Interesting
As you can see, this script really just combines techniques we’ve already learned in this column over the years (you have been a faithful reader, haven’t you?). Well, don’t feel bad if you missed one or two issues—I’ll remind you of what you need to know. Essentially, I use the Excel object model to programmatically read the computer name from a spreadsheet and apply the appropriate IP address for each. In order for this to work, the spreadsheet must be in the proper format. Setting it up is easy. If you’re “hard-wiring” static IP addresses to a number of computers, chances are that you’re working with a sequential range. Column A of the spreadsheet contains the computer names. Column B contains the IP addresses. The worksheet is named “IP Addresses,” and it doesn’t contain a header row. When it comes to entering the IP addresses into the spreadsheet, you can save a lot of typing by putting the first address in the first row and dragging the corner of the cell down. Excel automatically increments the last octet of the IP address by one. Of course, you’ll want to verify these numbers before you run the script.

Putting It To Work
Once I have all the computer names and IP addresses entered into the spreadsheet, I use a Do…Loop to connect to the computers individually and set the IP address and subnet mask using WMI. For the purposes of this script, I’m only using a single class C subnet. However, if you have more than one subnet, it’s simple to put the appropriate subnet mask in the spreadsheet.

Depending upon how many computers are in your worksheet, this script might be running for quite a while. Remember, it has to individually connect to each computer, query that computer’s IP information and change it to the information specified in the spreadsheet.

Optional Optimizations
If you’re only changing a few computers, you could add a line that displays the success or failure of each operation. If you’re leaving this script to run overnight, you can add a line to have it log the success or failure for each computer—just in case someone accidentally turned off their computer when they went home.

Wrapping Things Up
Next month, I’ll will finish my look into using WMI to edit IP information by showing you how to set additional IP-related information, including DNS and WINS servers and a default gateway.

About the Author

Chris Brooke, MCSE, is a contributing editor for Redmond magazine and director of enterprise technology for ComponentSource. He specializes in development, integration services and network/Internet administration. Send questions or your favorite scripts to [email protected].


comments powered by Disqus

Subscribe on YouTube