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" ?>
<package>
<comment>
This script allows you to set a static IP address on the local computer
</comment>
   <job>
       <runtime>
       <description>
       This script allows you to set static IP addresses
       for a list of computers
       </description>

       <example>
       C:\cscript SetRemoteIP.wsf
       </example>

       <named
       name="File"
       helpstring="The name of the Excel file containing the
       list of computers and IP addresses"
       type="string"
       required="True"
       />

       </runtime>

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

       <script language="VBScript">
       <![CDATA[
       Option Explicit
       Dim objService, objEnumerator, objInstance, intStatus,
         strIPAddr, strSNMask, strXLFile, strComputer
       strSNMask=Array("255.255.255.0")

       If WScript.Arguments.Named.Exists("File")
       strXLFile=WScript.Arguments.Named.Item("File")
       Else
       WScript.Arguments.ShowUsage()
       WScript.Quit
       End If

       objXL.Visible=True
       objXL.workbooks.open strXLFile
       objXL.sheets("IP Addresses").Activate
       objXL.ActiveSheet.range("A1").Activate

       Do While objXL.ActiveCell.Value<>""
        strComputer=objXL.ActiveCell.Value
        strIPaddr=objXL.ActiveCell.OffSet(0,1).Value
       Set        
        objService=objLocator.ConnectServer(strComputer,
        "root\cimv2",,0,context)
       Set objEnumerator=objService.ExecQuery(
        "Select * From Win32_NetworkAdapterConfiguration _
        Where DatabasePath IS NOT NULL")

       strIPaddr=Array(strIPaddr)

       objService.Security_.impersonationlevel = 3

       For Each objInstance in objEnumerator
         intStatus = objInstance.EnableStatic(
         strIPaddr, strSNMask)
       Next
       objXL.ActiveCell.OffSet(1,0).Activate
      Loop

      WScript.quit

         ]]>
      </script>
   </job>
</package>

[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.

Featured

Most   Popular