# Excel: Insert data to next available row

**URL:** <https://forums.powershell.org/t/excel-insert-data-to-next-available-row/17846>\
**Category:** PowerShell Help\
**Created:** [October 29, 2021, 7:31pm UTC](https://forums.powershell.org/t/excel-insert-data-to-next-available-row/17846 "2021-10-29T19:31:25Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![Thomasso](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.powershell.org/thomasso/32/1799_2.png) [@Thomasso](https://forums.powershell.org/u/Thomasso)\
**Post date:** [October 29, 2021, 7:31pm UTC](https://forums.powershell.org/t/excel-insert-data-to-next-available-row/17846/1 "2021-10-29T19:31:25Z")

</div>

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!

---

<div class="post-metadata">

**Author:** ![Thomasso](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.powershell.org/thomasso/32/1799_2.png) [@Thomasso](https://forums.powershell.org/u/Thomasso)\
**Post date:** [October 29, 2021, 8:00pm UTC](https://forums.powershell.org/t/excel-insert-data-to-next-available-row/17846/2 "2021-10-29T20:00:14Z")

</div>

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!

---

<div class="post-metadata">

**Author:** ![dotnVo](https://avatars.discourse-cdn.com/v4/letter/d/4af34b/32.png) [@dotnVo](https://forums.powershell.org/u/dotnVo)\
**Post date:** [May 16, 2024, 8:20pm UTC](https://forums.powershell.org/t/excel-insert-data-to-next-available-row/17846/3 "2024-05-16T20:20:35Z")

</div>