Append Single Cell in Excel Using Powershell

Pash

Hi,

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)
}

kalebksp

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 %" 

kalebksp

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()

Pash

Hi mate,

Thank you very much, great idea!

Pash