Will not fill the excel spread sheet as expected

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 |<br> where{$_.IPEnabled -eq &quot;True&quot;}<br> <br># Populate General Sheet&#40;1&#41; with information<br> foreach &#40;$objItem in $GenItems1&#41;{<br> $Sheet1.Cells.Item&#40;$intRow, 1&#41; = $StrComputer<br> Switch&#40;$objItem.DomainRole&#41;<br> {<br> 0{$Sheet1.Cells.Item&#40;$intRow, 3&#41; = &quot;Stand Alone Workstation&quot;}<br> 1{$Sheet1.Cells.Item&#40;$intRow, 3&#41; = &quot;Member Workstation&quot;}<br> 2{$Sheet1.Cells.Item&#40;$intRow, 3&#41; = &quot;Stand Alone Server&quot;}<br> 3{$Sheet1.Cells.Item&#40;$intRow, 3&#41; = &quot;Member Server&quot;}<br> 4{$Sheet1.Cells.Item&#40;$intRow, 3&#41; = &quot;Back-up Domain Controller&quot;}<br> 5{$Sheet1.Cells.Item&#40;$intRow, 3&#41; = &quot;Primary Domain Controller&quot;}<br> default{&quot;Undetermined&quot;}<br> }<br> $Sheet1.Cells.Item&#40;$intRow, 4&#41; = $objItem.Manufacturer<br> $Sheet1.Cells.Item&#40;$intRow, 5&#41; = $objItem.Model<br> $Sheet1.Cells.Item&#40;$intRow, 6&#41; = $objItem.SystemType<br> $Sheet1.Cells.Item&#40;$intRow, 7&#41; = $objItem.NumberOfProcessors<br> $Sheet1.Cells.Item&#40;$intRow, 8&#41; = $objItem.TotalPhysicalMemory / 1024 / 1024<br> $Sheet1.Cells.Item&#40;$intRow, 2&#41; = $objItem.UserName<br> }<br> foreach &#40;$objItem in $GenItems2&#41;{<br> $Sheet1.Cells.Item&#40;$intRow, 9&#41; = $objItem.Caption<br> $Sheet1.Cells.Item&#40;$intRow, 10&#41; = $objItem.csdversion<br> }<br> <br>#Populate Systems Sheet<br> foreach &#40;$objItem in $SysItems1&#41;{<br> $Sheet2.Cells.Item&#40;$intRow, 1&#41; = $StrComputer<br> $Sheet2.Cells.Item&#40;$intRow, 2&#41; = $objItem.Name<br> $Sheet2.Cells.Item&#40;$intRow, 3&#41; = $objItem.SMBIOSbiosVersion<br> $Sheet2.Cells.Item&#40;$intRow, 4&#41; = $objItem.SerialNumber<br> }<br> foreach &#40;$objItem in $SysItems2&#41;{ <br> $Sheet2.Cells.Item&#40;$intRow, 5&#41; = $objItem.Caption<br> }<br> foreach &#40;$objItem in $SysItems3&#41;{<br> $Sheet2.Cells.Item&#40;$intRow, 6&#41; = $objItem.BuildVersion<br> }<br> <br>#Populate Processor Sheet <br> foreach &#40;$objItem in $ProcItems1&#41;{<br> $Sheet3.Cells.Item&#40;$intRowCPU, 1&#41; = $StrComputer<br> $Sheet3.Cells.Item&#40;$intRowCPU, 2&#41; = $objItem.DeviceID+&quot; &quot;+$objItem.Name<br> $Sheet3.Cells.Item&#40;$intRowCPU, 3&#41; = $objItem.Description<br> $Sheet3.Cells.Item&#40;$intRowCPU, 4&#41; = $objItem.family<br> $Sheet3.Cells.Item&#40;$intRowCPU, 5&#41; = $objItem.currentClockSpeed<br> $Sheet3.Cells.Item&#40;$intRowCPU, 6&#41; = $objItem.l2cacheSize<br> $Sheet3.Cells.Item&#40;$intRowCPU, 7&#41; = $objItem.UpgradeMethod<br> $Sheet3.Cells.Item&#40;$intRowCPU, 8&#41; = $objItem.SocketDesignation<br> $intRowCPU = $intRowCPU + 1<br> }<br> <br>#Populate Memory Sheet<br>$bankcounter = 1<br> foreach &#40;$objItem in $memItems2&#41;{<br> $MemSlots = $objItem.MemoryDevices +1<br> <br> foreach &#40;$objItem in $MemItems1&#41;{<br> $Sheet4.Cells.Item&#40;$intRowMem, 1&#41; = $StrComputer<br> $Sheet4.Cells.Item&#40;$intRowMem, 2&#41; = &quot;Bank &quot; +$bankcounter<br> if&#40;$objItem.BankLabel -eq &quot;&quot;&#41;{<br> $Sheet4.Cells.Item&#40;$intRowMem, 3&#41; = $objItem.DeviceLocator}<br> Else{$Sheet4.Cells.Item&#40;$intRowMem, 3&#41; = $objItem.BankLabel}<br> $Sheet4.Cells.Item&#40;$intRowMem, 4&#41; = $objItem.Capacity/1024/1024<br> $Sheet4.Cells.Item&#40;$intRowMem, 5&#41; = $objItem.FormFactor<br> $Sheet4.Cells.Item&#40;$intRowMem, 6&#41; = $objItem.TypeDetail<br> $intRowMem = $intRowMem + 1<br> $bankcounter = $bankcounter + 1<br> }<br> while&#40;$bankcounter -lt $MemSlots&#41; <br> {<br> $Sheet4.Cells.Item&#40;$intRowMem, 1&#41; = $StrComputer<br> $Sheet4.Cells.Item&#40;$intRowMem, 2&#41; = &quot;Bank &quot; +$bankcounter<br> $Sheet4.Cells.Item&#40;$intRowMem, 3&#41; = &quot;is Empty&quot;<br> $Sheet4.Cells.Item&#40;$intRowMem, 4&#41; = &quot;&quot;<br> $Sheet4.Cells.Item&#40;$intRowMem, 5&#41; = &quot;&quot;<br> $Sheet4.Cells.Item&#40;$intRowMem, 6&#41; = &quot;&quot;<br> $intRowMem = $intRowMem + 1<br> $bankcounter = $bankcounter + 1<br> }<br> }<br> <br> <br>#Populate Disk Sheet<br> foreach &#40;$objItem in $DiskItems&#41;{<br> $Sheet5.Cells.Item&#40;$intRowDisk, 1&#41; = $StrComputer<br> Switch&#40;$objItem.DriveType&#41;<br> {<br> 2{$Sheet5.Cells.Item&#40;$intRowDisk, 2&#41; = &quot;Floppy&quot;}<br> 3{$Sheet5.Cells.Item&#40;$intRowDisk, 2&#41; = &quot;Fixed Disk&quot;}<br> 5{$Sheet5.Cells.Item&#40;$intRowDisk, 2&#41; = &quot;Removable Media&quot;}<br> default{&quot;Undetermined&quot;}<br> }<br> $Sheet5.Cells.Item&#40;$intRowDisk, 3&#41; = $objItem.DeviceID<br> $Sheet5.Cells.Item&#40;$intRowDisk, 4&#41; = $objItem.Size/1024/1024<br> $Sheet5.Cells.Item&#40;$intRowDisk, 5&#41; = $objItem.FreeSpace/1024/1024<br> $intRowDisk = $intRowDisk + 1<br> }<br> <br>#Populate Network Sheet<br> foreach &#40;$objItem in $NetItems&#41;{<br> $Sheet6.Cells.Item&#40;$intRowNet, 1&#41; = $StrComputer<br> $Sheet6.Cells.Item&#40;$intRowNet, 2&#41; = $objItem.Caption+&quot; &#40;enabled&#41;&quot;<br> $Sheet6.Cells.Item&#40;$intRowNet, 3&#41; = $objItem.DHCPEnabled<br> $Sheet6.Cells.Item&#40;$intRowNet, 4&#41; = $objItem.IPAddress<br> $Sheet6.Cells.Item&#40;$intRowNet, 5&#41; = $objItem.IPSubnet<br> $Sheet6.Cells.Item&#40;$intRowNet, 6&#41; = $objItem.DefaultIPGateway<br> $Sheet6.Cells.Item&#40;$intRowNet, 7&#41; = $objItem.DNSServerSearchOrder<br> $Sheet6.Cells.Item&#40;$intRowNet, 8&#41; = $objItem.FullDNSRegistrationEnabled<br> $Sheet6.Cells.Item&#40;$intRowNet, 9&#41; = $objItem.WINSPrimaryServer<br> $Sheet6.Cells.Item&#40;$intRowNet, 10&#41; = $objItem.WINSSecondaryServer<br> $Sheet6.Cells.Item&#40;$intRowNet, 11&#41; = $objItem.WINSEnableLMHostsLookup<br> $intRowNet = $intRowNet + 1<br> }<br> #Populate Software Sheet<br> foreach &#40;$objItem in $Software&#41;{<br> $Sheet7.Cells.Item&#40;$intRowSoft, 1&#41; = $StrComputer<br> $Sheet7.Cells.Item&#40;$intRowSoft, 2&#41; = $objItem.Name<br> $Sheet7.Cells.Item&#40;$intRowSoft, 3&#41; = $objItem.Vendor<br> $Sheet7.Cells.Item&#40;$intRowSoft, 4&#41; = $objItem.Version<br> $Sheet7.Cells.Item&#40;$intRowSoft, 5&#41; = $objItem.Caption<br> }<br> <br>$intRow = $intRow + 1<br>$intRowCPU = $intRowCPU + 1<br>$intRowMem = $intRowMem + 1<br>$intRowDisk = $intRowDisk + 1<br>$intRowNet = $intRowNet + 1<br>$intRowSoft = $intRowSoft + 1<br>}<br>}<br><br># =============================================================================================<br># Function Name &#39;ListComputers&#39; - Enumerates ALL computer objects in AD<br># ==============================================================================================<br>Function ListComputers {<br>$strCategory = &quot;computer&quot;<br><br>$objDomain = New-Object System.DirectoryServices.DirectoryEntry<br><br>$objSearcher = New-Object System.DirectoryServices.DirectorySearcher<br>$objSearcher.SearchRoot = $objDomain<br>$objSearcher.Filter = &#40;&quot;&#40;objectCategory=$strCategory&#41;&quot;&#41;<br><br>$colProplist = &quot;name&quot;<br>foreach &#40;$i in $colPropList&#41;{$objSearcher.PropertiesToLoad.Add&#40;$i&#41;}<br><br>$colResults = $objSearcher.FindAll&#40;&#41;<br><br>foreach &#40;$objResult in $colResults&#41;<br> {$objComputer = $objResult.Properties; $objComputer.name}<br>}<br><br># ==============================================================================================<br># Function Name &#39;ListServers&#39; - Enumerates ALL Servers objects in AD<br># ==============================================================================================<br>Function ListServers {<br>$strCategory = &quot;computer&quot;<br>$strOS = &quot;Windows*Server*&quot;<br><br>$objDomain = New-Object System.DirectoryServices.DirectoryEntry<br><br>$objSearcher = New-Object System.DirectoryServices.DirectorySearcher<br>$objSearcher.SearchRoot = $objDomain<br>$objSearcher.Filter = &#40;&quot;&#40;&amp;&#40;objectCategory=$strCategory&#41;&#40;OperatingSystem=$strOS&#41;&#41;&quot;&#41;<br><br>$colProplist = &quot;name&quot;<br>foreach &#40;$i in $colPropList&#41;{$objSearcher.PropertiesToLoad.Add&#40;$i&#41;}<br><br>$colResults = $objSearcher.FindAll&#40;&#41;<br><br>foreach &#40;$objResult in $colResults&#41;<br> {$objComputer = $objResult.Properties; $objComputer.name}<br>}<br><br># ========================================================================<br># Function Name &#39;ListTextFile&#39; - Enumerates Computer Names in a text file<br># Create a text file and enter the names of each computer. One computer<br># name per line. Supply the path to the text file when prompted.<br># ========================================================================<br>Function ListTextFile {<br> $strText = Read-Host &quot;Enter the path for the text file&quot;<br> $colComputers = Get-Content $strText<br>}<br><br># ========================================================================<br># Function Name &#39;SingleEntry&#39; - Enumerates Computer from user input<br># ========================================================================<br>Function ManualEntry {<br> $colComputers = Read-Host &quot;Enter Computer Name or IP&quot; <br>}<br><br># ==============================================================================================<br># Script Body<br># ==============================================================================================<br>$erroractionpreference = &quot;SilentlyContinue&quot;<br><br><br>#Gather info from user.<br>Write-Host &quot;********************************&quot; -ForegroundColor Green<br>Write-Host &quot;Computer Inventory Script&quot; -ForegroundColor Green<br>Write-Host &quot; -ForegroundColor Green<br>Write-Host &quot;Created: 09/21/2012&quot; -ForegroundColor Green<br>Write-Host &quot;********************************&quot; -ForegroundColor Green<br>Write-Host &quot; &quot;<br>Write-Host &quot;Admin rights are required to enumerate information.&quot; -ForegroundColor Green<br>Write-Host &quot;Would you like to use an alternative credential?&quot; -ForegroundColor Green<br>$credResponse = Read-Host &quot;[Y] Yes, [N] No&quot;<br> If&#40;$CredResponse -eq &quot;y&quot;&#41;{$cred = Get-Credential domain\username}<br>Write-Host &quot; &quot;<br>Write-Host &quot;Which computer resources would you like in the report?&quot; -ForegroundColor Green<br>$strResponse = Read-Host &quot;[3] Computer names from a File, [4] One Computer?&quot;<br>If&#40;$strResponse -eq &quot;1&quot;&#41;{$colComputers = ListComputers | Sort-Object}<br> elseif&#40;$strResponse -eq &quot;2&quot;&#41;{$colComputers = ListServers | Sort-Object}<br> elseif&#40;$strResponse -eq &quot;3&quot;&#41;{. ListTextFile}<br> elseif&#40;$strResponse -eq &quot;4&quot;&#41;{. ManualEntry}<br> else{Write-Host &quot;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: (:slight_smile: , 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: (:slight_smile: , 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: (:slight_smile: , 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.