Will not fill the excel spread sheet as expected

This topic contains 0 replies, has 1 voice, and was last updated by  Forums Archives 5 years, 11 months ago.

  • Author
    Posts
  • #5191

    by Sum4uandSum4u at 2012-10-10 21:20:10

    I have a script here and I am having a tough time getting the seventh tab to fill with the data expected. I can run the same line at the command line and the programs show up, yet they will not populate my excel spreadsheet. I am expecting the fields to fill with the software installed on the computer with the fields that I have labled, but nothing shows up. If I check the variable of $Software = gwmi Win32_Product -Comp $StrComputer -Credential $cred it shows all the software at the command line. Thanks in advance!

    [code2=powershell]Function WMILookupCred {
    foreach ($StrComputer in $colComputers){
    $GenItems1 = gwmi Win32_ComputerSystem -Comp $StrComputer -Credential $cred
    $GenItems2 = gwmi Win32_OperatingSystem -Comp $StrComputer -Credential $cred
    $SysItems1 = gwmi Win32_BIOS -Comp $StrComputer -Credential $cred
    $SysItems2 = gwmi Win32_TimeZone -Comp $StrComputer -Credential $cred
    $SysItems3 = gwmi Win32_WmiSetting -Comp $StrComputer -Credential $cred
    $ProcItems1 = gwmi Win32_Processor -Comp $StrComputer -Credential $cred
    $MemItems1 = gwmi Win32_PhysicalMemory -Comp $StrComputer -Credential $cred
    $memItems2 = gwmi Win32_PhysicalMemoryArray -Comp $StrComputer -Credential $cred
    $DiskItems = gwmi Win32_LogicalDisk -Comp $StrComputer -Credential $cred
    $Software = gwmi Win32_Product -Comp $StrComputer -Credential $cred
    $NetItems = gwmi Win32_NetworkAdapterConfiguration -Comp $StrComputer -Credential $cred |`
    where{$_.IPEnabled -eq "True"}

    # Populate General Sheet(1) with information
    foreach ($objItem in $GenItems1){
    $Sheet1.Cells.Item($intRow, 1) = $StrComputer
    Switch($objItem.DomainRole)
    {
    0{$Sheet1.Cells.Item($intRow, 3) = "Stand Alone Workstation"}
    1{$Sheet1.Cells.Item($intRow, 3) = "Member Workstation"}
    2{$Sheet1.Cells.Item($intRow, 3) = "Stand Alone Server"}
    3{$Sheet1.Cells.Item($intRow, 3) = "Member Server"}
    4{$Sheet1.Cells.Item($intRow, 3) = "Back-up Domain Controller"}
    5{$Sheet1.Cells.Item($intRow, 3) = "Primary Domain Controller"}
    default{"Undetermined"}
    }
    $Sheet1.Cells.Item($intRow, 4) = $objItem.Manufacturer
    $Sheet1.Cells.Item($intRow, 5) = $objItem.Model
    $Sheet1.Cells.Item($intRow, 6) = $objItem.SystemType
    $Sheet1.Cells.Item($intRow, 7) = $objItem.NumberOfProcessors
    $Sheet1.Cells.Item($intRow, 8) = $objItem.TotalPhysicalMemory / 1024 / 1024
    $Sheet1.Cells.Item($intRow, 2) = $objItem.UserName
    }
    foreach ($objItem in $GenItems2){
    $Sheet1.Cells.Item($intRow, 9) = $objItem.Caption
    $Sheet1.Cells.Item($intRow, 10) = $objItem.csdversion
    }

    #Populate Systems Sheet
    foreach ($objItem in $SysItems1){
    $Sheet2.Cells.Item($intRow, 1) = $StrComputer
    $Sheet2.Cells.Item($intRow, 2) = $objItem.Name
    $Sheet2.Cells.Item($intRow, 3) = $objItem.SMBIOSbiosVersion
    $Sheet2.Cells.Item($intRow, 4) = $objItem.SerialNumber
    }
    foreach ($objItem in $SysItems2){
    $Sheet2.Cells.Item($intRow, 5) = $objItem.Caption
    }
    foreach ($objItem in $SysItems3){
    $Sheet2.Cells.Item($intRow, 6) = $objItem.BuildVersion
    }

    #Populate Processor Sheet
    foreach ($objItem in $ProcItems1){
    $Sheet3.Cells.Item($intRowCPU, 1) = $StrComputer
    $Sheet3.Cells.Item($intRowCPU, 2) = $objItem.DeviceID+" "+$objItem.Name
    $Sheet3.Cells.Item($intRowCPU, 3) = $objItem.Description
    $Sheet3.Cells.Item($intRowCPU, 4) = $objItem.family
    $Sheet3.Cells.Item($intRowCPU, 5) = $objItem.currentClockSpeed
    $Sheet3.Cells.Item($intRowCPU, 6) = $objItem.l2cacheSize
    $Sheet3.Cells.Item($intRowCPU, 7) = $objItem.UpgradeMethod
    $Sheet3.Cells.Item($intRowCPU, 8) = $objItem.SocketDesignation
    $intRowCPU = $intRowCPU + 1
    }

    #Populate Memory Sheet
    $bankcounter = 1
    foreach ($objItem in $memItems2){
    $MemSlots = $objItem.MemoryDevices +1

    foreach ($objItem in $MemItems1){
    $Sheet4.Cells.Item($intRowMem, 1) = $StrComputer
    $Sheet4.Cells.Item($intRowMem, 2) = "Bank " +$bankcounter
    if($objItem.BankLabel -eq ""){
    $Sheet4.Cells.Item($intRowMem, 3) = $objItem.DeviceLocator}
    Else{$Sheet4.Cells.Item($intRowMem, 3) = $objItem.BankLabel}
    $Sheet4.Cells.Item($intRowMem, 4) = $objItem.Capacity/1024/1024
    $Sheet4.Cells.Item($intRowMem, 5) = $objItem.FormFactor
    $Sheet4.Cells.Item($intRowMem, 6) = $objItem.TypeDetail
    $intRowMem = $intRowMem + 1
    $bankcounter = $bankcounter + 1
    }
    while($bankcounter -lt $MemSlots)
    {
    $Sheet4.Cells.Item($intRowMem, 1) = $StrComputer
    $Sheet4.Cells.Item($intRowMem, 2) = "Bank " +$bankcounter
    $Sheet4.Cells.Item($intRowMem, 3) = "is Empty"
    $Sheet4.Cells.Item($intRowMem, 4) = ""
    $Sheet4.Cells.Item($intRowMem, 5) = ""
    $Sheet4.Cells.Item($intRowMem, 6) = ""
    $intRowMem = $intRowMem + 1
    $bankcounter = $bankcounter + 1
    }
    }

    #Populate Disk Sheet
    foreach ($objItem in $DiskItems){
    $Sheet5.Cells.Item($intRowDisk, 1) = $StrComputer
    Switch($objItem.DriveType)
    {
    2{$Sheet5.Cells.Item($intRowDisk, 2) = "Floppy"}
    3{$Sheet5.Cells.Item($intRowDisk, 2) = "Fixed Disk"}
    5{$Sheet5.Cells.Item($intRowDisk, 2) = "Removable Media"}
    default{"Undetermined"}
    }
    $Sheet5.Cells.Item($intRowDisk, 3) = $objItem.DeviceID
    $Sheet5.Cells.Item($intRowDisk, 4) = $objItem.Size/1024/1024
    $Sheet5.Cells.Item($intRowDisk, 5) = $objItem.FreeSpace/1024/1024
    $intRowDisk = $intRowDisk + 1
    }

    #Populate Network Sheet
    foreach ($objItem in $NetItems){
    $Sheet6.Cells.Item($intRowNet, 1) = $StrComputer
    $Sheet6.Cells.Item($intRowNet, 2) = $objItem.Caption+" (enabled)"
    $Sheet6.Cells.Item($intRowNet, 3) = $objItem.DHCPEnabled
    $Sheet6.Cells.Item($intRowNet, 4) = $objItem.IPAddress
    $Sheet6.Cells.Item($intRowNet, 5) = $objItem.IPSubnet
    $Sheet6.Cells.Item($intRowNet, 6) = $objItem.DefaultIPGateway
    $Sheet6.Cells.Item($intRowNet, 7) = $objItem.DNSServerSearchOrder
    $Sheet6.Cells.Item($intRowNet, 8) = $objItem.FullDNSRegistrationEnabled
    $Sheet6.Cells.Item($intRowNet, 9) = $objItem.WINSPrimaryServer
    $Sheet6.Cells.Item($intRowNet, 10) = $objItem.WINSSecondaryServer
    $Sheet6.Cells.Item($intRowNet, 11) = $objItem.WINSEnableLMHostsLookup
    $intRowNet = $intRowNet + 1
    }
    #Populate Software Sheet
    foreach ($objItem in $Software){
    $Sheet7.Cells.Item($intRowSoft, 1) = $StrComputer
    $Sheet7.Cells.Item($intRowSoft, 2) = $objItem.Name
    $Sheet7.Cells.Item($intRowSoft, 3) = $objItem.Vendor
    $Sheet7.Cells.Item($intRowSoft, 4) = $objItem.Version
    $Sheet7.Cells.Item($intRowSoft, 5) = $objItem.Caption
    }

    $intRow = $intRow + 1
    $intRowCPU = $intRowCPU + 1
    $intRowMem = $intRowMem + 1
    $intRowDisk = $intRowDisk + 1
    $intRowNet = $intRowNet + 1
    $intRowSoft = $intRowSoft + 1
    }
    }

    # =============================================================================================
    # Function Name 'ListComputers' – Enumerates ALL computer objects in AD
    # ==============================================================================================
    Function ListComputers {
    $strCategory = "computer"

    $objDomain = New-Object System.DirectoryServices.DirectoryEntry

    $objSearcher = New-Object System.DirectoryServices.DirectorySearcher
    $objSearcher.SearchRoot = $objDomain
    $objSearcher.Filter = ("(objectCategory=$strCategory)")

    $colProplist = "name"
    foreach ($i in $colPropList){$objSearcher.PropertiesToLoad.Add($i)}

    $colResults = $objSearcher.FindAll()

    foreach ($objResult in $colResults)
    {$objComputer = $objResult.Properties; $objComputer.name}
    }

    # ==============================================================================================
    # Function Name 'ListServers' – Enumerates ALL Servers objects in AD
    # ==============================================================================================
    Function ListServers {
    $strCategory = "computer"
    $strOS = "Windows*Server*"

    $objDomain = New-Object System.DirectoryServices.DirectoryEntry

    $objSearcher = New-Object System.DirectoryServices.DirectorySearcher
    $objSearcher.SearchRoot = $objDomain
    $objSearcher.Filter = ("(&(objectCategory=$strCategory)(OperatingSystem=$strOS))")

    $colProplist = "name"
    foreach ($i in $colPropList){$objSearcher.PropertiesToLoad.Add($i)}

    $colResults = $objSearcher.FindAll()

    foreach ($objResult in $colResults)
    {$objComputer = $objResult.Properties; $objComputer.name}
    }

    # ========================================================================
    # Function Name 'ListTextFile' – Enumerates Computer Names in a text file
    # Create a text file and enter the names of each computer. One computer
    # name per line. Supply the path to the text file when prompted.
    # ========================================================================
    Function ListTextFile {
    $strText = Read-Host "Enter the path for the text file"
    $colComputers = Get-Content $strText
    }

    # ========================================================================
    # Function Name 'SingleEntry' – Enumerates Computer from user input
    # ========================================================================
    Function ManualEntry {
    $colComputers = Read-Host "Enter Computer Name or IP"
    }

    # ==============================================================================================
    # Script Body
    # ==============================================================================================
    $erroractionpreference = "SilentlyContinue"

    #Gather info from user.
    Write-Host "********************************" -ForegroundColor Green
    Write-Host "Computer Inventory Script" -ForegroundColor Green
    Write-Host " -ForegroundColor Green
    Write-Host "Created: 09/21/2012" -ForegroundColor Green
    Write-Host "********************************" -ForegroundColor Green
    Write-Host " "
    Write-Host "Admin rights are required to enumerate information." -ForegroundColor Green
    Write-Host "Would you like to use an alternative credential?" -ForegroundColor Green
    $credResponse = Read-Host "[Y] Yes, [N] No"
    If($CredResponse -eq "y"){$cred = Get-Credential domain\username}
    Write-Host " "
    Write-Host "Which computer resources would you like in the report?" -ForegroundColor Green
    $strResponse = Read-Host "[3] Computer names from a File, [4] One Computer?"
    If($strResponse -eq "1"){$colComputers = ListComputers | Sort-Object}
    elseif($strResponse -eq "2"){$colComputers = ListServers | Sort-Object}
    elseif($strResponse -eq "3"){. ListTextFile}
    elseif($strResponse -eq "4"){. ManualEntry}
    else{Write-Host "You did not supply a correct response, `
    Please run script again." -foregroundColor Red}
    Write-Progress -Activity "Getting Inventory" -status "Running..." -id 1

    #New Excel Application
    $Excel = New-Object -Com Excel.Application
    $Excel.visible = $True

    # Create 6 worksheets
    $Excel = $Excel.Workbooks.Add()
    $Sheet = $Excel.Worksheets.Add()
    $Sheet = $Excel.Worksheets.Add()
    $Sheet = $Excel.Worksheets.Add()
    $Sheet = $Excel.Worksheets.Add()

    # Assign each worksheet to a variable and
    # name the worksheet.
    $Sheet1 = $Excel.Worksheets.Item(1)
    $Sheet2 = $Excel.WorkSheets.Item(2)
    $Sheet3 = $Excel.WorkSheets.Item(3)
    $Sheet4 = $Excel.WorkSheets.Item(4)
    $Sheet5 = $Excel.WorkSheets.Item(5)
    $Sheet6 = $Excel.WorkSheets.Item(6)
    $Sheet7 = $Excel.WorkSheets.Item(7)
    $Sheet1.Name = "General"
    $Sheet2.Name = "System"
    $Sheet3.Name = "Processor"
    $Sheet4.Name = "Memory"
    $Sheet5.Name = "Disk"
    $Sheet6.Name = "Network"
    $Sheet7.Name = "Software"

    #Create Heading for General Sheet
    $Sheet1.Cells.Item(1,1) = "Device_Name"
    $Sheet1.Cells.Item(1,2) = "Last Logged In"
    $Sheet1.Cells.Item(1,3) = "Role"
    $Sheet1.Cells.Item(1,4) = "HW_Make"
    $Sheet1.Cells.Item(1,5) = "HW_Model"
    $Sheet1.Cells.Item(1,6) = "HW_Type"
    $Sheet1.Cells.Item(1,7) = "CPU_Count"
    $Sheet1.Cells.Item(1,8) = "Memory_MB"
    $Sheet1.Cells.Item(1,9) = "Operating_System"
    $Sheet1.Cells.Item(1,10) = "SP_Level"

    #Create Heading for System Sheet
    $Sheet2.Cells.Item(1,1) = "Device_Name"
    $Sheet2.Cells.Item(1,2) = "BIOS_Name"
    $Sheet2.Cells.Item(1,3) = "BIOS_Version"
    $Sheet2.Cells.Item(1,4) = "HW_Serial_#"
    $Sheet2.Cells.Item(1,5) = "Time_Zone"
    $Sheet2.Cells.Item(1,6) = "WMI_Version"

    #Create Heading for Processor Sheet
    $Sheet3.Cells.Item(1,1) = "Device_Name"
    $Sheet3.Cells.Item(1,2) = "Processor(s)"
    $Sheet3.Cells.Item(1,3) = "Type"
    $Sheet3.Cells.Item(1,4) = "Family"
    $Sheet3.Cells.Item(1,5) = "Speed_MHz"
    $Sheet3.Cells.Item(1,6) = "Cache_Size_MB"
    $Sheet3.Cells.Item(1,7) = "Interface"
    $Sheet3.Cells.Item(1,8) = "#_of_Sockets"

    #Create Heading for Memory Sheet
    $Sheet4.Cells.Item(1,1) = "Device_Name"
    $Sheet4.Cells.Item(1,2) = "Bank_#"
    $Sheet4.Cells.Item(1,3) = "Label"
    $Sheet4.Cells.Item(1,4) = "Capacity_MB"
    $Sheet4.Cells.Item(1,5) = "Form"
    $Sheet4.Cells.Item(1,6) = "Type"

    #Create Heading for Disk Sheet
    $Sheet5.Cells.Item(1,1) = "Device_Name"
    $Sheet5.Cells.Item(1,2) = "Disk_Type"
    $Sheet5.Cells.Item(1,3) = "Drive_Letter"
    $Sheet5.Cells.Item(1,4) = "Capacity_MB"
    $Sheet5.Cells.Item(1,5) = "Free_Space_MB"

    #Create Heading for Network Sheet
    $Sheet6.Cells.Item(1,1) = "Device_Name"
    $Sheet6.Cells.Item(1,2) = "Network_Card"
    $Sheet6.Cells.Item(1,3) = "DHCP_Enabled"
    $Sheet6.Cells.Item(1,4) = "IP_Address"
    $Sheet6.Cells.Item(1,5) = "Subnet_Mask"
    $Sheet6.Cells.Item(1,6) = "Default_Gateway"
    $Sheet6.Cells.Item(1,7) = "DNS_Servers"
    $Sheet6.Cells.Item(1,8) = "DNS_Reg"
    $Sheet6.Cells.Item(1,9) = "Primary_WINS"
    $Sheet6.Cells.Item(1,10) = "Secondary_WINS"
    $Sheet6.Cells.Item(1,11) = "WINS_Lookup"

    #Create Heading for Software Sheet
    $Sheet7.Cells.Item(1,1) = "Machine Name"
    $Sheet7.Cells.Item(1,2) = "Vendor"
    $Sheet7.Cells.Item(1,3) = "Version"
    $Sheet7.Cells.Item(1,4) = "Caption"
    $colSheets = ($Sheet1, $Sheet2, $Sheet3, $Sheet4, $Sheet5, $Sheet6, $Sheet7)

    foreach ($colorItem in $colSheets){
    $intRow = 2
    $intRowCPU = 2
    $intRowMem = 2
    $intRowDisk = 2
    $intRowNet = 2
    $WorkBook = $colorItem.UsedRange
    $WorkBook.Interior.ColorIndex = 20
    $WorkBook.Font.ColorIndex = 11
    $WorkBook.Font.Bold = $True
    }

    If($credResponse -eq "y"){WMILookupCred}
    Else{WMILookup}

    #Auto Fit all sheets in the Workbook
    foreach ($colorItem in $colSheets){
    $WorkBook = $colorItem.UsedRange
    $WorkBook.EntireColumn.AutoFit()
    clear
    }[/code2]

    by JeffH at 2012-10-11 06:50:59

    The first thing to do is NOT set $errorActionPreference to "SilentlyContinue". If there are errors, perhaps from syntax, you aren't seeing them.

    by Sum4uandSum4u at 2012-10-11 07:07:07

    Thanks for the input. My GOD.... I have about 150 errors in the console!
    Here are a couple of the errors. I am looking now for a solution.
    ERROR: Exception setting "Item": "Exception from HRESULT: 0x800A03EC"
    CompInv_v2.ps1 (287): ERROR: At Line: 287 char: 21
    ERROR: + $Sheet7.Cells.Item < <<< ($intRowSoft, 1) = $StrComputer
    ERROR: + CategoryInfo : NotSpecified: (:) [], SetValueInvocationException
    ERROR: + FullyQualifiedErrorId : CatchFromBaseAdapterParameterizedPropertySetValueTI
    ERROR:
    ERROR: Exception setting "Item": "Exception from HRESULT: 0x800A03EC"
    CompInv_v2.ps1 (288): ERROR: At Line: 288 char: 21
    ERROR: + $Sheet7.Cells.Item < <<< ($intRowSoft, 2) = $objItem.Name
    ERROR: + CategoryInfo : NotSpecified: (:) [], SetValueInvocationException
    ERROR: + FullyQualifiedErrorId : CatchFromBaseAdapterParameterizedPropertySetValueTI
    ERROR:
    ERROR: Exception setting "Item": "Exception from HRESULT: 0x800A03EC"
    CompInv_v2.ps1 (289): ERROR: At Line: 289 char: 21
    ERROR: + $Sheet7.Cells.Item < <<< ($intRowSoft, 3) = $objItem.Vendor
    ERROR: + CategoryInfo : NotSpecified: (:) [], SetValueInvocationException
    ERROR: + FullyQualifiedErrorId : CatchFromBaseAdapterParameterizedPropertySetValueTI

    by JeffH at 2012-10-11 07:20:30

    If your WMI fails for some reasons then there will be nothing to write to Excel which I believe will throw an exception. But the big take away here is to not suppress errors like we did in the VBScript days. If you know there will be errors that can be ignored (or not), learn how to manage them with Try/Catch.

    by JeffH at 2012-10-11 07:24:24

    You also have an issue on line 217

    Write-Host " -ForegroundColor Green

    You are missing the second "". Are you using a script editor or the ISE to write this? These tools should detect that kind of error.

You must be logged in to reply to this topic.