Options
Append Single Cell in Excel Using Powershell
Hi,
For part of my script I have a foreach loop as so:-
This data is to be displayed in excel spreadsheet. I am hitting a problem in that PC's with more than one physical hard disk are overwriting this cell, where I would like to append. Anyone come across this before or have any suggestions?
Full script here for anyone interested:-
For part of my script I have a foreach loop as so:-
foreach($drive in $drives) { $size1 = $drive.size / 1GB $size = "{0:N2}" -f $size1 $free1 = $drive.freespace / 1GB $free = "{0:N2}" -f $free1 $freea = $free1 / $size1 * 100 $freeb = "{0:N2}" -f $freea $ID = $drive.DeviceID $c.Cells.Item($intRow, 11) = "$ID = Total Space: $size GB / Free Space: $free GB / Free (Percent): $freeb %" $d.EntireColumn.AutoFit() }
This data is to be displayed in excel spreadsheet. I am hitting a problem in that PC's with more than one physical hard disk are overwriting this cell, where I would like to append. Anyone come across this before or have any suggestions?
Full script here for anyone interested:-
#### Spreadsheet Location $strPath = "C:\Scripts\SERVERINFO\serverinfo.xls" $onerror = "SilentlyContinue" $a = New-Object -comobject Excel.Application $a.Visible = $True $b = $a.Workbooks.Add() $c = $b.Worksheets.Item(1) $c.Cells.Item(1,1) = "Machine Name" $c.Cells.Item(1,2) = "OS Running" $c.Cells.Item(1,3) = "Domain Role" $c.Cells.Item(1,4) = "Total Physical Memory" $c.Cells.Item(1,5) = "Total Free Physical Memory" $c.Cells.Item(1,6) = "Total Virtual Memory" $c.Cells.Item(1,7) = "Total Free Virtual Memory" $c.Cells.Item(1,[IMG]https://us.v-cdn.net/6030959/uploads/images/smilies/icon_cool.gif[/IMG] = "Last Boot Time" $c.Cells.Item(1,9) = "Bios Version" $c.Cells.Item(1,10) = "CPU Info" $c.Cells.Item(1,11) = "Disk Info" $d = $c.UsedRange $d.Interior.ColorIndex = 19 $d.Font.ColorIndex = 11 $d.Font.Bold = $True $d.EntireColumn.AutoFit() $intRow = 2 $cred = Get-Credential $computers = Get-Content "C:\scripts\computers.txt" foreach ($computer in $computers) { #### Set Variables $user = $env:USERNAME #### Win32 class short name assignment - add -credential $cred where needed $OS = Get-WmiObject -Class Win32_OperatingSystem -ComputerName $computer $Bios = Get-WmiObject -Class Win32_BIOS -ComputerName $computer $CS = Get-WmiObject -Class Win32_ComputerSystem -ComputerName $computer $CPU = Get-WmiObject -Class Win32_Processor -ComputerName $computer $drives = Get-WmiObject -ComputerName $computer Win32_LogicalDisk | Where-Object {$_.DriveType -eq 3} #### Set Variable information with short name $OSRunning = $OS.caption + " " + $OS.OSArchitecture + " SP " + $OS.ServicePackMajorVersion $DomainRole = $CS.DomainRole $TotalAvailMemory = $OS.totalvisiblememorysize/1kb $TotalVirtualMemory = $OS.totalvirtualmemorysize/1kb $TotalFreeMemory = $OS.FreePhysicalMemory/1kb $TotalFreeVirtualMemory = $OS.FreeVirtualMemory/1kb $TotalMem = "{0:N2}" -f $TotalAvailMemory $TotalVirt = "{0:N2}" -f $TotalVirtualMemory $FreeMem = "{0:N2}" -f $TotalFreeMemory $FreeVirtMem = "{0:N2}" -f $TotalFreeVirtualMemory $date = Get-Date $uptime = $OS.ConvertToDateTime($OS.lastbootuptime) $BiosVersion = $Bios.Manufacturer + " " + $Bios.SMBIOSBIOSVERSION + " " + $Bios.ConvertToDateTime($Bios.Releasedate) $CPUInfo = $CPU.Name + " & has " + $CPU.NumberOfCores + " Cores & the FSB is " + $CPU.ExtClock + " Mhz" $CPULOAD = $CPU.LoadPercentage if (($DomainRole -eq "0") -or ($DomainRole -eq "1")) { $Role = "Work Station" } elseif (($DomainRole -eq "2") -or ($DomainRole -eq "3")) { $Role = "Member Server" } elseif (($DomainRole -eq "4") -or ($DomainRole -eq "5")) { $Role = "Domain Controller" } else { $Role = "Unknown" } #### Pump Data to Excel $c.Cells.Item($intRow, 1) = $computer $c.Cells.Item($intRow, 2) = $OSRunning $c.Cells.Item($intRow, 3) = $Role $c.Cells.Item($intRow, 4) = "$TotalMem MB" $c.Cells.Item($intRow, 5) = "$FreeMem MB" $c.Cells.Item($intRow, 6) = "$TotalVirt MB" $c.Cells.Item($intRow, 7) = "$FreeVirtMem MB" $c.Cells.Item($intRow, [IMG]https://us.v-cdn.net/6030959/uploads/images/smilies/icon_cool.gif[/IMG] = $uptime $c.Cells.Item($intRow, 9) = $BiosVersion $c.Cells.Item($intRow, 10) = $CPUInfo foreach($drive in $drives) { $size1 = $drive.size / 1GB $size = "{0:N2}" -f $size1 $free1 = $drive.freespace / 1GB $free = "{0:N2}" -f $free1 $freea = $free1 / $size1 * 100 $freeb = "{0:N2}" -f $freea $ID = $drive.DeviceID $c.Cells.Item($intRow, 11) = "$ID = Total Space: $size GB / Free Space: $free GB / Free (Percent): $freeb %" $d.EntireColumn.AutoFit() } $d.EntireColumn.AutoFit() $intRow = $intRow + 1 } IF(Test-Path $strPath) { Remove-Item $strPath $a.ActiveWorkbook.SaveAs($strPath) } ELSE { $a.ActiveWorkbook.SaveAs($strPath) }
DevOps Engineer and Security Champion. https://blog.pash.by - I am trying to find my writing style, so please bear with me.
Comments
-
Optionskalebksp Member Posts: 1,033 ■■■■■□□□□□You just want to append the second drive info to the same string as the first?
$c.Cells.Item($intRow, 11) = $c.Cells.Item($intRow,11) + "$ID = Total Space: $size GB / Free Space: $free GB / Free (Percent): $freeb %"
-
Optionskalebksp Member Posts: 1,033 ■■■■■□□□□□You could also do something like this so the script isn't reading and writing to that cell over and over if you have a ton of drives.
$driveStr = "" foreach($drive in $drives) { $size1 = $drive.size / 1GB $size = "{0:N2}" -f $size1 $free1 = $drive.freespace / 1GB $free = "{0:N2}" -f $free1 $freea = $free1 / $size1 * 100 $freeb = "{0:N2}" -f $freea $ID = $drive.DeviceID $driveStr += "$ID = Total Space: $size GB / Free Space: $free GB / Free (Percent): $freeb %" } $c.Cells.Item($intRow, 11) = $driveStr $d.EntireColumn.AutoFit()
-
OptionsPash Member Posts: 1,600 ■■■■■□□□□□Hi mate,
Thank you very much, great idea!
PashDevOps Engineer and Security Champion. https://blog.pash.by - I am trying to find my writing style, so please bear with me.