Dynamically create worksheets in Excel

PowerShell Help
1

Good day everyone,

I’m trying to create a function where people can get a quick and easy listing of users in Security Groups and have the membership be placed in individual worksheets in an Excel workbook. The problem I’m having is that the way I’m trying to accomplish it is giving me Invalid Index errors and I’m a bit stumped. I hope that someone might be able to see where my mistake is or point me in a better direction.


Begin
{
$Groups = Get-ADGroup -Filter {name -like 'sg_data_*'} | Select-Object -ExpandProperty name
$SheetVar = 1
$excel = New-Object -ComObject excel.application
$excel.visible = $true
$excel = $excel.workbooks.add()
$excel.worksheets.item(3).delete()
$excel.worksheets.item(2).delete()
}

Process
{
ForEach($Group in $Groups)
    {
    $sheet = $excel.worksheets.item($sheetvar)
    $Sheet.name = "$Group"
    $SheetVar++
    $excel.worksheets.item($SheetVar).Add()   ### Pretty sure my problem is right here 
    }

}

I’ve taken out the internal ForEach loop since when I run just this I get the exact same errors and I’m pretty sure the last line in my Process section is where my logic error is.

Thanks to everyone who takes a look.

2

Hi Wennis,

I received two errors from the Begin section.

I think you’re better to do something like this in the begin section to ensure you are using the right index.

$Groups = Get-ADGroup -Filter {name -like 'sg_data_*'} | Select-Object -ExpandProperty name

$SheetVar = 1
$excel = New-Object -ComObject excel.application
$excel.visible = $true
$excel = $excel.workbooks.add()
$count = $excel.worksheets.count
While ($count -ne 1) {
$excel.Worksheets.Item($count).Delete()
$count = $excel.worksheets.count
}

For adding extra worksheets in your spreadsheet, use

$excel.worksheets.add().

There’s no such method as $excel.worksheets.item(x).Add().

When you add a worksheet, it becomes the active sheet, so you do not need to worry activating it. It also adopts the first index as well, so you can refer to it as

$excel.sheets(1)

So you can do this :

$excel.Sheets(1).Name = 'MySheetName'

and so on.

let me know if this helps.

cheers,

Tim

3

Thanks so much Tim. Excel automatically making the newly added sheet active and using the correct method solved most of my internal logic issues and the while loop in the start made everything just that much cleaner.