Dynamically create worksheets in Excel

Tagged: ,

This topic contains 2 replies, has 2 voices, and was last updated by Profile photo of wenniss wenniss 11 months ago.

  • Author
    Posts
  • #33890
    Profile photo of wenniss
    wenniss
    Participant

    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.

  • #33907
    Profile photo of Tim Pringle
    Tim Pringle
    Participant

    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

  • #33909
    Profile photo of wenniss
    wenniss
    Participant

    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.

You must be logged in to reply to this topic.