Hi,
I found this script out there on the net and it seems
perfect for what I want to do. That is Ping a series of IP
addresses and retern the info to an Excel Spreadsheet.
Unfortunatly, it uses the Select Case True method, which I
am not familliar with.
Here is the error I get when I try to run the script:
E:\Utilities\AdminTools\IP Ping.vbs(78, 2) Microsoft
VBScript runtime error: Type mismatch: '[string: "
Pinging ndcspi02."]'
And here is the code:
'IP Ping.vbs
'Ping multiple computers taken from a list in a text file
out put to an Excel Spreadsheet
' Requirments: List of IP Addresses in a file called (See
strFileName for the required text file)
' One IP Address per line
' Excel is needed
' Script was written in Windows XP using Wscript 5.6
' No blank line at the end of the script
'**********************************************************
********************************
Option Explicit
Const ForReading = 1
Const ForWriting = 2
Const ForAppending = 8
Dim strFileName : strFileName = "IPAddress.txt"
Dim Fso : Set Fso = CreateObject
("Scripting.FileSystemObject")
Dim oXLS : Set oXLS = WScript.CreateObject
("Excel.Application")
Dim WshShell : Set WshShell = createobject("wscript.shell")
Dim i
Dim intIndex
Dim oFile
Dim png
Dim strComputerIP()
Dim strIpAddress
Dim strPing
Dim strReply
Dim strRet
Dim strCname
If Not Fso.FileExists(strFileName) then
strRet = Msgbox("The file, " & strFileName & " is not
available" & vbCr _
& "The file must be located in the same folder as the
script." & vbCR _
& "Please check for the file <<" & strFileName
& ">in the folder" & vbCR _
& "<<" & Fso.GetParentFolderName
(Wscript.ScriptFullName) & ">>")
Wscript.Quit
End if
Set oFile = Fso.OpenTextFile(strFileName, 1)
'Configure Excel while leaving the spreadsheet hidden
oXLS.WorkBooks.Add
oXLS.Columns(1).ColumnWidth = 20
oXLS.Columns(2).ColumnWidth = 30
oXLS.Columns(3).ColumnWidth = 40
'Set column headers
oXLS.Cells(1, 1).Value = "IP Address"
oXLS.Cells(1, 2).Value = "Computer Name"
oXLS.Cells(1, 3).Value = "Return"
'Format text (bold)
oXLS.Range("A1:C1").Select
oXLS.Selection.Font.Bold = True
oXLS.Selection.Interior.ColorIndex = 1
oXLS.Selection.Interior.Pattern = 1 'xlSolid
oXLS.Selection.Font.ColorIndex = 2
'Left Align text
oXLS.Columns("B:B").Select
oXLS.Selection.HorizontalAlignment = &hFFFFEFDD ' xlLeft
intIndex = 2
i = 0
Do Until oFile.AtEndOfStream
Redim Preserve strComputerIP(i)
strComputerIP(i) = oFile.ReadLine
'Ping Ip Addresses
set png = WshShell.exec("ping -a -n 1 " & strComputerIP
(i))
do until png.status = 1 : wscript.sleep 10 : loop
strPing = png.stdout.readall
'NOTE: The string being looked for in the Instr is case
sensitive.
'Do not change the case of any character which appears on
the
'same line as a Case InStr. As this will result in a
failure.
Select Case True
Case InStr(strPing, "Request timed out", 1)
strReply = "Request timed out"
strCname = lcase(getcName(strPing))
Case InStr(strPing, "could not find host", 1)
strReply = "Host not reachable"
trCname = lcase(getcName(strPing))
Case InStr(strPing, "Reply from", 1)
strReply = "Ping Succesful"
strCname = lcase(getcName(strPing))
End Select
If strCName = "" then
strCname = "N/A"
End If
Call Show(strComputerIP(i), strCName, strReply)
i = i + 1
Loop
'Make the spreadsheet visable
oXLS.Visible = TRUE
Function GetcName(ByVal reply)
Dim C, tempcName
C = Instr(reply,"[")
If C = 0 Then Exit Function
TempcName = mid(reply,12,C-12)
'Used to remove the FQDN from the computer name.
GetcName = replace
(tempcName, ".ci.charlotte.nc.us", "",1)
End Function
Sub Show(strIP, strName, strValue)
oXLS.Cells(intIndex, 1).Value = strIP
oXLS.Cells(intIndex, 2).Value = strName
oXLS.Cells(intIndex, 3).Value = strValue
intIndex = intIndex + 1
oXLS.Cells(intIndex, 1).Select
End Sub