?
Solved

Autoformat / Get IP VBScript

Posted on 2009-12-28
5
Medium Priority
?
684 Views
Last Modified: 2012-05-08
Hello Experts,

I have a 2 part question.

1. I need to AutoFormat column C if the value = "Not Pingable" highlight cell RED.

2. I need to get the IP Address and put it in column B, assuming that the hostname is pingable.

Here is the code that I have so far:

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

intRow = 2

objExcel.Cells(1, 1).Value = "Host Name"
objExcel.Cells(1, 2).Value = "IP Address"
objExcel.Cells(1, 3).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, 3).Value = "Pingable"
Case 1 objExcel.Cells(intRow, 3).Value = "Not Pingable"
End Select

intRow = intRow + 1
Loop


objExcel.Range("A1:B1").Select
objExcel.Selection.Interior.ColorIndex = 19
objExcel.Selection.Font.ColorIndex = 11
objExcel.Selection.Font.Bold = True
objExcel.Cells.EntireColumn.AutoFit
objExcel.Selection.AutoFilter

intRow = intRow - 2
MsgBox "Script Has Completed Successfully,  " & intRow & " Servers Were Pinged"

0
Comment
Question by:eddiepardon
  • 2
  • 2
5 Comments
 
LVL 13

Expert Comment

by:sameer2010
ID: 26137828
Hi,

Where are you getting the issue?
The coloring should reside inside the CASE statement because you need it conditionally.
0
 

Author Comment

by:eddiepardon
ID: 26140037
Sameer,

Thanks for responding.

What would the code be for the Case statement?

What about the IP Address?
0
 
LVL 65

Accepted Solution

by:
RobSampson earned 2000 total points
ID: 26141858
Hi, this should be pretty close to what you're after.

Regards,

Rob.
Set WshShell = CreateObject("WScript.Shell")
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
objExcel.Workbooks.Add

intRow = 2

objExcel.Cells(1, 1).Value = "Host Name"
objExcel.Cells(1, 2).Value = "IP Address"
objExcel.Cells(1, 3).Value = "Results"

Set Fso = CreateObject("Scripting.FileSystemObject")
Set InputFile = fso.OpenTextFile("MachineList.Txt")

Do While Not (InputFile.atEndOfStream)
	HostName = InputFile.ReadLine
	objExcel.Cells(intRow, 1).Value = HostName
	If Ping(HostName) = True Then
		objExcel.Cells(intRow, 3).Value = "Pingable"
		objExcel.Cells(intRow, 2).Value = GetIPAddress(HostName)
	Else
		objExcel.Cells(intRow, 3).Value = "Not Pingable"
		objExcel.Range("A" & intRow & ":C" & intRow).Font.ColorIndex = 3
	End If
	intRow = intRow + 1
Loop

intRow = intRow = 2

objExcel.Range("A1:C1").Interior.ColorIndex = 19
objExcel.Range("A1:C1").Font.ColorIndex = 11
objExcel.Range("A1:C1").Font.Bold = True
objExcel.Cells.EntireColumn.AutoFit
objExcel.Range("A1:C1").AutoFilter

MsgBox "Script Has Completed Successfully,  " & intRow & " Servers Were Pinged"

Function Ping(strComputer)
	Dim objShell, boolCode
	Set objShell = CreateObject("WScript.Shell")
	boolCode = objShell.Run("Ping -n 1 -w 300 " & strComputer, 0, True)
	If boolCode = 0 Then
		Ping = True
	Else
		Ping = False
	End If
End Function

Function GetIPAddress(strComputer)
	Set objWMIService = GetObject("winmgmts:{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2") 
	Set colComputerIP = objWMIService.ExecQuery("Select IPAddress from Win32_NetworkAdapterConfiguration WHERE IPEnabled = True")
	strIPAddress = ""
	For Each IPConfig in colComputerIP
		If Not IsNull(IPConfig.IPAddress) Then
	        For intIPCount = LBound(IPConfig.IPAddress) To UBound(IPConfig.IPAddress)
	        	If IPConfig.IPAddress(intIPCount) <> "0.0.0.0" Then
					If strIPAddress = "" Then
						strIPAddress = IPConfig.IPAddress(intIPCount)
					Else
						strIPAddress = strIPAddress & " - " & IPConfig.IPAddress(intIPCount)
					End If
				End If
			Next
		End If
	Next
	GetIPAddress = strIPAddress
End Function

Open in new window

0
 

Author Closing Comment

by:eddiepardon
ID: 31670689
Rob,

Thanks! The solution is right on target.
0
 
LVL 65

Expert Comment

by:RobSampson
ID: 26143105
No problem. Thanks for the grade.

Regards,

Rob.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
With User Account Control (UAC) enabled in Windows 7, one needs to open an elevated Command Prompt in order to run scripts under administrative privileges. Although the elevated Command Prompt accomplishes the task, the question How to run as script…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
Screencast - Getting to Know the Pipeline
Suggested Courses
Course of the Month16 days, 12 hours left to enroll

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question