• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 364
  • Last Modified:

Hardware Inventory Script for multiple hosts

Could someone help me modify this script? I would like to add the hard disk drive capacity AND read the computer names from a text file.
http://www.experts-http://www.experts-exchange.com/Programming/Languages/.NET/Visual_Basic.NET/Q_22585270.html?sfQueryTermInfo=1+hardwar+script+wmi

Thanks so much
0
briehill
Asked:
briehill
  • 6
  • 4
  • 2
1 Solution
 
rejoinderCommented:
Can you this script and see if it meets your needs.
The text file must have one computer name only per line.
'===========================
Option Explicit
 
Const ForReading = 1
 
'On Error Resume Next
Dim objFSO, objFSO1, wshNetwork, strComputer, objWMIService, colItems, strSerialNumber, strFileName, strResults, objItem, objOutputFile, strCPUDetails, strHDDDetails, strFilePath, objFile
 
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFSO1 = CreateObject("Scripting.FileSystemObject")
'Type the location of text file containing computers names
strFilePath = "Your-file-containing-computer-names.txt"
 
Set objFile = objFSO1.OpenTextFile (strFilePath, ForReading)
Do Until objFile.AtEndOfStream
    strComputer = objFile.Readline
    Set WshNetwork = WScript.CreateObject("WScript.Network")
 
    ' Get the serial number first to see if it already exists in the spreadsheet
    Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
    Set colItems = objWMIService.ExecQuery("Select * from Win32_BIOS",,48)
    For Each objItem In colItems
        strSerialNumber = "" & Trim(objItem.SerialNumber)
    Next
    Set objWMIService = Nothing
    Set colItems = Nothing
 
    strFileName = "\\NTFP\Rsampson\Scripting\Test Scripts\Hardware Inventory Script\" & strSerialNumber & ".txt"
 
    ' Create Excel Spreadsheet
    strResults = "Computername;Username;Manufacturer;Model;Serial Number;CPU;CPU Speed;Operating System;Service Pack;Total Memory;HDD;Audit Date" & VbCrLf
 
    ' Get Computer System Details
    Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
    Set colItems = objWMIService.ExecQuery("Select * from Win32_ComputerSystem",,48)
    For Each objItem In colItems
        strResults = strResults & Trim(objItem.Caption)  & ";" & Trim(objItem.UserName) & ";" & Trim(objItem.Manufacturer) & ";" & Trim(objItem.Model)
    Next
    Set objWMIService = Nothing
    Set colItems = Nothing
    '
    'Output the Serial Number
    strResults = strResults & ";" & strSerialNumber
 
    '
    ' Get CPU Details
    Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
    Set colItems = objWMIService.ExecQuery("Select * from Win32_Processor",,48)
    For Each objItem In colItems
        strCPUDetails = Trim(objItem.Name) & ";" & Trim(objItem.CurrentClockSpeed)
    Next
    Set objWMIService = Nothing
    Set colItems = Nothing
    strResults = strResults & ";" & strCPUDetails
    '
    ' Get OS Details
    Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
    Set colItems = objWMIService.ExecQuery("Select * from Win32_OperatingSystem",,48)
    For Each objItem In colItems
        strResults = strResults & ";" & Trim(objItem.Caption) & ";" & Trim(objItem.CSDVersion) & ";" & Trim(FormatNumber(objItem.TotalVisibleMemorySize/1024,0))
    Next
    Set objWMIService = Nothing
    Set colItems = Nothing
    '
    ' Get HDD Details
    Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
    Set colItems = objWMIService.ExecQuery("Select * from win32_LogicalDisk",,48)
    For Each objItem In colItems
        strHDDDetails = strHDDDetails & ";" & Trim(objItem.Caption) & ";" & Trim(objItem.size)
    Next
    Set objWMIService = Nothing
    Set colItems = Nothing
    strResults = strResults & strHDDDetails
    '
    ' Get & Writeout current Date
    strResults = strResults & ";" & Trim(Day(Now) & "-" & Month(Now) & "-" & Year(Now))
 
    Set objOutputFile = objFSO.CreateTextFile(strFileName, True)
    objOutputFile.Write strResults
    objOutputFile.Close
 
    Set objOutputFile = Nothing
    Set objFSO = Nothing
Loop
 
Set objFile = Nothing
Set objFSO1 = Nothing
 
MsgBox "Done"
'=====================

Open in new window

0
 
briehillAuthor Commented:
no, it creates a txt file for each system. i'd like the script to write the results of the hardware inventory for each computer in one excel spreadsheet. the script that i reference in my original post uses the strComputer = "."  which I think means the local host. I need to capture this information for several computers on my network.  I'd also like to add a column to the spreadsheet for the hard disk size.
0
 
rejoinderCommented:
Can you please provide a sample of the Excel spreadsheet please?
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
briehillAuthor Commented:
sure, i've attached both the script and the spreadsheet that it writes to.
Set WshNetwork = WScript.CreateObject("WScript.Network")
 
strComputer = "."
strWorkBook = "D:\Scripting\Hardware.xls"
 
' Create Excel Spreadsheet
Set app = CreateObject("Excel.Application")
app.Workbooks.Open strWorkBook
Set wb = app.ActiveWorkbook
app.Visible = False
wb.Activate
Set ws = wb.Worksheets(1)
ws.Cells(1,1).Value = "Computername"
ws.Columns(1).ColumnWidth = 20
ws.Cells(1,2).Value = "Username"
ws.Columns(2).ColumnWidth = 30
ws.Cells(1,3).Value = "Manufacturer"
ws.Columns(3).ColumnWidth = 20
ws.Cells(1,4).Value = "Model"
ws.Columns(4).ColumnWidth = 20
ws.Cells(1,5).Value = "Serial Number"
ws.Columns(5).ColumnWidth = 30
ws.Cells(1,6).Value = "CPU"
ws.Columns(6).ColumnWidth = 30
ws.Cells(1,7).Value = "CPU Speed"
ws.Columns(7).ColumnWidth = 10
ws.Cells(1,8).Value = "Operating System"
ws.Columns(8).ColumnWidth = 40
ws.Cells(1,9).Value = "Service Pack"
ws.Columns(9).ColumnWidth = 20
ws.Cells(1,10).Value = "Total Memory"
ws.Columns(10).ColumnWidth = 20
ws.Cells(1,11).Value = "Audit Date"
ws.Columns(11).ColumnWidth = 20
ws.Rows(1).Font.Bold = True
 
' Get the serial number first to see if it already exists in the spreadsheet
Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
Set colItems = objWMIService.ExecQuery("Select * from Win32_BIOS",,48)
For Each objItem In colItems
strSerialNumber = "" & objItem.SerialNumber
Next
Set objWMIService = Nothing
Set colItems = Nothing
 
intRowToUse = -1
For intRowCount = 2 To ws.UsedRange.Rows.Count
      If Trim(strSerialNumber) = Trim(ws.Cells(intRowCount,5).Value) Then
            intRowToUse = intRowCount
      End If
Next
If intRowToUse = -1 Then
      intRowToUse = ws.UsedRange.Rows.Count + 1
End If
 
' Get Computer System Details
Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
Set colItems = objWMIService.ExecQuery("Select * from Win32_ComputerSystem",,48)
For Each objItem In colItems
ws.Cells(intRowToUse,1).Value = "" & objItem.Caption 
    ws.Cells(intRowToUse,2).Value = "" & objItem.UserName
    ws.Cells(intRowToUse,3).Value = "" & objItem.Manufacturer
    ws.Cells(intRowToUse,4).Value = "" & objItem.Model
Next
Set objWMIService = Nothing
Set colItems = Nothing
'
'Output the Serial Number
ws.Cells(intRowToUse,5).Value = strSerialNumber
 
'
' Get CPU Details
Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
Set colItems = objWMIService.ExecQuery("Select * from Win32_Processor",,48)
For Each objItem In colItems
ws.Cells(intRowToUse,6).Value = "" & objItem.Name 
    ws.Cells(intRowToUse,7).Value = "" & objItem.CurrentClockSpeed 
Next
Set objWMIService = Nothing
Set colItems = Nothing
'
' Get OS Details
Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
Set colItems = objWMIService.ExecQuery("Select * from Win32_OperatingSystem",,48)
For Each objItem In colItems
    ws.Cells(intRowToUse,8).Value = "" & objItem.Caption
    ws.Cells(intRowToUse,9).Value = "" & objItem.CSDVersion 
    ws.Cells(intRowToUse,10).Value = "" & FormatNumber(objItem.TotalVisibleMemorySize/1024,0)   
Next
Set objWMIService = Nothing
Set colItems = Nothing
'
' Get & Writeout current Date
ws.Cells(intRowToUse,11).value = "" & Day(Now) & "-" & Month(Now) & "-" & Year(Now)
 
'Autofit all columns
app.ActiveSheet.Columns.EntireColumn.AutoFit
' Save Audit File
app.DisplayAlerts = False
'wb.Saved = True
wb.Close True
app.DisplayAlerts = False
app.quit
 
MsgBox "Done"
'=====================

Open in new window

hardware.xls
0
 
rejoinderCommented:
Here you are.  This will add a new column for HDD Capacity.  To poll other computers, make a text file with one machine name per line.  On line 4 edit the path to point to the text file for reading.

Const ForReading = 1
 
' Path to read machine names from
strFilePath = "Your-file-containing-computer-names.txt"
 
' Create Excel Spreadsheet
strWorkBook = "D:\Scripting\Hardware.xls"
Set app = CreateObject("Excel.Application")
app.Workbooks.Open strWorkBook
Set wb = app.ActiveWorkbook
app.Visible = False
wb.Activate
Set ws = wb.Worksheets(1)
with ws
    .Cells(1,1).Value = "Computername"
    .Columns(1).ColumnWidth = 20
    .Cells(1,2).Value = "Username"
    .Columns(2).ColumnWidth = 30
    .Cells(1,3).Value = "Manufacturer"
    .Columns(3).ColumnWidth = 20
    .Cells(1,4).Value = "Model"
    .Columns(4).ColumnWidth = 20
    .Cells(1,5).Value = "Serial Number"
    .Columns(5).ColumnWidth = 30
    .Cells(1,6).Value = "CPU"
    .Columns(6).ColumnWidth = 30
    .Cells(1,7).Value = "CPU Speed"
    .Columns(7).ColumnWidth = 10
    .Cells(1,8).Value = "Operating System"
    .Columns(8).ColumnWidth = 40
    .Cells(1,9).Value = "Service Pack"
    .Columns(9).ColumnWidth = 20
    .Cells(1,10).Value = "Total Memory"
    .Columns(10).ColumnWidth = 20
    .Cells(1,11).Value = "HDD Capacity"
    .Columns(11).ColumnWidth = 40
    .Cells(1,12).Value = "Audit Date"
    .Columns(12).ColumnWidth = 20
    .Rows(1).Font.Bold = True
end with
 
Set WshNetwork = WScript.CreateObject("WScript.Network")
 
Set objFSO  = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile (strFilePath, ForReading)
Do Until objFile.AtEndOfStream
    strComputer = objFile.Readline
    ' Get the serial number first to see if it already exists in the spreadsheet
    Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
    Set colItems = objWMIService.ExecQuery("Select * from Win32_BIOS",,48)
    For Each objItem In colItems
        strSerialNumber = objItem.SerialNumber
    Next
    Set objWMIService = Nothing
    Set colItems = Nothing
 
    intRowToUse = -1
    For intRowCount = 2 To ws.UsedRange.Rows.Count
        If Trim(strSerialNumber) = Trim(ws.Cells(intRowCount,5).Value) Then
            intRowToUse = intRowCount
        End If
    Next
    If intRowToUse = -1 Then
        intRowToUse = ws.UsedRange.Rows.Count + 1
    End If
 
    ' Get Computer System Details
    Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
    Set colItems = objWMIService.ExecQuery("Select * from Win32_ComputerSystem",,48)
    For Each objItem In colItems
        with ws
            .Cells(intRowToUse,1).Value = objItem.Caption 
            .Cells(intRowToUse,2).Value = objItem.UserName
            .Cells(intRowToUse,3).Value = objItem.Manufacturer
            .Cells(intRowToUse,4).Value = objItem.Model
        end with
    Next
    Set objWMIService = Nothing
    Set colItems = Nothing
    
    ' Output the Serial Number
    ws.Cells(intRowToUse,5).Value = strSerialNumber
 
    ' Get CPU Details
    Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
    Set colItems = objWMIService.ExecQuery("Select * from Win32_Processor",,48)
    For Each objItem In colItems
        with ws
            .Cells(intRowToUse,6).Value = objItem.Name 
            .Cells(intRowToUse,7).Value = objItem.CurrentClockSpeed 
        end with
    Next
    Set objWMIService = Nothing
    Set colItems = Nothing
    
    ' Get OS Details
    Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
    Set colItems = objWMIService.ExecQuery("Select * from Win32_OperatingSystem",,48)
    For Each objItem In colItems
        with ws
            .Cells(intRowToUse,8).Value = objItem.Caption
            .Cells(intRowToUse,9).Value = objItem.CSDVersion 
            .Cells(intRowToUse,10).Value = FormatNumber(objItem.TotalVisibleMemorySize/1024,0)   
        end with
    Next
    Set objWMIService = Nothing
    Set colItems = Nothing
    
    ' Get HDD Details
    strHDDDetails = ""
    Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
    Set colItems = objWMIService.ExecQuery("Select * from win32_LogicalDisk",,48)
    For Each objItem In colItems
        if NOT IsNull(objItem.size) then
            strSize = CStr(FormatNumber((objItem.size/1024/1024/1024),1)) & " GB"
        else
            strSize = "N/A"
        end if
        if objItem.DriveType = 3 then
            strHDDDetails = strHDDDetails & " " & Trim(objItem.Caption) & " " & Trim(strSize)
        end if
    Next
    Set objWMIService = Nothing
    Set colItems = Nothing
    
    ' Output the HDD details
    ws.Cells(intRowToUse,11).Value = strHDDDetails
    
    ' Get & Writeout current Date
    ws.Cells(intRowToUse,12).value = Day(Now) & "-" & Month(Now) & "-" & Year(Now)
loop 
' Autofit all columns
app.ActiveSheet.Columns.EntireColumn.AutoFit
' Save Audit File
app.DisplayAlerts = False
' wb.Saved = True
wb.Close True
app.DisplayAlerts = False
app.quit
 
MsgBox "Done"
'=====================

Open in new window

0
 
briehillAuthor Commented:
thank you; i will try it in the morning when i return to work. but before i do can you tell me if the script will hang if it can't collect the information from one of the computers listed in the text file? in other words,  how do i tell the script to continue to the next computer in the file if it encounters an error or a computer is offline?
0
 
rejoinderCommented:
The script above will not move on if a machine is offline.  I will post a revised script shortly.
0
 
rejoinderCommented:
This version will log that there was an error.   Because a machine cannot be logged, the code was changed to append to the file rather than try and replace existing rows.  This was done because the original script was looking for a serial number match and would overwrite the line based on that information.  Since there is a chance you will not reach some machines, there is no way of knowing for sure what the serial number is of that machine.  This would start to add lines to the Excel file which would never be overwritten so I decided to append everything.  You can use the filter option in Excel to show you newly added records.
Const ForReading = 1
 
on error resume next
 
' Path to read machine names from
strFilePath = "Your-file-containing-computer-names.txt"
 
' Create Excel Spreadsheet
strWorkBook = "D:\Scripting\Hardware.xls"
Set app = CreateObject("Excel.Application")
app.Workbooks.Open strWorkBook
Set wb = app.ActiveWorkbook
app.Visible = False
wb.Activate
Set ws = wb.Worksheets(1)
with ws
    .Cells(1,1).Value = "Computername"
    .Columns(1).ColumnWidth = 20
    .Cells(1,2).Value = "Username"
    .Columns(2).ColumnWidth = 30
    .Cells(1,3).Value = "Manufacturer"
    .Columns(3).ColumnWidth = 20
    .Cells(1,4).Value = "Model"
    .Columns(4).ColumnWidth = 20
    .Cells(1,5).Value = "Serial Number"
    .Columns(5).ColumnWidth = 30
    .Cells(1,6).Value = "CPU"
    .Columns(6).ColumnWidth = 30
    .Cells(1,7).Value = "CPU Speed"
    .Columns(7).ColumnWidth = 10
    .Cells(1,8).Value = "Operating System"
    .Columns(8).ColumnWidth = 40
    .Cells(1,9).Value = "Service Pack"
    .Columns(9).ColumnWidth = 20
    .Cells(1,10).Value = "Total Memory"
    .Columns(10).ColumnWidth = 20
    .Cells(1,11).Value = "HDD Capacity"
    .Columns(11).ColumnWidth = 40
    .Cells(1,12).Value = "Audit Date"
    .Columns(12).ColumnWidth = 20
    .Rows(1).Font.Bold = True
end with
 
n = ws.UsedRange.Rows.Count
 
if err.number <> 0 then
    wb.Close True
    wscript.echo "Script could not open the Excel file." & vbCRLF & "Check the filename and try again."
    wscript.quit
end if
 
Set WshNetwork = WScript.CreateObject("WScript.Network")
 
Set objFSO  = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile (strFilePath, ForReading)
Do Until objFile.AtEndOfStream
    strComputer = objFile.Readline
    n = n + 1
    ' Get the serial number first to see if it already exists in the spreadsheet
    Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
    if err.number <> 0 then
        with ws
            .Cells(n,1).Value = "Cannot reach " & strComputer
            .Cells(n,12).value = Day(Now) & "-" & Month(Now) & "-" & Year(Now)
        end with
        err.clear
    else
        Set colItems = objWMIService.ExecQuery("Select * from Win32_BIOS",,48)
        For Each objItem In colItems
            strSerialNumber = objItem.SerialNumber
        Next
        Set objWMIService = Nothing
        Set colItems = Nothing
 
        ' Get Computer System Details
        Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
        Set colItems = objWMIService.ExecQuery("Select * from Win32_ComputerSystem",,48)
        For Each objItem In colItems
            with ws
                .Cells(n,1).Value = objItem.Caption 
                .Cells(n,2).Value = objItem.UserName
                .Cells(n,3).Value = objItem.Manufacturer
                .Cells(n,4).Value = objItem.Model
            end with
        Next
        Set objWMIService = Nothing
        Set colItems = Nothing
    
        ' Output the Serial Number
        ws.Cells(n,5).Value = strSerialNumber
 
        ' Get CPU Details
        Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
        Set colItems = objWMIService.ExecQuery("Select * from Win32_Processor",,48)
        For Each objItem In colItems
            with ws
                .Cells(n,6).Value = objItem.Name 
                .Cells(n,7).Value = objItem.CurrentClockSpeed 
            end with
        Next
        Set objWMIService = Nothing
        Set colItems = Nothing
    
        ' Get OS Details
        Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
        Set colItems = objWMIService.ExecQuery("Select * from Win32_OperatingSystem",,48)
        For Each objItem In colItems
            with ws
                .Cells(n,8).Value = objItem.Caption
                .Cells(n,9).Value = objItem.CSDVersion 
                .Cells(n,10).Value = FormatNumber(objItem.TotalVisibleMemorySize/1024,0)   
            end with
        Next
        Set objWMIService = Nothing
        Set colItems = Nothing
        
        ' Get HDD Details
        strHDDDetails = ""
        Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
        Set colItems = objWMIService.ExecQuery("Select * from win32_LogicalDisk",,48)
        For Each objItem In colItems
            if NOT IsNull(objItem.size) then
                strSize = CStr(FormatNumber((objItem.size/1024/1024/1024),1)) & " GB"
            else
                strSize = "N/A"
            end if
            if objItem.DriveType = 3 then
                strHDDDetails = strHDDDetails & " " & Trim(objItem.Caption) & " " & Trim(strSize)
            end if
        Next
        Set objWMIService = Nothing
        Set colItems = Nothing
        
        ' Output the HDD details
        ws.Cells(n,11).Value = strHDDDetails
      
        ' Get & Writeout current Date
        ws.Cells(n,12).value = Day(Now) & "-" & Month(Now) & "-" & Year(Now)
    end if
loop 
' Autofit all columns
app.ActiveSheet.Columns.EntireColumn.AutoFit
' Save Audit File
app.DisplayAlerts = False
' wb.Saved = True
wb.Close True
app.DisplayAlerts = False
app.quit
 
MsgBox "Done"
'=====================

Open in new window

0
 
briehillAuthor Commented:
It works perfectly! Thank you so much for your assistance, you are AWESOME<
0
 
NeriXsCommented:
Can you change that lines added during several executions is the one ranked below the other?

Currently when the script is started 2 times:

Pc1
Pc2
Pc3
Pc1
Pc2
Pc3

What I'd like:

Pc1
Pc1
Pc2
Pc2
Pc3
Pc3
0
 
rejoinderCommented:
This should do the trick...
Const ForReading = 1
 
on error resume next
 
' Path to read machine names from
strFilePath = "Your-file-containing-computer-names.txt"
 
' Create Excel Spreadsheet
strWorkBook = "D:\Scripting\Hardware.xls"
 
Set app = CreateObject("Excel.Application")
app.Workbooks.Open strWorkBook
Set wb = app.ActiveWorkbook
app.Visible = False
wb.Activate
Set ws = wb.Worksheets(1)
with ws
    .Cells(1,1).Value = "Computername"
    .Columns(1).ColumnWidth = 20
    .Cells(1,2).Value = "Username"
    .Columns(2).ColumnWidth = 30
    .Cells(1,3).Value = "Manufacturer"
    .Columns(3).ColumnWidth = 20
    .Cells(1,4).Value = "Model"
    .Columns(4).ColumnWidth = 20
    .Cells(1,5).Value = "Serial Number"
    .Columns(5).ColumnWidth = 30
    .Cells(1,6).Value = "CPU"
    .Columns(6).ColumnWidth = 30
    .Cells(1,7).Value = "CPU Speed"
    .Columns(7).ColumnWidth = 10
    .Cells(1,8).Value = "Operating System"
    .Columns(8).ColumnWidth = 40
    .Cells(1,9).Value = "Service Pack"
    .Columns(9).ColumnWidth = 20
    .Cells(1,10).Value = "Total Memory"
    .Columns(10).ColumnWidth = 20
    .Cells(1,11).Value = "HDD Capacity"
    .Columns(11).ColumnWidth = 40
    .Cells(1,12).Value = "Audit Date"
    .Columns(12).ColumnWidth = 20
    .Rows(1).Font.Bold = True
end with
 
n = ws.UsedRange.Rows.Count
 
if err.number <> 0 then
    wb.Close True
    wscript.echo "Script could not open the Excel file." & vbCRLF & "Check the filename and try again."
    wscript.quit
end if
 
Set WshNetwork = WScript.CreateObject("WScript.Network")
 
Set objFSO  = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile (strFilePath, ForReading)
Do Until objFile.AtEndOfStream
    strComputer = objFile.Readline
    n = n + 1
    ' Get the serial number first to see if it already exists in the spreadsheet
    Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
    if err.number <> 0 then
        with ws
            .Cells(n,1).Value = "Cannot reach " & strComputer
            .Cells(n,12).value = Day(Now) & "-" & Month(Now) & "-" & Year(Now)
        end with
        err.clear
    else
        Set colItems = objWMIService.ExecQuery("Select * from Win32_BIOS",,48)
        For Each objItem In colItems
            strSerialNumber = objItem.SerialNumber
        Next
        Set objWMIService = Nothing
        Set colItems = Nothing
 
        ' Get Computer System Details
        Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
        Set colItems = objWMIService.ExecQuery("Select * from Win32_ComputerSystem",,48)
        For Each objItem In colItems
            with ws
                .Cells(n,1).Value = trim(objItem.Caption)
                .Cells(n,2).Value = trim(objItem.UserName)
                .Cells(n,3).Value = trim(objItem.Manufacturer)
                .Cells(n,4).Value = trim(objItem.Model)
            end with
        Next
        Set objWMIService = Nothing
        Set colItems = Nothing
    
        ' Output the Serial Number
        ws.Cells(n,5).Value = strSerialNumber
 
        ' Get CPU Details
        Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
        Set colItems = objWMIService.ExecQuery("Select * from Win32_Processor",,48)
        For Each objItem In colItems
            with ws
                .Cells(n,6).Value = trim(objItem.Name)
                .Cells(n,7).Value = trim(objItem.CurrentClockSpeed)
            end with
        Next
        Set objWMIService = Nothing
        Set colItems = Nothing
    
        ' Get OS Details
        Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
        Set colItems = objWMIService.ExecQuery("Select * from Win32_OperatingSystem",,48)
        For Each objItem In colItems
            with ws
                .Cells(n,8).Value  = trim(objItem.Caption)
                .Cells(n,9).Value  = trim(objItem.CSDVersion)
                .Cells(n,10).Value = trim(FormatNumber(objItem.TotalVisibleMemorySize/1024,0))
            end with
        Next
        Set objWMIService = Nothing
        Set colItems = Nothing
        
        ' Get HDD Details
        strHDDDetails = ""
        Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
        Set colItems = objWMIService.ExecQuery("Select * from win32_LogicalDisk",,48)
        For Each objItem In colItems
            if NOT IsNull(objItem.size) then
                strSize = trim(CStr(FormatNumber((objItem.size/1024/1024/1024),1)) & " GB")
            else
                strSize = trim("N/A")
            end if
            if objItem.DriveType = 3 then
                strHDDDetails = strHDDDetails & " " & Trim(objItem.Caption) & " " & Trim(strSize)
            end if
        Next
        Set objWMIService = Nothing
        Set colItems = Nothing
        
        ' Output the HDD details
        ws.Cells(n,11).Value = trim(strHDDDetails)
      
        ' Get & Writeout current Date
        ws.Cells(n,12).value = trim(Day(Now) & "-" & Month(Now) & "-" & Year(Now))
    end if
loop 
' Autofit all columns
app.ActiveSheet.Columns.EntireColumn.AutoFit
' Sort
Set objRange = ws.UsedRange
Set objRange2 = ws.Range("A1")
objRange.Sort(objRange2)
' Save Audit File
app.DisplayAlerts = False
' wb.Saved = True
wb.Close True
app.DisplayAlerts = False
app.quit
 
MsgBox "Done"
'=====================

Open in new window

0
 
NeriXsCommented:
thank you rejoinder

This does not work correctly, the title line "computername, username, ..." is a copy every time! And classification of machinery in the text file is not respected.
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

  • 6
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now