Continuous ping with updates to Excel from list of hostnames via VBScript

Question

The following VBScript will display the status of a single ping command from a list of hostnames to an Excel spreadsheet:

Set objExcel = CreateObject("Excel.Application")
 
objExcel.Visible = True
 
objExcel.Workbooks.Add
 
intRow = 2
 

'# Define Labels 

objExcel.Cells(1, 1).Value = "Node"
 
objExcel.Cells(1, 2).Value = "Status"


'# Create file system object for reading the hosts from text file


Set Fso = CreateObject("Scripting.FileSystemObject")
 
Set InputFile = fso.OpenTextFile("MachineList.Txt")
 
'# Loop thru the text file till the end 
 
Do While Not (InputFile.atEndOfStream)
 
HostName = InputFile.ReadLine
  
'# Create shell object for Pinging the host machines

Set WshShell = WScript.CreateObject("WScript.Shell")
 
Ping = WshShell.Run("ping -n 1 " & HostName, 0, True)
 
 
objExcel.Cells(intRow, 1).Value = HostName
 
'# use switch case for checking the machine updown status
 
Select Case Ping

Case 0
    objExcel.Cells(intRow, 2).Value = "Up"
    objExcel.Cells(intRow, 2).Interior.Color = vbGreen
Case 1
    objExcel.Cells(intRow, 2).Value = "Down"
    objExcel.Cells(intRow, 2).Interior.Color = vbRed

End Select
 

intRow = intRow + 1
 
Loop
 
'# Format the excel
 
objExcel.Range("A1:B1").Select
 
objExcel.Selection.Interior.ColorIndex = 19
 
objExcel.Selection.Font.ColorIndex = 11
 
objExcel.Selection.Font.Bold = True
 
objExcel.Cells.EntireColumn.AutoFit

This code works as intended.

I want to constantly ping the hostnames with live updates to Excel. I have tried adding -t to the ping command - instead of -n 1 like you would in command prompt, but the script gets hung, as it will stop on the first hostname in the list, and will not progress further.

My question: How do I achieve a continuous ping command or run the script repeatedly?

After searching online for a solution for a few days, I can find none.

You can use `Application.OnTime` in Excel to run your macro in a loop with a specific time interval. EDIT: since you're using vbscript you can put your code in a loop and add a delay using `WScript.Sleep()` You'll need to change how you manage the results display though... — Tim Williams, Jan 24 '17 at 02:02
@TimWilliams, Can you expand on the loop concept and how much change to the results display are we talking about here? I have been throwing around an idea for the output to be to an HTML file later down this development road. I know it's a bit off topic for this question, but if I have to pivot, that's the direction I would like to go in - just as a note. — Vandal, Jan 24 '17 at 02:20

score 1 · Accepted Answer · answered Jan 24 '17 at 07:01

Use of wscript.sleep and writing results to a text file

Dim txt, fso, InputFile, OutputFile, arr, h, Ping, sep

Set fso = CreateObject("Scripting.FileSystemObject")
Set WshShell = wscript.CreateObject("WScript.Shell")

'# load the hosts from a text file into an array
Set InputFile = fso.OpenTextFile("MachineList.Txt")
arr = Split(InputFile.readall(), vbCrLf)
InputFile.Close

Do
    txt = Now & vbcrlf 'add a timestamp
    sep = ""
    For Each h In arr
        Ping = WshShell.Run("ping -n 1 " & h, 0, True)

        txt = txt & sep & h & ","
        If Ping = 1 Then
            txt = txt & "Up"
        ElseIf Ping = 0 Then
            txt = txt & "Down"
        Else
            txt = txt & "???"
        End If
        sep = vbCrLf
    Next 

    Set OutputFile = fso.createTextFile("Results.Txt")
    OutputFile.write txt
    OutputFile.Close

    wscript.sleep 1000 * 60 '60 sec
Loop

How do I shorten the sleep time? I need to get it as low as possible. Also, I had to change the `If Ping = 1 Then` to Down, and vice versa to get the correct output for the `Ping` results. Is there an alternative for the output, or must it be to a .txt file? If not, I may be able to point the .html file to pull results from the .txt later down the road. — Vandal, Jan 24 '17 at 19:36
You can put the results wherever you like. Writing to text file was just an example. Look at the docs for Sleep - Google is a thing you know.... — Tim Williams, Jan 24 '17 at 19:53

Continuous ping with updates to Excel from list of hostnames via VBScript

1 Answers1