Clicky

I am writing code to get the system model and serial number of a list of pc's but keep getting an error
 
Error.png
  • 26 KB
  • Error
Error.png


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                             
1: 2: 3: 4: 5: 6: 7: 8: 9: 10: 11: 12: 13: 14: 15: 16: 17: 18: 19: 20: 21: 22: 23: 24: 25: 26: 27: 28: 29: 30: 31: 32: 33: 34: 35: 36: 37: 38: 39: 40: 41: 42: 43: 44: 45: 46: 47: 48: 49: 50: 51: 52: 53: 54: 55: 56: 57: 58: 59: 60: 61: 62: 63: 64: 65: 66: 67: 68: 69: 70: 71: 72: 73: 74: 75: 76: 77: 78: 79: 80: 81: 82: 83: 84: 85: 86: 87: 88: 89: 90: 91: 92: 93: 94: 95: 96: 97: 98: 99: 100: 101: 102: 103: 104: 105: 106: 107: 108: 109: 110: 111: 112: 113: 114: 115: 116: 117: 118: 119: 120: 121: 122: 

Select allOpen in new window



Any help would be appreciated.

    asked 12/14/2011 11:22

    Everwulf's gravatar image

    Everwulf ♦♦


    3 Answers:
    Try adding

    On Error Resume Next

    at the top
    link

    answered

    CodeCruiser's gravatar image

    CodeCruiser

    That worked but I had to put the that in the Do until section

     
    1:
    2:
    3:
    Do Until objTS.AtEndOfStream
    On Error Resume Next
        strComputer = objTS.ReadLine
    link

    answered 2011-12-15 at 11:39:38

    Everwulf's gravatar image

    Everwulf

    Right answer but had to move it around a bit to find the right place.
    link

    answered 2011-12-15 at 12:35:51

    Everwulf's gravatar image

    Everwulf

    Your answer
    [hide preview]

    Follow this question

    By Email:

    Once you sign in you will be able to subscribe for any updates here

    By RSS:

    Answers

    Answers and Comments

    Tags:

    ×2
    ×1

    Asked: 12/14/2011 11:22

    Seen: 384 times

    Last updated: 12/15/2011 04:36