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.
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
allows you to set static IP addresses
for a list of computers
helpstring="The name of
the Excel file containing the
list of computers and IP addresses"
Dim objService, objEnumerator,
"Select * From Win32_NetworkAdapterConfiguration
Where DatabasePath IS
Each objInstance in objEnumerator
intStatus = objInstance.EnableStatic(
[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.
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.