Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 -- RRohl ------------------------------------------------------------------------ RRohl's Profile: http://www.excelforum.com/member.php...o&userid=36718 View this thread: http://www.excelforum.com/showthread...hreadid=566137 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Ping from Excel - Almost there
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Ping from Excel - Almost there
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.AutoFi -- RRoh ----------------------------------------------------------------------- RRohl's Profile: http://www.excelforum.com/member.php...fo&userid=3671 View this thread: http://www.excelforum.com/showthread.php?threadid=56613 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Ping from Excel - Almost there
Is there are question in there ?
NickHK "RRohl" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
PING within Excel Cell | Excel Worksheet Functions | |||
Excel Ping Command..via web... | Excel Programming | |||
Ping Server Excel Macro | Excel Programming | |||
Ping results into Excel | Excel Programming | |||
ping in excel | Excel Programming |