• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 481
  • Last Modified:

Ping all machines and results in different excel sheets

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
bsharath
Asked:
bsharath
1 Solution
 
stephenbarrowCommented:
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
 
patrickabCommented:
>You willl need to use VBA to do this.

I believe that Sharath was hoping you would provide the VBA.
0
 
Jeroen RosinkSoftware testing consultantCommented:
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
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
ghostdog74Commented:
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
 
bsharathAuthor Commented:
Roos01
Can i use ghostdog1 code in excel and use your code.

I am a bit confused on which code to use...
0
 
bsharathAuthor Commented:
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
 
Jeroen RosinkSoftware testing consultantCommented:
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
 
bsharathAuthor Commented:
Hi,

Is this a Macro or vbs script...
0
 
Jeroen RosinkSoftware testing consultantCommented:
This is a macro which should be used in a new module in excel.
0
 
bsharathAuthor Commented:
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
 
Jeroen RosinkSoftware testing consultantCommented:
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
 
bsharathAuthor Commented:
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
 
Jeroen RosinkSoftware testing consultantCommented:
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
 
bsharathAuthor Commented:
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
 
Jeroen RosinkSoftware testing consultantCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now