Excel: Insert data to next available row

PowerShell Help
1

Hi.

I’m working on a script which pulls computers from AD and from their windows version/build, is put into a worksheet for that build.

The script creates a hashtable with build numbers/ versions and then a function to pull the actual windows build from the windows version provided by AD.

Everything is working fine except the insert, because it functions with a counter which increments for every iteration/computer, and the problem is that I put the computers into different wotksheets which again gives a lot of blank rows in every sheet (because the row number is taken from the counter).

Is there a way to avoid using a counter to specify the row number in excel and instead just insert into the next availablke row in the sheet?

Here’s my script:

Hashtable for version/ build numbers

$Buldnumbers = @{}
$Buldnumbers.add(“10.0 (10240)”, “1507”)
$Buldnumbers.add(“10.0 (10586)”, “1511”)
$Buldnumbers.add(“10.0 (14393)”, “1607”)
$Buldnumbers.add(“10.0 (15063)”, “1703”)
$Buldnumbers.add(“10.0 (16299)”, “1709”)
$Buldnumbers.add(“10.0 (17134)”, “1803”)
$Buldnumbers.add(“10.0 (17763)”, “1809”)
$Buldnumbers.add(“10.0 (18362)”, “1903”)
$Buldnumbers.add(“10.0 (18363)”, “1909”)
$Buldnumbers.add(“10.0 (19041)”, “20H1”)
$Buldnumbers.add(“10.0 (19042)”, “20H2”)
$Buldnumbers.add(“10.0 (19043)”, “21H1”)

-------------------------------------------------------------------

Function to extract the correct windows build number

-------------------------------------------------------------------

function Get-Windows-Buildnumber
{
param(
[Parameter(Mandatory=$true)]
[String]$OSBuild
)

return $Buldnumbers[$OSBuild]

}

Create new excel file

$excel = New-Object -ComObject excel.application
#$excel.Visible = $true

Create new workbook in excel file

$workbook = $excel.Workbooks.Add()

foreach ($h in $Buldnumbers.GetEnumerator())
{
# Name the worksheet
$worksheet1 = $WorkBook.Worksheets.Add()
$worksheet1.Name = $h.Value.ToString()

# create the column headers
$worksheet1.Cells.Item(1,1) = 'Computername'
$worksheet1.Cells.Item(1,2) = 'Windows build'
$worksheet1.Cells.Item(1,3) = 'DistinguishedName'

#adjusting the column width so all data's properly visible
$usedRange = $worksheet1.UsedRange
$usedRange.EntireColumn.AutoFit() | Out-Null

}

$i = 2 # for the start row and row count

$Computers = Get-ADComputer -Filter * -SearchBase “Omitted here” -Properties Name,OperatingsystemVersion,DistinguishedName | Select-Object Name,OperatingsystemVersion,DistinguishedName
foreach($c in $Computers)
{
try {
$Buildnumber = Get-Windows-Buildnumber($c.OperatingsystemVersion)

    # Open the relevant sheet from the computers OS build number
    $worksheet = $WorkBook.Sheets.Item($Buildnumber)
    $worksheet.cells.item($i,1) = $c.Name
    $worksheet.cells.item($i,2) = $Buildnumber
    $worksheet.cells.item($i,3) = $c.DistinguishedName

    $i++
}
catch {}

}

Save the workbook and close Excel

$workbook.SaveAs(“C:\OUTPUT\Computer OS builds.xlsx”)
$excel.Quit()

Thanks!

2

I think I found a solution to this.
Instead of auto increment and use the same counter I count the rows in that specific worksheet just before I make the insert and increment that number with one like this.

$Buildnumber = Get-Windows-Buildnumber($c.OperatingsystemVersion)

    # Open the relevant sheet from the computers OS build number
    $worksheet = $WorkBook.Sheets.Item($Buildnumber)

    # Counter for the next available row in the sheet
    $RowCount = $worksheet.UsedRange.RowS.Count +1

    $worksheet.cells.item($RowCount,1) = $c.Name
    $worksheet.cells.item($RowCount,2) = $Buildnumber
    $worksheet.cells.item($RowCount,3) = $c.DistinguishedName

Thanks!

1 Like