Everwulf
asked on
Code to ping PC then get Model and Serial number
I am writing code to get the system model and serial number of a list of pc's but keep getting an error
We have determined that since we are pulling from an inventory list not all the machines will be able to pull model or serial number. However I am not entirely sure how I would take that into account.
Here is the code:
Any help would be appreciated.
We have determined that since we are pulling from an inventory list not all the machines will be able to pull model or serial number. However I am not entirely sure how I would take that into account.
Here is the code:
'Reads Text file with assets and returns model and sn in xls file
' text file to read from
strReadFile = "computers.txt"
' excel file to create
sXLS = "modelinfo.xls"
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objTS = objFSO.OpenTextFile(strReadFile)
Set objShell = CreateObject("WScript.Shell")
Set objExcel = CreateObject("Excel.Application")
objExcel.Application.DisplayAlerts = False
objExcel.Visible = true
objExcel.Workbooks.Add
' define the column titles
objExcel.Cells(1,1).Value = "Computer Name"
objExcel.Cells(1,2).Value = "Model"
objExcel.Cells(1,3).Value = "Service Tag"
xRow = 1
yColumn = 1
' apply styles to rows and columns
Do Until yColumn = 4
objExcel.Cells(xRow,yColumn).Font.Bold = True
objExcel.Cells(xRow,yColumn).Font.Size = 11
objExcel.Cells(xRow,yColumn).Interior.ColorIndex = 11
objExcel.Cells(xRow,yColumn).Interior.Pattern = 1
objExcel.Cells(xRow,yColumn).Font.ColorIndex = 2
objExcel.Cells(xRow,yColumn).Borders.LineStyle = 1
objExcel.Cells(xRow,yColumn).WrapText = True
yColumn = yColumn + 1
Loop
x = 2
y = 1
' start reading from the text file, until the end
Do Until objTS.AtEndOfStream
strComputer = objTS.ReadLine
' check if the computername is pingbale, if not then skip to next name
If (IsPingable(strComputer) = True) then
Set objWMIService = GetObject("winmgmts:" _
& "{impersonationLevel=impersonate}!\\" _
& strComputer & "\root\cimv2")
Set colComputer = objWMIService.ExecQuery _
("SELECT * FROM Win32_ComputerSystemProduct","WQL",48)
y1 = y
If Err.number=0 Then
For Each objComputer in colComputer
objExcel.Cells(x,y1).Value = strComputer
y1 = y1 + 1 ' go to next column
objExcel.Cells(x,y1).Value = objComputer.Name
y1 = y1 + 1 ' go to next column
objExcel.Cells(x,y1).Value = objComputer.IdentifyingNumber
x = x + 1 ' go to the next Row
Next
Else
objExcel.Cells(x,y1).Value = strComputer
y1 = y1 + 1 ' go to next column
objExcel.Cells(x,y1).Value = "Model not found!"
y1 = y1 + 1 ' go to next column
objExcel.Cells(x,y1).Value = "Serial not found!"
x = x + 1 ' go to the next Row
End If
Err.clear
Else
objExcel.Cells(x,y1).Value = strComputer
y1 = y1 + 1 ' go to next column
objExcel.Cells(x,y1).Value = "Not Pingable"
x = x + 1 ' go to the next Row
End If
Loop
objExcel.Columns("A:C").Select
objExcel.Selection.HorizontalAlignment = 3 'center all data
objExcel.Selection.Borders.LineStyle = 1 'apply borders
objExcel.Columns("A:AH").EntireColumn.AutoFit 'autofit all columns
Const Excel07 = 12
appVerInt = split(objExcel.Version, ".")(0)
If appVerInt- Excel07 >=0 Then
objExcel.ActiveWorkbook.SaveAs(sXLS), 56 'You use office 2007 / 2010
Else
objExcel.ActiveWorkbook.SaveAs(sXLS), 43 'You use office 97-2003
End If
objExcel.Quit
set objExcel = Nothing
objTS.Close
msgbox "Done!"
WScript.Quit
Function IsPingable(ByVal strHost)
Dim objPing : Set objPing = GetObject("winmgmts:{impersonationLevel=impersonate}").ExecQuery _
("select * from Win32_PingStatus where address = '" & strHost & "'")
Dim objRetStatus
For Each objRetStatus In objPing
If IsNull(objRetStatus.StatusCode) Or objRetStatus.StatusCode<>0 Then
IsPingable = False
Else
IsPingable = True
End If
Next
If (not IsPingable = True) Then IsPingable = False
End Function
Any help would be appreciated.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Right answer but had to move it around a bit to find the right place.
ASKER
Open in new window