Ping from a spreadsheet and write results

8 Mar 2007 10:33 AM

Simon G

Hello All,

I'm looking for a script that will take the computer name from a cell, ping
it, and then write to the next column "yes" or "no" depending if it reutned a
valid response

any idea?

8 Mar 2007 7:05 PM

urkec

'on Windows XP and Windows Server 2003

Set objWMIService = GetObject _
   ("winmgmts:\\.\root\cimv2")

set XL = CreateObject ("Excel.Application")
XL.Visible = true
XL.DisplayAlerts = false

set Sheet = XL.Workbooks.Open _
   ("C:\test.xls").Sheets (1)

i = 1

'column A contains IP addresses

do
set Cell = Sheet.Cells (i, 1)
if Cell <> "" then
call Ping
else
'Stop at first empty cell
exit do
end if
i = i + 1
loop

WScript.Echo "Done"

sub Ping

set colItems = objWMIService.ExecQuery _
   ("Select * from Win32_PingStatus " & _
   "Where Address = '" & Cell & "'")

for each objItem in colItems
if objItem.StatusCode = 0 then
Sheet.Cells (Cell.Row, Cell.Column + 1) = "Yes"
else
Sheet.Cells (Cell.Row, Cell.Column + 1) = "No"
end if
next

end sub

This script opens an Excel Workbook, takes computer names from column A of
Sheet1 , pings them, and writes "Yes" or "No" to column B depending on the
result. It stops at the first empty cell in column A. It leaves the workbook
open and doesn't save it. If you are not running XP or 2003 you can change
the sub to use DOS Ping command instead of Win32_PingStatus WMI class.

I hope this helps some.

--
urkec

Show quote

"Simon G" wrote:

> Hello All,
>
> I'm looking for a script that will take the computer name from a cell, ping
> it, and then write to the next column "yes" or "no" depending if it reutned a
> valid response
>
> any idea?

9 Mar 2007 4:04 PM

Simon G

That works lovely.

Could i ask you to see if it can be modified slightly. What i would like
is....

If cell is populated then ping
If cell is blank then skip to next cell
If cell is "STOP" then script stops

Is that possible?

Show quote

"Simon G" wrote:

> Hello All,
>
> I'm looking for a script that will take the computer name from a cell, ping
> it, and then write to the next column "yes" or "no" depending if it reutned a
> valid response
>
> any idea?

10 Mar 2007 8:36 AM

urkec

'on Windows XP and Windows Server 2003

Set objWMIService = GetObject _
("winmgmts:\\.\root\cimv2")

set XL = CreateObject ("Excel.Application")
XL.Visible = true
XL.DisplayAlerts = false

set Sheet = XL.Workbooks.Open _
("C:\scripts\test.xls").Sheets (1)

i = 1

'column A contains IP addresses

do
set Cell = Sheet.Cells (i, 1)
if UCase (Cell) = "STOP" then
exit do
elseif Cell <> "" then
call Ping
end if
i = i + 1
loop

WScript.Echo "Done"

sub Ping

set colItems = objWMIService.ExecQuery _
("Select * from Win32_PingStatus " & _
"Where Address = '" & Cell & "'")

for each objItem in colItems
if objItem.StatusCode = 0 then
Sheet.Cells (Cell.Row, Cell.Column + 1) = "Yes"
else
Sheet.Cells (Cell.Row, Cell.Column + 1) = "No"
end if
next

end sub

--
urkec

Show quote

"Simon G" wrote:

> That works lovely.
>
> Could i ask you to see if it can be modified slightly. What i would like
> is....
>
> If cell is populated then ping
> If cell is blank then skip to next cell
> If cell is "STOP" then script stops
>
> Is that possible?
>
> "Simon G" wrote:
>
> > Hello All,
> >
> > I'm looking for a script that will take the computer name from a cell, ping
> > it, and then write to the next column "yes" or "no" depending if it reutned a
> > valid response
> >
> > any idea?

12 Mar 2007 8:09 PM

OldDog

On Mar 10, 3:36 am, urkec <u***@discussions.microsoft.com> wrote:
Show quote

> 'on Windows XP and Windows Server 2003
>
> Set objWMIService = GetObject _
> ("winmgmts:\\.\root\cimv2")
>
> set XL = CreateObject ("Excel.Application")
> XL.Visible = true
> XL.DisplayAlerts = false
>
> set Sheet = XL.Workbooks.Open _
> ("C:\scripts\test.xls").Sheets (1)
>
> i = 1
>
> 'column A contains IP addresses
>
> do
> set Cell = Sheet.Cells (i, 1)
> if UCase (Cell) = "STOP" then
> exit do
> elseif Cell <> "" then
> call Ping
> end if
> i = i + 1
> loop
>
> WScript.Echo "Done"
>
> sub Ping
>
> set colItems = objWMIService.ExecQuery _
> ("Select * from Win32_PingStatus " & _
> "Where Address = '" & Cell & "'")
>
> for each objItem in colItems
> if objItem.StatusCode = 0 then
> Sheet.Cells (Cell.Row, Cell.Column + 1) = "Yes"
> else
> Sheet.Cells (Cell.Row, Cell.Column + 1) = "No"
> end if
> next
>
> end sub
>
> --
> urkec
>
>
>
> "Simon G" wrote:
> > That works lovely.
>
> > Could i ask you to see if it can be modified slightly. What i would like
> > is....
>
> > If cell is populated then ping
> > If cell is blank then skip to next cell
> > If cell is "STOP" then script stops
>
> > Is that possible?
>
> > "Simon G" wrote:
>
> > > Hello All,
>
> > > I'm looking for a script that will take the computer name from a cell, ping
> > > it, and then write to the next column "yes" or "no" depending if it reutned a
> > > valid response
>
> > > any idea?- Hide quoted text -
>
> - Show quoted text -

This is in no way more functioanal than urkec's script, but I added
some fetures.
1. Headers for the columns. A1 is Server Names, B1 is Pings ?
2. If Yes the cell font is green and if No the font is red.

'on Windows XP and Windows Server 2003
Set objWMIService = GetObject _
("winmgmts:\\.\root\cimv2")

set XL = CreateObject ("Excel.Application")
XL.Visible = True
XL.DisplayAlerts = false

Set Book1 = XL.Workbooks.Open _
("C:\scripts\test.xls")
book1.activate

i = 2

'column A contains IP addresses
Set objWorksheet = book1.Worksheets(1)
objWorksheet.Range("A1:B1").Font.Bold = True
objWorksheet.Cells(1, 1).Value = "Server Name"
objWorksheet.Cells(1, 2).Value = "Pings?"
objWorksheet.Cells.EntireColumn.AutoFit
Do
set Cell = objWorksheet.Cells (i, 1)
if UCase (Cell) = "STOP" Then
exit Do
elseif Cell <> "" Then
call Ping
end If
i = i + 1
Loop

WScript.Echo "Done"

sub Ping

set colItems = objWMIService.ExecQuery _
("Select * from Win32_PingStatus " & _
"Where Address = '" & Cell & "'")

for each objItem in colItems
if objItem.StatusCode = 0 Then
objWorksheet.Cells(Cell.Row, Cell.Column + 1).font.color =
rgb(0,128,0)
objWorksheet.Cells (Cell.Row, Cell.Column + 1) = "Yes"
Else
objWorksheet.Cells(Cell.Row, Cell.Column + 1).font.color =
rgb(255,0,0)
objWorksheet.Cells (Cell.Row, Cell.Column + 1) = "No"
end If
Next

end Sub