Ping from Excel - Almost there

In this scrip file I am able to open a text file, ping the computer by host name and return the hostname and status to a specified excel workbook/sheet. Instead of calling a text file for the hostname I want to call the hostname from a specified column in the worksheet and only record the status. I would like this to execute once every 300 seconds. Any Ideas?


'Shell ("ping.exe -t " & ActiveCell.Value), vbNormalFocus

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
Set objWorkbook = objExcel.Workbooks.Open("C:servers.xls")
Set objWorksheet = objWorkbook.Worksheets(4)
intRow = 2
objExcel.Cells(1, 1).Value = "Machine Name"
objExcel.Cells(1, 2).Value = "Results"

Set Fso = CreateObject("Scripting.FileSystemObject")

Set InputFile = fso.OpenTextFile("MachineList.Txt")

Do While Not (InputFile.atEndOfStream)
HostName = InputFile.ReadLine

Set WshShell = WScript.CreateObject("WScript.Shell")
Ping = WshShell.Run("ping -n 1 " & HostName, 0, True)
objExcel.Cells(intRow, 1).Value = HostName

Select Case Ping
Case 0 objExcel.Cells(intRow, 2).Value = "On Line"
Case 1 objExcel.Cells(intRow, 2).Value = "NO PING"

End Select
intRow = intRow + 1

Loop
objExcel.Range("A1:B1").Select
objExcel.Selection.Interior.ColorIndex = 1
objExcel.Selection.Font.ColorIndex = 6
objExcel.Selection.Font.Bold = True
objExcel.Cells.EntireColumn.AutoFit

You can use the Range function to get the value of the cell. Just
Change A1 to whatever cell contains the servername. As for running it
every 300 seconds that's easy too.
Add this line directly after your Sub YourMacroName() statement
Application.OnTime Now+TimeValue("00:05:00") "YourMacroName"
Do While Not (InputFile.atEndOfStream)
HostName = Range("A1")

Set WshShell = WScript.CreateObject("WScript.Shell")
Ping = WshShell.Run("ping -n 1 " & HostName, 0, True)

HTH

Die_Another_Day
RRohl wrote:
> In this scrip file I am able to open a text file, ping the computer by
> host name and return the hostname and status to a specified excel
> workbook/sheet. Instead of calling a text file for the hostname I want
> to call the hostname from a specified column in the worksheet and only
> record the status. I would like this to execute once every 300
> seconds. Any Ideas?
>
>
> 'Shell ("ping.exe -t " & ActiveCell.Value), vbNormalFocus
>
> Set objExcel = CreateObject("Excel.Application")
> objExcel.Visible = True
> Set objWorkbook = objExcel.Workbooks.Open("C:servers.xls")
> Set objWorksheet = objWorkbook.Worksheets(4)
> intRow = 2
> objExcel.Cells(1, 1).Value = "Machine Name"
> objExcel.Cells(1, 2).Value = "Results"
>
> Set Fso = CreateObject("Scripting.FileSystemObject")
>
> Set InputFile = fso.OpenTextFile("MachineList.Txt")
>
> Do While Not (InputFile.atEndOfStream)
> HostName = InputFile.ReadLine
>
> Set WshShell = WScript.CreateObject("WScript.Shell")
> Ping = WshShell.Run("ping -n 1 " & HostName, 0, True)
> objExcel.Cells(intRow, 1).Value = HostName
>
> Select Case Ping
> Case 0 objExcel.Cells(intRow, 2).Value = "On Line"
> Case 1 objExcel.Cells(intRow, 2).Value = "NO PING"
>
> End Select
> intRow = intRow + 1
>
> Loop
> objExcel.Range("A1:B1").Select
> objExcel.Selection.Interior.ColorIndex = 1
> objExcel.Selection.Font.ColorIndex = 6
> objExcel.Selection.Font.Bold = True
> objExcel.Cells.EntireColumn.AutoFit
>
>
> --
> RRohl
> ------------------------------------------------------------------------
> RRohl's Profile: http://www.excelforum.com/member.php...o&userid=36718
> View this thread: http://www.excelforum.com/showthread...hreadid=566137

Dim arrExcelValues()


Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
Set objWorkbook = objExcel.Workbooks.Open("C:Serverlist.xls")
Set objWorksheet = objWorkbook.Worksheets(1)
intRow = 2

objExcel.Cells(1, 1).Value = "Server Name"
objExcel.Cells(1, 2).Value = "Results"

i = 2
x = 1

Do Until objExcel.Cells(i, 1).Value = ""

ReDim Preserve arrExcelValues(x)
arrExcelValues(x) = objExcel.Cells(i, 1).Value


Set WshShell = WScript.CreateObject("WScript.Shell")

Ping = WshShell.Run("ping -n 1 " & arrExcelValues(x), 0, True)
objExcel.Cells(intRow, 1).Value = arrExcelValues(x)

i = i + 1
x = x + 1


if ping = 0 then
objExcel.Cells(intRow, 2).Value = "On Line"
else
objExcel.Cells(intRow, 2).Value = "NO PING"
end if

intRow = intRow + 1

Loop
objExcel.Range("A1:B1").Select
objExcel.Selection.Interior.ColorIndex = 5
objExcel.Selection.Font.ColorIndex = 6
objExcel.Selection.Font.Bold = True
objExcel.Cells.EntireColumn.AutoFit

Is there are question in there ?

NickHK

"RRohl" <RRohl.2cwjah_1156183514.3168@excelforum-nospam.com> wrote in
message news:RRohl.2cwjah_1156183514.3168@excelforum-nospam.com...
>
> Dim arrExcelValues()
>
>
> Set objExcel = CreateObject("Excel.Application")
> objExcel.Visible = True
> Set objWorkbook = objExcel.Workbooks.Open("C:Serverlist.xls")
> Set objWorksheet = objWorkbook.Worksheets(1)
> intRow = 2
>
> objExcel.Cells(1, 1).Value = "Server Name"
> objExcel.Cells(1, 2).Value = "Results"
>
> i = 2
> x = 1
>
> Do Until objExcel.Cells(i, 1).Value = ""
>
> ReDim Preserve arrExcelValues(x)
> arrExcelValues(x) = objExcel.Cells(i, 1).Value
>
>
> Set WshShell = WScript.CreateObject("WScript.Shell")
>
> Ping = WshShell.Run("ping -n 1 " & arrExcelValues(x), 0,
> True)
> objExcel.Cells(intRow, 1).Value = arrExcelValues(x)
>
> i = i + 1
> x = x + 1
>
>
> if ping = 0 then
> objExcel.Cells(intRow, 2).Value = "On Line"
> else
> objExcel.Cells(intRow, 2).Value = "NO PING"
> end if
>
> intRow = intRow + 1
>
> Loop
> objExcel.Range("A1:B1").Select
> objExcel.Selection.Interior.ColorIndex = 5
> objExcel.Selection.Font.ColorIndex = 6
> objExcel.Selection.Font.Bold = True
> objExcel.Cells.EntireColumn.AutoFit
>
>
> --
> RRohl
> ------------------------------------------------------------------------
> RRohl's Profile:
http://www.excelforum.com/member.php...o&userid=36718
> View this thread: http://www.excelforum.com/showthread...hreadid=566137
>