Excel ile ping atma

İpuçlari

by Ali Örtül 28 Mart 2021

Excel ile ping atma yazısında sunucu, client veya applianceların online offline durumlarını check edebiliriz. Aşağıda paylaştığım excel dosyası içerisinde macro barındırdığı için xlsm formatındadır. İlk sheet de manual olarak ip adreslerini kontrol edebilir ikinci sheetde ise excele ip kontrolü otomatik yaptırabilirsiniz. Tek yapmanız gereken size ait ipleri ilgili hücreye paste etmek ve start etmek olacak.

Excel ping atma

İki macro’dan pingsystem macrosu aşağıdaki kodlardan oluşuyor.

 Function Ping(strip)
Dim objshell, boolcode
Set objshell = CreateObject("Wscript.Shell")
boolcode = objshell.Run("ping -n 1 -w 1000 " &amp; strip, 0, True)
If boolcode = 0 Then
    Ping = True
Else
    Ping = False
End If
End Function

Sub PingSystem()
Dim strip As String
Do Until Sheet1.Range("F1").Value = "STOP"
Sheet1.Range("F1").Value = "TESTING"
For introw = 2 To ActiveSheet.Cells(65536, 2).End(xlUp).Row
    strip = ActiveSheet.Cells(introw, 2).Value
    If Ping(strip) = True Then
        ActiveSheet.Cells(introw, 3).Value = "Online"
        ActiveSheet.Cells(introw, 3).Font.Color = RGB(0, 0, 0)
        Application.Wait (Now + TimeValue("0:00:01"))
        ActiveSheet.Cells(introw, 3).Font.Color = RGB(0, 200, 0)
    Else
        ActiveSheet.Cells(introw, 3).Value = "Offline"
        ActiveSheet.Cells(introw, 3).Interior.ColorIndex = 0
        ActiveSheet.Cells(introw, 3).Font.Color = RGB(200, 0, 0)
        Application.Wait (Now + TimeValue("0:00:01"))
        ActiveSheet.Cells(introw, 3).Interior.ColorIndex = 6
    End If
    If Sheet1.Range("F1").Value = "STOP" Then
        Exit For
    End If
Next
Loop
Sheet1.Range("F1").Value = "IDLE"
End Sub

Sub stop_ping()
    Sheet1.Range("F1").Value = "STOP"
End Sub

Function Ping(strip)

Dim objshell, boolcode

Set objshell = CreateObject("Wscript.Shell")

boolcode = objshell.Run("ping -n 1 -w 1000 " & strip, 0, True)

If boolcode = 0 Then

Ping = True

Else

Ping = False

End If

End Function

Sub PingSystem()

Dim strip As String

Do Until Sheet1.Range("F1").Value = "STOP"

Sheet1.Range("F1").Value = "TESTING"

For introw = 2 To ActiveSheet.Cells(65536, 2).End(xlUp).Row

strip = ActiveSheet.Cells(introw, 2).Value

If Ping(strip) = True Then

ActiveSheet.Cells(introw, 3).Value = "Online"

ActiveSheet.Cells(introw, 3).Font.Color = RGB(0, 0, 0)

Application.Wait (Now + TimeValue("0:00:01"))

ActiveSheet.Cells(introw, 3).Font.Color = RGB(0, 200, 0)

Else

ActiveSheet.Cells(introw, 3).Value = "Offline"

ActiveSheet.Cells(introw, 3).Interior.ColorIndex = 0

ActiveSheet.Cells(introw, 3).Font.Color = RGB(200, 0, 0)

Application.Wait (Now + TimeValue("0:00:01"))

ActiveSheet.Cells(introw, 3).Interior.ColorIndex = 6

End If

If Sheet1.Range("F1").Value = "STOP" Then

Exit For

End If

Loop

Sheet1.Range("F1").Value = "IDLE"

End Sub

Sub stop_ping()

Sheet1.Range("F1").Value = "STOP"

End Sub

İkinci macro olan stop ise aşağıdadır.

Function Ping(strip)
Dim objshell, boolcode
Set objshell = CreateObject("Wscript.Shell")
boolcode = objshell.Run("ping -n 1 -w 1000 " &amp; strip, 0, True)
If boolcode = 0 Then
    Ping = True
Else
    Ping = False
End If
End Function

Sub PingSystem()
Dim strip As String
Do Until Sheet1.Range("F1").Value = "STOP"
Sheet1.Range("F1").Value = "TESTING"
For introw = 2 To ActiveSheet.Cells(65536, 2).End(xlUp).Row
    strip = ActiveSheet.Cells(introw, 2).Value
    If Ping(strip) = True Then
        ActiveSheet.Cells(introw, 3).Value = "Online"
        ActiveSheet.Cells(introw, 3).Font.Color = RGB(0, 0, 0)
        Application.Wait (Now + TimeValue("0:00:01"))
        ActiveSheet.Cells(introw, 3).Font.Color = RGB(0, 200, 0)
    Else
        ActiveSheet.Cells(introw, 3).Value = "Offline"
        ActiveSheet.Cells(introw, 3).Interior.ColorIndex = 0
        ActiveSheet.Cells(introw, 3).Font.Color = RGB(200, 0, 0)
        Application.Wait (Now + TimeValue("0:00:01"))
        ActiveSheet.Cells(introw, 3).Interior.ColorIndex = 6
    End If
    If Sheet1.Range("F1").Value = "STOP" Then
        Exit For
    End If
Next
Loop
Sheet1.Range("F1").Value = "IDLE"
End Sub

Sub stop_ping()
    Sheet1.Range("F1").Value = "STOP"
End Sub

Function Ping(strip)

Dim objshell, boolcode

Set objshell = CreateObject("Wscript.Shell")

boolcode = objshell.Run("ping -n 1 -w 1000 " & strip, 0, True)

If boolcode = 0 Then

Ping = True

Else

Ping = False

End If

End Function

Sub PingSystem()

Dim strip As String

Do Until Sheet1.Range("F1").Value = "STOP"

Sheet1.Range("F1").Value = "TESTING"

For introw = 2 To ActiveSheet.Cells(65536, 2).End(xlUp).Row

strip = ActiveSheet.Cells(introw, 2).Value

If Ping(strip) = True Then

ActiveSheet.Cells(introw, 3).Value = "Online"

ActiveSheet.Cells(introw, 3).Font.Color = RGB(0, 0, 0)

Application.Wait (Now + TimeValue("0:00:01"))

ActiveSheet.Cells(introw, 3).Font.Color = RGB(0, 200, 0)

Else

ActiveSheet.Cells(introw, 3).Value = "Offline"

ActiveSheet.Cells(introw, 3).Interior.ColorIndex = 0

ActiveSheet.Cells(introw, 3).Font.Color = RGB(200, 0, 0)

Application.Wait (Now + TimeValue("0:00:01"))

ActiveSheet.Cells(introw, 3).Interior.ColorIndex = 6

End If

If Sheet1.Range("F1").Value = "STOP" Then

Exit For

End If

Loop

Sheet1.Range("F1").Value = "IDLE"

End Sub

Sub stop_ping()

Sheet1.Range("F1").Value = "STOP"

End Sub

Excel ping atma dosyasını aşağıdaki linkten indirebilirsiniz.

Excel ping indir

Excel ile ping atma

Excel ping atma

One thought on “Excel ile ping atma”

Bir yanıt yazın