Thank you urkec for your responce. I noticed that you mentioned that
Win32_PingStatus was added for windows XP. If I'm using Windows 2000 what
would I need to do to make it work?

Thank you again for your help!

Andy

"urkec" wrote:

"Joel" wrote:

I'm not getting the error. I think the focus my not be on the present
workbook. try adding this line of code

"Andy Dawkins" wrote:

Hello all,
I am attempting to ping a list of machine names listed within Excel 2003.
So far, I have been able to do this by changing some VBS code I found
online. The problem is that the code opens an existing file, but I would
like to have the results stay in the active spreadsheet.

On Error Resume Next
Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\CIMV2")
Set colItems = objWMIService.ExecQuery("Select IpAddress From
Win32_NetworkAdapterConfiguration Where IPEnabled=TRUE")


The code you posted does not ping remote computers, it tries to connect to a
WMI service on remote computers (strComputer), and if it is available gets
it's IP address. You can use Win32_PingStatus WMI class to ping remote
machines. (It was added for Windows XP, so if you can't use it you can use
Ping command instead.) Your code will work, but it is faster to ping a
computer first, and only if it is available attempt connecting to remote WMI.
Here is some code:

Sub Ping()


Set Machines = Sheets(1).Range("A1", "A10")

For Each Machine In Machines.Cells
Debug.Print Machine
Set objPing = GetObject _
("winmgmts:{impersonationLevel=impersonate}"). _
ExecQuery("select * from Win32_PingStatus " & _
"where address = '" & Machine & "'")
For Each objStatus In objPing
If objStatus.StatusCode = 0 Then
Sheets(1).Cells(Machine.Row, Machine.Column + 1) = _
objStatus.StatusCode & " On Line"
Else
Sheets(1).Cells(Machine.Row, Machine.Column + 1) = _
objStatus.StatusCode & " Off Line"
End If
Next
Next


End Sub


It connects to local WMI service (no computer name in WMI moniker) and gets
ping status for computer names listed in cells A1 - A10. (you can also use IP
address instead of computer name). Possible values for objStatus.StatusCode
a

0 Success
11001 Buffer Too Small
11002 Destination Net Unreachable
11003 Destination Host Unreachable
11004 Destination Protocol Unreachable
11005 Destination Port Unreachable
11006 No Resources
11007 Bad Option
11008 Hardware Error
11009 Packet Too Big
11010 Request Timed Out
11011 Bad Request
11012 Bad Route
11013 TimeToLive Expired Transit
11014 TimeToLive Expired Reassembly
11015 Parameter Problem
11016 Source Quench
11017 Option Too Big
11018 Bad Destination
11032 Negotiating IPSEC
11050 General Failure

(copied from Win32_PingStatus documentation)

Hope this helps.

--
urkec