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" ?>
<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.

comments powered by Disqus

Reader Comments:

Fri, Jul 18, 2003 Anonymous Anonymous

i need to try this

Wed, Aug 7, 2002 Anonymous Anonymous

Cants download script and would be nice if it was explained in more depth

Wed, Aug 7, 2002 Anonymous Anonymous

Can't download script and would be nice if it was explained in more depth.

Sat, Jul 27, 2002 Kirk Columbia, SC

I find that there is an error in the Query Portion if running on Windows 98. I leave off the 'Where DatabasePath IS NOT NULL' part of the query and it is actually able to find some objects.

Tue, Jun 25, 2002 Bob East Coast

be nice if the link to download the script worked. O-well, copy and paste it is.

Sat, Jun 22, 2002 Lisa USA

This script contains the same silly error as last months! Do you guys not check these? I guess you get what you pay for.

Add Your Comment Now:

Your Name:(optional)
Your Email:(optional)
Your Location:(optional)
Comment:
Please type the letters/numbers you see above

Redmond Tech Watch

Sign up for our newsletter.

I agree to this site's Privacy Policy.