Solved I need help editing a script file to display system info

June 6, 2012 at 09:46:08
Specs: Windows XP
Ladies and Gentlemen,

I need help in editing the below script.

Here is the list of modifications:

1. I need it to scan a text file of computer names instead of using an input box to retrieve the system information
2. I need the file to output with the headings going across the top (A1, B1, C1, etc) instead of listing out in column (A1, A2, , A3, etc)

The script to be edited can be found below.

Thanks in advance for all your help.
----------------------------------------------------------------------------------------------------------------------------

strComputer = InputBox ("Enter Machine Name")

Set objExcel = CreateObject("Excel.Application")

objExcel.Visible = True

objExcel.Workbooks.Add

objExcel.Cells(1, 1).Value = "Name"

objExcel.Cells(2, 1).Value = "Caption"

objExcel.Cells(3, 1).Value = "Version"

objExcel.Cells(4, 1).Value = "Registered User"

objExcel.Cells(5, 1).Value = "Serial Number"

objExcel.Cells(6, 1).Value = "CSD Version"

objExcel.Cells(7, 1).Value = "Description"

objExcel.Cells(8, 1).Value = "Last Boot Up Time"

objExcel.Cells(9, 1).Value = "Local Date Time"

objExcel.Cells(10, 1).Value = "Organization"

objExcel.Cells(11, 1).Value = "Domain"

objExcel.Cells(12, 1).Value = "Manufacturer"

objExcel.Cells(13, 1).Value = "Model"

objExcel.Cells(14, 1).Value = "Number Of Processors"

objExcel.Cells(15, 1).Value = "Primary Owner Name"

objExcel.Cells(16, 1).Value = "System Type"

objExcel.Cells(17, 1).Value = "Total Physical Memory"

objExcel.Cells(18, 1).Value = "User Name"

objExcel.Cells(19, 1).Value = "Caption"

objExcel.Cells(20, 1).Value = "Manufacturer"

objExcel.Cells(21, 1).Value = "Name"

objExcel.Cells(22, 1).Value = "Release Date"

objExcel.Cells(23, 1).Value = "Serial Number"

objExcel.Cells(24, 1).Value = "SMBIOS BIOS Version"

objExcel.Cells(25, 1).Value = "Version"

Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")

Set colItems = objWMIService.ExecQuery("Select * from Win32_OperatingSystem")

For Each objItem in colItems

objExcel.Cells(1, 2).Value = objItem.CSName

objExcel.Cells(2, 2).Value = objItem.Caption

objExcel.Cells(3, 2).Value = objItem.Version

objExcel.Cells(4, 2).Value = objItem.RegisteredUser

objExcel.Cells(5, 2).Value = objItem.SerialNumber

objExcel.Cells(6, 2).Value = objItem.CSDVersion

objExcel.Cells(7, 2).Value = objItem.Description

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

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

objExcel.Cells(10, 2).Value = objItem.Organization

Next

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

For Each objItem in colItems

objExcel.Cells(11, 2).Value = objItem.Domain

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

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

objExcel.Cells(14, 2).Value = objItem.NumberOfProcessors

objExcel.Cells(15, 2).Value = objItem.PrimaryOwnerName

objExcel.Cells(16, 2).Value = objItem.SystemType

objExcel.Cells(17, 2).Value = (objItem.TotalPhysicalMemory /1024) & " MB"

objExcel.Cells(18, 2).Value = objItem.UserName

Next

Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")

Set colItems = objWMIService.ExecQuery("Select * from Win32_BIOS")

For Each objItem in colItems

objExcel.Cells(19, 2).Value = objItem.Caption

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

objExcel.Cells(21, 2).Value = objItem.Name

objExcel.Cells(22, 2).Value = objItem.ReleaseDate

objExcel.Cells(23, 2).Value = objItem.SerialNumber

objExcel.Cells(24, 2).Value = objItem.SMBIOSBIOSVersion

objExcel.Cells(25, 2).Value = objItem.Version

Next

objExcel.Range("A1:A25").Select

objExcel.Selection.Font.ColorIndex = 11

objExcel.Selection.Font.Bold = True

objExcel.Cells.EntireColumn.AutoFit

MsgBox "Done"


See More: I need help editing a script file to display system info

Report •


#1
June 6, 2012 at 09:57:21
✔ Best Answer
Swap the numbers around in all of the Cells(), so that "Cells(25, 2)" becomes "Cells(2,25)".
Then change Range("A1:A25") to Range("1:1")

How To Ask Questions The Smart Way


Report •

#2
June 6, 2012 at 10:31:21
Thanks Razor2.3!

I tried it and it worked. Great stuff!

I need to find out how to scan a text file of computer names instead of using an input box to retrieve the system information.



Report •

#3
June 11, 2012 at 07:12:18
Hi Ladies and Gentlemen,

Can anyone else assist me with my request?

Regards,
Nedd


Report •

Related Solutions

#4
June 18, 2012 at 06:46:17
I'm not a fan of this piecemeal development, nor am I a particular fan of bumping posts. So before I spend my time rewriting the script, what else does it need to do?

How To Ask Questions The Smart Way


Report •

#5
June 18, 2012 at 09:41:19
Hi Razor2.3,

Sorry about that! I also need it to pull the "published at" info from active directory, that is where the computer is located in the OU.

Thanks for everything.

Nedd


Report •

#6
June 18, 2012 at 13:21:47
Largely untested. Also assumes servers exist in your AD domain.
Const filePath = "some.txt"

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
objExcel.Workbooks.Add
objExcel.Cells(1) = "Name"
objExcel.Cells(2) = "Caption"
objExcel.Cells(3) = "Version"
objExcel.Cells(4) = "Registered User"
objExcel.Cells(5) = "Serial Number"
objExcel.Cells(6) = "CSD Version"
objExcel.Cells(7) = "Description"
objExcel.Cells(8) = "Last Boot Up Time"
objExcel.Cells(9) = "Local Date Time"
objExcel.Cells(10) = "Organization"
objExcel.Cells(11) = "Domain"
objExcel.Cells(12) = "Manufacturer"
objExcel.Cells(13) = "Model"
objExcel.Cells(14) = "Number Of Processors"
objExcel.Cells(15) = "Primary Owner Name"
objExcel.Cells(16) = "System Type"
objExcel.Cells(17) = "Total Physical Memory"
objExcel.Cells(18) = "User Name"
objExcel.Cells(19) = "Caption"
objExcel.Cells(20) = "Manufacturer"
objExcel.Cells(21) = "Name"
objExcel.Cells(22) = "Release Date"
objExcel.Cells(23) = "Serial Number"
objExcel.Cells(24) = "SMBIOS BIOS Version"
objExcel.Cells(25) = "Version"
objExcel.Cells(26) = "Published At"
With objExcel.Range("1:1")
  .Font.ColorIndex = 11
  .Font.Bold = True
End With

domain = "<LDAP://" & GetObject("LDAP://rootDSE").Get("defaultNamingContext") & ">"
Set pn = CreateObject("Pathname")
Set com = CreateObject("ADODB.Command")
com.ActiveConnection = "Provider=ADsDSOObject"
rowCnt = 1

With CreateObject("Scripting.FileSystemObject").OpenTextFile(filePath)
  Do Until .AtEndOfStream
    rowCnt = rowCnt + 1
    Set row = objExcel.Rows(rowCnt)
    strComputer = Trim(.ReadLine)
    Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
    
    Set colItems = objWMIService.ExecQuery("Select * from Win32_OperatingSystem")
    For Each objItem in colItems
      row.Cells(1) = objItem.CSName
      row.Cells(2) = objItem.Caption
      row.Cells(3) = objItem.Version
      row.Cells(4) = objItem.RegisteredUser
      row.Cells(5) = objItem.SerialNumber
      row.Cells(6) = objItem.CSDVersion
      row.Cells(7) = objItem.Description
      row.Cells(8) = objItem.LastBootUpTime
      row.Cells(9) = objItem.LocalDateTime
      row.Cells(10) = objItem.Organization
    Next
    
    Set colItems = objWMIService.ExecQuery("Select * from Win32_ComputerSystem")
    For Each objItem in colItems
      row.Cells(11) = objItem.Domain
      row.Cells(12) = objItem.Manufacturer
      row.Cells(13) = objItem.Model
      row.Cells(14) = objItem.NumberOfProcessors
      row.Cells(15) = objItem.PrimaryOwnerName
      row.Cells(16) = objItem.SystemType
      row.Cells(17) = (objItem.TotalPhysicalMemory /1024) & " MB"
      row.Cells(18) = objItem.UserName
    Next
    
    Set colItems = objWMIService.ExecQuery("Select * from Win32_BIOS")
    For Each objItem in colItems
      row.Cells(19) = objItem.Caption
      row.Cells(20) = objItem.Manufacturer
      row.Cells(21) = objItem.Name
      row.Cells(22) = objItem.ReleaseDate
      row.Cells(23) = objItem.SerialNumber
      row.Cells(24) = objItem.SMBIOSBIOSVersion
      row.Cells(25) = objItem.Version
    Next
    
    com.CommandText = domain & ";(&(objectCategory=computer)(cn=" & strComputer _
        & "));ADsPath;subtree"
    Set rs = com.Execute
    If Not rs.EOF Then
      pn.Set rs(0), 1
      row.Cells(26) = pn.Retrieve(4)
    Else
      row.Cells(26) = "* * * Not Found * * *"
      row.Cells(26).Font.ColorIndex = 3
    End If
  Loop
End With

objExcel.Cells.EntireColumn.AutoFit
MsgBox "Done"

How To Ask Questions The Smart Way


Report •

#7
June 18, 2012 at 13:33:27
Hi Razor2.3,

Thank you so much! This script works fantastic.
Enjoy the balance of your day.

Nedd


Report •

Ask Question