Toon posts:

[VBS] WMI info naar Excel

Pagina: 1
Acties:

Onderwerpen

Excel Vba

maandag 26 april 2010 14:14

Acties:
  • 0 Henk 'm!

Verwijderd

Topicstarter
Tweakers,


Ik ben bezig met een VBS script om WMI informatie in een Excel sheet te droppen.
Het is me tot nu toe aardig gelukt. Heb alleen problemen met de volgende;

- WAN MAC adres niet opvraagbaar.
- Autofit columns na het schrijven.


Hier mijn scriptje;
VBScript: i-info
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
131
132
133
134
135
136
137

  








' == Open Excel ==
Set objExcel = CreateObject("Excel.Application")




' == Schrijf headers weg ==
objExcel.Visible = True
objExcel.Workbooks.Add
objExcel.Cells(1, 1).Value = "Operating System"
objExcel.Cells(1, 2).Value = "O.S. Version"
objExcel.Cells(1, 3).Value = "Total Virtual memory "
objExcel.Cells(1, 4).Value = "Directory "
objExcel.Cells(1, 5).Value = "Computernaam" 
objExcel.Cells(1, 6).Value = "MAC Adres"
objExcel.Cells(1, 7).Value = "Serial Number"
objExcel.Cells(1, 8).Value = "Manufacturer"
objExcel.Cells(1, 9).Value = "Model"
objExcel.Cells(1, 10).Value = "Memory"
objExcel.Cells(1, 11).Value = "Harddisk Capacity"
objExcel.Cells(1, 12).Value = "Service Pack level"

' == haal WMI data op  en schrijf weg ==
strComputer = "."
Set objWMIService = GetObject("winmgmts:" _
    & "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")
Set colSettings = objWMIService.ExecQuery _
    ("Select * from Win32_OperatingSystem")


For Each objOperatingSystem in colSettings 
    objExcel.Cells(2, 1).Value =  objOperatingSystem.Name
    objExcel.Cells(2, 2).Value =  objOperatingSystem.Version
    objExcel.Cells(2, 3).Value =  objOperatingSystem.TotalVirtualMemorySize
    objExcel.Cells(2, 4).Value =  objOperatingSystem.WindowsDirectory


Next

Set colSettings = objWMIService.ExecQuery _
    ("Select * from Win32_ComputerSystem")

For Each objComputer in colSettings 
    objExcel.Cells(2, 5).Value = objComputer.Name
Next

Set colItems = objWMIService.ExecQuery _
    ("Select * From Win32_NetworkAdapterConfiguration Where IPEnabled = True")

For Each objItem in colItems
    objExcel.Cells(2, 6).Value =  objItem.MACAddress

Next

Set objWMIService = getObject("winmgmts:" &_
     "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")
Set colBIOS = objWMIService.ExecQuery ("Select * from Win32_SystemEnclosure")

For Each objBIOS in colBIOS
    objExcel.Cells(2, 7).Value =  objBIOS.SerialNumber






Next

Set colItems = objWMIService.ExecQuery("Select * from Win32_ComputerSystem",,48)
For Each objItem In colItems

    objExcel.Cells(2, 8).Value =  objItem.Manufacturer

Next
Set colItems = objWMIService.ExecQuery("Select * from Win32_ComputerSystem",,48)
For Each objItem In colItems

    objExcel.Cells(2, 9).Value =  objItem.Model

Next
Set objWMIService = GetObject("winmgmts:" _
& "{impersonationLevel=impersonate}!\\" _ 
& strComputer & "\root\cimv2") 

Set colComputer = objWMIService.ExecQuery _
("Select * from Win32_ComputerSystem")

For Each objComputer in colComputer 
    objExcel.Cells(2, 10).Value =  int((objComputer.TotalPhysicalMemory) /1048576)+9 & "MB"


Next
Set objWMIService = GetObject("winmgmts:\\" _
& strComputer & "\root\cimv2")
Set colItems = objWMIService.ExecQuery(_
"Select * from Win32_DiskDrive")

For Each objItem in colItems
    objExcel.Cells(2, 11).Value = Int(objItem.Size /(1073741824)) & " GB"





Next
Const Impersonate = "winmgmts:{impersonationLevel=impersonate}!\\"
computer = "."
Set oWMI = GetObject(Impersonate & computer & "\root\cimv2")
Set QueryWMI = oWMI.ExecQuery("SELECT * FROM Win32_OperatingSystem")

For Each oItem In QueryWMI
    objExcel.Cells(2, 12).Value = oItem.ServicePackMajorVersion


Next


Set objRange = objWorksheet.UsedRange
objRange.EntireColumn.Autofit()

maandag 26 april 2010 21:51

Acties:
  • 0 Henk 'm!
  • TeeDee
  • Registratie: Februari 2001
  • Laatst online: 12-09 17:37

TeeDee

CQB 241

- En wat lukt er dan niet aan het Autofit verhaal?

Heb je al gedebugged? Zo ja, wat zijn de eventuele foutmeldingen? Gooi eens wat Echo's op. Iets van een colItems.Count ofzo. Met andere woorden: niet zomaar even "Hier is mijn script, zus en zo werkt niet, fix het even voor mij".

[ Voor 9% gewijzigd door TeeDee op 26-04-2010 21:51 ]

Heart..pumps blood.Has nothing to do with emotion! Bored

zondag 2 mei 2010 01:30

Acties:
  • 0 Henk 'm!

Verwijderd

Topicstarter
Hi Teedee,


Thanks voor de reactie.
Het is inmiddels gelukt. Wou "even" weten welke code ik moest gebruiken om WAN/WMI info te krijgen en de kolomen te autofitten.

Hierbij de code voor de zoekenden;

VBScript: filename
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
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262

 for (i = 0; i < 4; i++) 

' == Open Excel ==
Set objExcel = CreateObject("Excel.Application")




' == Schrijf headers weg ==
objExcel.Visible = True
objExcel.Workbooks.Add
objExcel.Cells(1, 1).Value = "Operating System"
objExcel.Cells(1, 2).Value = "O.S. Version"
objExcel.Cells(1, 3).Value = "Total Virtual memory "
objExcel.Cells(1, 4).Value = "Directory "
objExcel.Cells(1, 5).Value = "Computername" 
objExcel.Cells(1, 6).Value = "MAC Address"
objExcel.Cells(1, 7).Value = "Serial Number"
objExcel.Cells(1, 8).Value = "Manufacturer"
objExcel.Cells(1, 9).Value = "Model"
objExcel.Cells(1, 10).Value = "Memory"
objExcel.Cells(1, 11).Value = "Harddisk Capacity"
objExcel.Cells(1, 12).Value = "Service Pack level"
objExcel.Cells(1, 13).Value = "Username"
objExcel.Cells(1, 14).Value = "Processor"

' == Text Opmaken ==
objExcel.Cells(1, 1).Font.Bold = TRUE
objExcel.Cells(1, 2).Font.Bold = TRUE
objExcel.Cells(1, 3).Font.Bold = TRUE
objExcel.Cells(1, 4).Font.Bold = TRUE
objExcel.Cells(1, 5).Font.Bold = TRUE
objExcel.Cells(1, 6).Font.Bold = TRUE
objExcel.Cells(1, 7).Font.Bold = TRUE
objExcel.Cells(1, 8).Font.Bold = TRUE
objExcel.Cells(1, 9).Font.Bold = TRUE
objExcel.Cells(1, 10).Font.Bold = TRUE
objExcel.Cells(1, 11).Font.Bold = TRUE
objExcel.Cells(1, 12).Font.Bold = TRUE
objExcel.Cells(1, 13).Font.Bold = TRUE
objExcel.Cells(1, 14).Font.Bold = TRUE
objExcel.Cells(1, 15).Font.Bold = TRUE
objExcel.Cells(1, 16).Font.Bold = TRUE




' == haal WMI data op  en schrijf weg ==
strComputer = "."
Set objWMIService = GetObject("winmgmts:" _
    & "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")
Set colSettings = objWMIService.ExecQuery _
    ("Select * from Win32_OperatingSystem")


For Each objOperatingSystem in colSettings 
    objExcel.Cells(2, 1).Value =  objOperatingSystem.Caption
    objExcel.Cells(2, 2).Value =  objOperatingSystem.Version
    objExcel.Cells(2, 3).Value =  objOperatingSystem.TotalVirtualMemorySize
    objExcel.Cells(2, 4).Value =  objOperatingSystem.WindowsDirectory


Next

Set colSettings = objWMIService.ExecQuery _
    ("Select * from Win32_ComputerSystem")

For Each objComputer in colSettings 
    objExcel.Cells(2, 5).Value = objComputer.Name
Next

Set colItems = objWMIService.ExecQuery _
    ("Select * From Win32_NetworkAdapterConfiguration Where IPEnabled = True")

For Each objItem in colItems
    objExcel.Cells(2, 6).Value =  objItem.MACAddress

Next

Set objWMIService = getObject("winmgmts:" &_
     "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")
Set colBIOS = objWMIService.ExecQuery ("Select * from Win32_SystemEnclosure")

For Each objBIOS in colBIOS
    objExcel.Cells(2, 7).Value =  objBIOS.SerialNumber






Next

Set colItems = objWMIService.ExecQuery("Select * from Win32_ComputerSystem",,48)
For Each objItem In colItems

    objExcel.Cells(2, 8).Value =  objItem.Manufacturer

Next
Set colItems = objWMIService.ExecQuery("Select * from Win32_ComputerSystem",,48)
For Each objItem In colItems

    objExcel.Cells(2, 9).Value =  objItem.Model

Next
Set objWMIService = GetObject("winmgmts:" _
& "{impersonationLevel=impersonate}!\\" _ 
& strComputer & "\root\cimv2") 

Set colComputer = objWMIService.ExecQuery _
("Select * from Win32_ComputerSystem")

For Each objComputer in colComputer 
    objExcel.Cells(2, 10).Value =  int((objComputer.TotalPhysicalMemory) /1048576)+9 & " MB"


Next
Set objWMIService = GetObject("winmgmts:\\" _
& strComputer & "\root\cimv2")
Set colItems = objWMIService.ExecQuery(_
"Select * from Win32_DiskDrive")

For Each objItem in colItems
    objExcel.Cells(2, 11).Value = Int(objItem.Size /(1073741824)) & " GB"





Next
Const Impersonate = "winmgmts:{impersonationLevel=impersonate}!\\"
computer = "."
Set oWMI = GetObject(Impersonate & computer & "\root\cimv2")
Set QueryWMI = oWMI.ExecQuery("SELECT * FROM Win32_OperatingSystem")

For Each oItem In QueryWMI
    objExcel.Cells(2, 12).Value = oItem.ServicePackMajorVersion


Next


Set wshShell = WScript.CreateObject( "WScript.Shell" )
strUserName = wshShell.ExpandEnvironmentStrings( "%USERNAME%" )
objExcel.Cells(2, 13).Value =  strUserName


Set objWMIService = GetObject("winmgmts:" _
    & "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")

set colItems = objWMIService.ExecQuery("Select * from Win32_Processor")

For Each objItem in colItems
    objExcel.Cells(2, 14).Value = objItem.Name
    





Next
Set objRange = objExcel.Range("A1")
objRange.Activate
Set objRange = objExcel.ActiveCell.EntireColumn
objRange.Autofit()


Set objRange = objExcel.Range("A1")
objRange.Activate
Set objRange = objExcel.ActiveCell.EntireColumn
objRange.Autofit()

Set objRange = objExcel.Range("A1")
objRange.Activate
Set objRange = objExcel.ActiveCell.EntireColumn
objRange.Autofit()


Set objRange = objExcel.Range("B1")
objRange.Activate
Set objRange = objExcel.ActiveCell.EntireColumn
objRange.Autofit()


Set objRange = objExcel.Range("C1")
objRange.Activate
Set objRange = objExcel.ActiveCell.EntireColumn
objRange.Autofit()


Set objRange = objExcel.Range("D1")
objRange.Activate
Set objRange = objExcel.ActiveCell.EntireColumn
objRange.Autofit()


Set objRange = objExcel.Range("E1")
objRange.Activate
Set objRange = objExcel.ActiveCell.EntireColumn
objRange.Autofit()

Set objRange = objExcel.Range("F1")
objRange.Activate
Set objRange = objExcel.ActiveCell.EntireColumn
objRange.Autofit()

Set objRange = objExcel.Range("G1")
objRange.Activate
Set objRange = objExcel.ActiveCell.EntireColumn
objRange.Autofit()

Set objRange = objExcel.Range("H1")
objRange.Activate
Set objRange = objExcel.ActiveCell.EntireColumn
objRange.Autofit()


Set objRange = objExcel.Range("I1")
objRange.Activate
Set objRange = objExcel.ActiveCell.EntireColumn
objRange.Autofit()

Set objRange = objExcel.Range("J1")
objRange.Activate
Set objRange = objExcel.ActiveCell.EntireColumn
objRange.Autofit()


Set objRange = objExcel.Range("K1")
objRange.Activate
Set objRange = objExcel.ActiveCell.EntireColumn
objRange.Autofit()


Set objRange = objExcel.Range("L1")
objRange.Activate
Set objRange = objExcel.ActiveCell.EntireColumn
objRange.Autofit()


Set objRange = objExcel.Range("M1")
objRange.Activate
Set objRange = objExcel.ActiveCell.EntireColumn
objRange.Autofit()


Set objRange = objExcel.Range("N1")
objRange.Activate
Set objRange = objExcel.ActiveCell.EntireColumn
objRange.Autofit()


Set objRange = objExcel.Range("O1")
objRange.Activate
Set objRange = objExcel.ActiveCell.EntireColumn
objRange.Autofit()


Set objRange = objExcel.Range("P1")
objRange.Activate
Set objRange = objExcel.ActiveCell.EntireColumn
objRange.Autofit()

zondag 2 mei 2010 01:53

Acties:
  • 0 Henk 'm!
  • pieturp
  • Registratie: April 2004
  • Laatst online: 27-08 14:18

pieturp

gaffa!

Visual Basic:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

' == Text Opmaken ==
objExcel.Cells(1, 1).Font.Bold = TRUE
objExcel.Cells(1, 2).Font.Bold = TRUE
objExcel.Cells(1, 3).Font.Bold = TRUE
objExcel.Cells(1, 4).Font.Bold = TRUE
objExcel.Cells(1, 5).Font.Bold = TRUE
objExcel.Cells(1, 6).Font.Bold = TRUE
objExcel.Cells(1, 7).Font.Bold = TRUE
objExcel.Cells(1, 8).Font.Bold = TRUE
objExcel.Cells(1, 9).Font.Bold = TRUE
objExcel.Cells(1, 10).Font.Bold = TRUE
objExcel.Cells(1, 11).Font.Bold = TRUE
objExcel.Cells(1, 12).Font.Bold = TRUE
objExcel.Cells(1, 13).Font.Bold = TRUE
objExcel.Cells(1, 14).Font.Bold = TRUE
objExcel.Cells(1, 15).Font.Bold = TRUE
objExcel.Cells(1, 16).Font.Bold = TRUE


_O-

Om over de rest maar te zwijgen :F

... en etcetera en zo

zondag 2 mei 2010 02:46

Acties:
  • 0 Henk 'm!
  • Freeaqingme
  • Registratie: April 2006
  • Laatst online: 12-09 16:28

Freeaqingme

VBScript kan toch wel loop structures aan? Ik zou eens kritisch naar je code proberen te kijken, en kijk dan of je 't niet met heel veel minder regels af kan.

Bijvoorbeeld:
code:
1
2
3

for(i = 1; i < 17; i++) {
          objExcel.Cells(1, i).Font.Bold = TRUE;
}

No trees were harmed in creating this message. However, a large number of electrons were terribly inconvenienced.

zondag 2 mei 2010 10:36

Acties:
  • 0 Henk 'm!
  • pieturp
  • Registratie: April 2004
  • Laatst online: 27-08 14:18

pieturp

gaffa!

Sorry, vorige post was misschien wat flauw.
Maar seriously: DRY; Don't repeat yourself. Als je aan 't proggen bent en je bent aan 't copy-pasten, denk dan echt "dit kan handiger"!

Zie bijvoorbeeld de post hierboven.

... en etcetera en zo

Pagina: 1
Reageer

Apple iPhone 16e LG OLED evo G5 Google Pixel 10 Samsung Galaxy S25 Star Wars: Outlaws Nintendo Switch 2 Apple AirPods Pro (2e generatie) Sony PlayStation 5 Pro

Tweakers is onderdeel van DPG Media B.V.
Alle rechten voorbehouden - Auteursrecht © 1998 - 2025 • Hosting door TrueFullstaq