?
Solved

Ping all machines and results in different excel sheets

Posted on 2007-10-11
15
Medium Priority
?
476 Views
Last Modified: 2010-04-16
Hi,

I want a way that when i run the script it pings each machine in the Domain and get the data ta a sheet in excel and rename the excel to the todats date.When i run the next day it has to crete the data in another new sheet.Is there a way to do this.

Regards
Sharath
0
Comment
Question by:bsharath
15 Comments
 
LVL 3

Expert Comment

by:stephenbarrow
ID: 20063305
When you run the script redirect to output to a text file and read in the results to your spreadsheet.  You willl need to use VBA to do this.
Stephen
0
 
LVL 45

Expert Comment

by:patrickab
ID: 20063832
>You willl need to use VBA to do this.

I believe that Sharath was hoping you would provide the VBA.
0
 
LVL 33

Expert Comment

by:Jeroen Rosink
ID: 20063998
you could use some code like:

Dim ws1 as worksheet
Sub NewSheet()
    Sheets.Add
    ActiveSheet.Copy After:=Sheets(ActiveWorkbook.Sheets.Count)
    ActiveSheet.Name = Format(Now(), "dd-mmm-yyyy")
    Set ws1 = activesheet
    Call YOURPINGMACRO

End Sub

And use ws1 as sheet in your PingMacro

regards,
Jeroen
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 9

Expert Comment

by:ghostdog74
ID: 20064106
here is a simple one. OP please adapt/amend

Option Explicit
Dim objPing,objFile,objFSO,objExcel,objSheet,PING
Dim myOutFile,query,Row,Col,PingMachines(2),i,response
Col = 1
Row = 1
PingMachines(0)="127.0.0.1"
PingMachines(1)="134.0.0.1"
Set objFSO=CreateObject("Scripting.FileSystemObject")
Set objExcel = CreateObject("Excel.Application")
objExcel.Workbooks.Add
Set objSheet = objExcel.ActiveWorkbook.Worksheets(1)
objExcel.Columns(1).ColumnWidth = 16
objExcel.Columns(2).ColumnWidth = 20
objSheet.Cells(1,Col).Value = "Server" 'Contain IP address
objSheet.Cells(1,Col+1).Value = "Date" ''Date Time
objSheet.Cells(1,Col+2).Value = "Response" 'Response of the ping
Row=Row+1
For i=LBound(PingMachines) To UBound(PingMachines) -1
      query="select * from Win32_PingStatus where address ='"& PingMachines(i) & "'"
      Set objPing = GetObject("winmgmts:{impersonationLevel=impersonate}").ExecQuery(query)
      For Each PING In objPing
        Select Case PING.StatusCode
          Case 0
            response="Reply from " & PING.ProtocolAddress
          Case 11002    
            response="Destination Net Unreachable"
          Case 11003
            response="Destination Net Unreachable"
          Case 11010
            response="Request Timed Out"    
        End Select
        objSheet.Cells(Row,1).Value=PingMachines(i)
        objSheet.Cells(Row,2).Value=Now
        objSheet.Cells(Row,3).Value=response
        Row=Row+1
      Next
Next
objExcel.ActiveWorkbook.SaveAs("c:\temp\test.xls")
objExcel.ActiveWorkbook.Close
objExcel.Application.Quit
0
 
LVL 11

Author Comment

by:bsharath
ID: 20067175
Roos01
Can i use ghostdog1 code in excel and use your code.

I am a bit confused on which code to use...
0
 
LVL 11

Author Comment

by:bsharath
ID: 20075775
ghostdog74

Can the script take the ip's or machine names from a txt file and change the sheetname to today's date...
So when i repeat the ping it has to create a new sheet and change date to today's date...
0
 
LVL 33

Expert Comment

by:Jeroen Rosink
ID: 20076568
Hello bsharath,
I asumed you already had the script for pinging machines on the domain.
The script I gave is creating a new worksheet every day.

You might try this one, where a sheet called: PC Names, contains all the PC names you want to track.

Dim ws1, ws2 As Worksheet
Dim i As Long
Sub NewSheet()
Set ws2 = Sheets("PC Names") 'this sheetc ontains all PC names
RowsA = ws2.Cells(Rows.Count, 1).End(xlUp).Row
    Sheets.Add
    ActiveSheet.Copy After:=Sheets(ActiveWorkbook.Sheets.Count)
    ActiveSheet.Name = Format(Now(), "dd-mmm-yyyy")
    Set ws1 = ActiveSheet
    For i = 2 To RowsA
    If Cells(i, 1) <> "" Then
        Call MYPINGMACRO(Cells(i, 1))
    End If
End Sub

Sub MYPINGMACRO(strComputer)
'http://msdn2.microsoft.com/en-us/library/aa394595.aspx
    'strComputer = Cells(i, 2)
    ws1.Cells(i, 1) = strComputer
    '"ping -n 1 ComputerName >> C:\results.txt".
    Set objShell = CreateObject("WScript.Shell")
    Set objScriptExec = objShell.Exec( _
        "ping -n 2 -w 1000 " & strComputer)
    strPingResults = LCase(objScriptExec.StdOut.ReadAll)
    If InStr(strPingResults, "reply from") Then
        If InStr(strPingResults, "destination net unreachable") Then
            ws1.Cells(i, 2) = strComputer & "did not respond to ping."
        Else
           ws1.Cells(i, 2) = strComputer & " responded to ping."
        End If
    Else
        ws1.Cells(i, 2) = strComputer & " did not respond to ping."
    End If
End Sub

regards,
Jeroen
0
 
LVL 11

Author Comment

by:bsharath
ID: 20076643
Hi,

Is this a Macro or vbs script...
0
 
LVL 33

Expert Comment

by:Jeroen Rosink
ID: 20076658
This is a macro which should be used in a new module in excel.
0
 
LVL 11

Author Comment

by:bsharath
ID: 20076675
I have put the whole code in a new Module and named the sheet (PC Names)
Where should i put the machine names?
Or should i create a file name (PC Names) and run the script...

Should the whole script be in a single Module?
0
 
LVL 33

Expert Comment

by:Jeroen Rosink
ID: 20076742
The machine names should be stored in column A of the sheet called PC Names

the script can be p[laced in one module within the VBA editor.

to run the script while reading a textfile need more coding and changes on errors.
If it is easy to add the machinenames in excel then I would prefer that approach.

Jeroen
0
 
LVL 11

Author Comment

by:bsharath
ID: 20076786
I get a Compile error.

End Sub

To just reconfirm....Is all this 1 code .I need to put this in 1 module...

Start code......................

Dim ws1, ws2 As Worksheet
Dim i As Long
Sub NewSheet()
Set ws2 = Sheets("PC Names") 'this sheetc ontains all PC names
RowsA = ws2.Cells(Rows.Count, 1).End(xlUp).Row
    Sheets.Add
    ActiveSheet.Copy After:=Sheets(ActiveWorkbook.Sheets.Count)
    ActiveSheet.Name = Format(Now(), "dd-mmm-yyyy")
    Set ws1 = ActiveSheet
    For i = 2 To RowsA
    If Cells(i, 1) <> "" Then
        Call MYPINGMACRO(Cells(i, 1))
    End If
End Sub

Sub MYPINGMACRO(strComputer)
'http://msdn2.microsoft.com/en-us/library/aa394595.aspx
    'strComputer = Cells(i, 2)
    ws1.Cells(i, 1) = strComputer
    '"ping -n 1 ComputerName >> C:\results.txt".
    Set objShell = CreateObject("WScript.Shell")
    Set objScriptExec = objShell.Exec( _
        "ping -n 2 -w 1000 " & strComputer)
    strPingResults = LCase(objScriptExec.StdOut.ReadAll)
    If InStr(strPingResults, "reply from") Then
        If InStr(strPingResults, "destination net unreachable") Then
            ws1.Cells(i, 2) = strComputer & "did not respond to ping."
        Else
           ws1.Cells(i, 2) = strComputer & " responded to ping."
        End If
    Else
        ws1.Cells(i, 2) = strComputer & " did not respond to ping."
    End If
End Sub


End Code......................
0
 
LVL 33

Expert Comment

by:Jeroen Rosink
ID: 20077154
I noticed that I forgot the Next statement:

Dim ws1, ws2 As Worksheet
Dim i As Long
Sub NewSheet()
Set ws2 = Sheets("PC Names") 'this sheetc ontains all PC names
RowsA = ws2.Cells(Rows.Count, 1).End(xlUp).Row
    Sheets.Add
    ActiveSheet.Copy After:=Sheets(ActiveWorkbook.Sheets.Count)
    ActiveSheet.Name = Format(Now(), "dd-mmm-yyyy")
    Set ws1 = ActiveSheet
    For i = 2 To RowsA
    If Cells(i, 1) <> "" Then
        Call MYPINGMACRO(Cells(i, 1))
    End If
   Next i 'Forgot about this statement
End Sub

0
 
LVL 11

Author Comment

by:bsharath
ID: 20077205
Thanks now it dont get any error.It creates the sheet with todays date but no results in there.
In the sheet Pcname i have some machine names.When i run the Macro it just creates the sheet but no data in it.
Can you even give the time.As if i run the macro mor than once it says duplicate sheet name available.
0
 
LVL 33

Accepted Solution

by:
Jeroen Rosink earned 2000 total points
ID: 20079905
here an changed macro which cheks if a sheet is already there or not.

Dim ws1, ws2 As Worksheet
Dim i As Long
Sub NewSheet()
Dim X As Object
Set ws2 = Sheets("PC Names") 'this sheetc ontains all PC names
RowsA = ws2.Cells(Rows.Count, 1).End(xlUp).Row

'Check if sheet exist

    On Error Resume Next
    sname = Format(Now(), "dd-mmm-yyyy")
    Set X = ActiveWorkbook.Sheets(sname)
    If Err <> 0 Then
        'If sheet doesn't exist then create new sheet
        Sheets.Add
        ActiveSheet.Copy After:=Sheets(ActiveWorkbook.Sheets.Count)
        ActiveSheet.Name = sname ' Format(Now(), "dd-mmm-yyyy")
        Set ws1 = ActiveSheet
    Else
        Set ws1 = Sheets(sname)
    End If
    On Error GoTo 0
   
    For i = 1 To RowsA 'starts on Row1 of column A
    If ws2.Cells(i, 1) <> "" Then
        Call MYPINGMACRO(ws2.Cells(i, 1))
    End If
    Next i
End Sub

Sub MYPINGMACRO(strComputer)
'http://msdn2.microsoft.com/en-us/library/aa394595.aspx
    ws1.Cells(i, 1) = strComputer
    Set objShell = CreateObject("WScript.Shell")
    Set objScriptExec = objShell.Exec( _
        "ping -n 2 -w 1000 " & strComputer)
    strPingResults = LCase(objScriptExec.StdOut.ReadAll)
    If InStr(strPingResults, "reply from") Then
        If InStr(strPingResults, "destination net unreachable") Then
            ws1.Cells(i, 2) = strComputer & "did not respond to ping."
        Else
           ws1.Cells(i, 2) = strComputer & " responded to ping."
        End If
    Else
        ws1.Cells(i, 2) = strComputer & " did not respond to ping."
    End If
End Sub

regards,
Jeroen
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
Q&A with Course Creator, Mark Lassoff, on the importance of HTML5 in the career of a modern-day developer.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…
Suggested Courses

612 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