?
Solved

How to run ping commands from Excel?

Posted on 2007-10-04
3
Medium Priority
?
3,031 Views
Last Modified: 2011-10-03
I want to be able to ping from Excel 2007.  Column A should be a list of computer names, column B should use CONCATENATE to put that computer name in a string "ping -n 1 ComputerName >> C:\results.txt".  I would like to make a macro that would run those pings in order, going down Column B.  Is that possible?  I haven't been able to find any information about running pings from Excel so far.

Also, if it is possible to run the pings, would it be possible to have the results put in Column C for that computer name?
0
Comment
Question by:joeybeeble
  • 2
3 Comments
 
LVL 33

Accepted Solution

by:
Jeroen Rosink earned 500 total points
ID: 20016020
perhaps this macro does what you need:

To look in Column B and return the results into Column C you might think of using tomsehing like this: (include the function mentioned above in the module)


Sub PingPcForRestults()
'http://msdn2.microsoft.com/en-us/library/aa394595.aspx
Dim RowsB As Long

RowsB = Cells(Rows.Count, 2).End(xlUp).Row
For i = 1 To RowsB

    If Cells(i, 2) <> "" Then
    strComputer = Cells(i, 2)
   
    '"ping -n 1 ComputerName >> C:\results.txt".
    Set objShell = CreateObject("WScript.Shell")
    Set objScriptExec = objShell.Exec( _
        "ping -n 2 -w 1000 " & strComputer & " >> C:\results.txt")
    strPingResults = LCase(objScriptExec.StdOut.ReadAll)
    If InStr(strPingResults, "reply from") Then
        If InStr(strPingResults, "destination net unreachable") Then
            Cells(i, 3) = strComputer & "did not respond to ping."
        Else
           Cells(i, 3) = strComputer & " responded to ping."
        End If
    Else
        Cells(i, 3) = strComputer & " did not respond to ping."
    End If

Next
End Sub


regards,
Jeroen
0
 

Author Comment

by:joeybeeble
ID: 20025697
That is exactly what I was looking for.  Thanks!
0
 
LVL 33

Expert Comment

by:Jeroen Rosink
ID: 20025706
Thanks for the grade!
It was a pleasure to help.

regards,
Jeroen
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question