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
123
124
125
126
127
128
129
130
| ' text file to read from
strReadFile = "C:\computers.txt"
' excel file to create
sXLS = "C:\service tags.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"
objExcel.Cells(1,4).Value = "Utilisateur"
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 non trouvé!"
y1 = y1 + 1 ' go to next column
objExcel.Cells(x,y1).Value = "Serial non trouvé!"
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)
If Trim(strHost) <> "" Then
strCommand = "Ping.exe -n 3 -w 750 " & strHost
Set objExecObject = objShell.Exec _
("%comspec% /c title " & strHost _
& chr(38) & strCommand)
Do While Not objExecObject.StdOut.AtEndOfStream
strText = objExecObject.StdOut.ReadLine()
If Instr(strText, "TTL=") > 0 _
Then IsPingable = True : Exit Do
Loop
If IsPingable = True then
With GetObject("winmgmts:root\cimv2")
For Each objProcess in .ExecQuery _
("SELECT commandline FROM Win32_Process" _
& " WHERE Name = 'ping.exe'",,48)
If objProcess.commandline = strCommand _
Then objProcess.Terminate() : Exit For
Next
End With
End If
End If
If (not IsPingable = True) Then IsPingable = False
End Function |
