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
 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:
 
'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

Open in new window


Any help would be appreciated.
EverwulfAsked:
Who is Participating?
 
CodeCruiserCommented:
Try adding

On Error Resume Next

at the top
0
 
EverwulfAuthor Commented:
That worked but I had to put the that in the Do until section

 
Do Until objTS.AtEndOfStream
On Error Resume Next
    strComputer = objTS.ReadLine

Open in new window

0
 
EverwulfAuthor Commented:
Right answer but had to move it around a bit to find the right place.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.