none
Powershell creating excel worksheets

    Question

  • Is there a limit to creating worksheet?

    Here is my code:

    $excel = New-Object -ComObject Excel.Application
        $workbook = $excel.Workbooks.add()
        $worksheetA = $workbook.Worksheets.Add() 
        $sheet1 = $workbook.worksheets.Item(1)
        $sheet1.name = "SQL LOGINS"
        $sheet2 = $workbook.worksheets.Item(2)
        $sheet2.name = "VERSION"
        $sheet3 = $workbook.worksheets.Item(3)
        $sheet3.name = "sp_helprotect"
        $sheet4 = $workbook.worksheets.Item(4)
        $sheet4.name = "HOLD1"
        $sheet5 = $workbook.worksheets.Item(5)
        $sheet5.name = "sysobjects"
        $sheet6 = $workbook.worksheets.Item(6)
        $sheet6.name = "sp_helpdb" 

    It errors at $sheet5 but creates 1-4.

    Exception getting "Item": "Invalid index. (Exception from HRESULT: 0x8002000B (DISP_E_BADINDEX))"
    At C:\SCRIPTS\Powershell\SQL\AUDIT\AUDIT.ps1:28 char:5
    +     $sheet5 = $workbook.worksheets.Item(5)
    +     ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
        + CategoryInfo          : NotSpecified: (:) [], ParentContainsErrorRecordException
        + FullyQualifiedErrorId : CatchFromBaseAdapterParameterizedPropertyGetValueTI

    Friday, October 18, 2013 7:07 PM

Answers

  • I am by no means an Excel coding expert - but try this out:

    $excel = New-Object -ComObject Excel.Application
    $excel.Visible = $true
    $workbook = $excel.Workbooks.add()
    $workbook.worksheets.add()
    $workbook.worksheets.add()
    $workbook.worksheets.add()
    $sheet1 = $workbook.worksheets.Item(1)
    $sheet1.name = "SQL LOGINS"
    $sheet2 = $workbook.worksheets.Item(2)
    $sheet2.name = "VERSION"
    $sheet3 = $workbook.worksheets.Item(3)
    $sheet3.name = "sp_helprotect"
    $sheet4 = $workbook.worksheets.Item(4)
    $sheet4.name = "HOLD1"
    $sheet5 = $workbook.worksheets.Item(5)
    $sheet5.name = "sysobjects"
    $sheet6 = $workbook.worksheets.Item(6)
    $sheet6.name = "sp_helpdb" 


    G. Samuel Hays, MCT, MCSE 2012, MCITP: Enterprise Admin

    Blog:gsamuelhays.blogspot.com

    twitter:twitter.com/gsamuelhays

    • Marked as answer by Badpig522 Tuesday, October 22, 2013 7:01 PM
    Friday, October 18, 2013 7:44 PM
  • I was able to create 20 worksheets with no issues:

    $MissingType = [System.Type]::Missing
    $WorksheetCount = 20
    $excel = New-Object -ComObject excel.application
    $excel.Visible = $True
    # Add a workbook
    $Workbook = $Excel.Workbooks.Add()
    $Workbook.Title = 'Something'
    #Add worksheets
    $null = $Excel.Worksheets.Add($MissingType, $Excel.Worksheets.Item($Excel.Worksheets.Count), 
    $WorksheetCount - $Excel.Worksheets.Count, $Excel.Worksheets.Item(1).Type)
    1..20 | ForEach {
        $Excel.Worksheets.Item($_).Name = "Name - $($_)"
    }


    Boe Prox
    Blog | Twitter
    PoshWSUS | PoshPAIG | PoshChat | PoshEventUI
    PowerShell Deep Dives Book

    • Marked as answer by Badpig522 Tuesday, October 22, 2013 7:01 PM
    Friday, October 18, 2013 8:04 PM

All replies

  • I am by no means an Excel coding expert - but try this out:

    $excel = New-Object -ComObject Excel.Application
    $excel.Visible = $true
    $workbook = $excel.Workbooks.add()
    $workbook.worksheets.add()
    $workbook.worksheets.add()
    $workbook.worksheets.add()
    $sheet1 = $workbook.worksheets.Item(1)
    $sheet1.name = "SQL LOGINS"
    $sheet2 = $workbook.worksheets.Item(2)
    $sheet2.name = "VERSION"
    $sheet3 = $workbook.worksheets.Item(3)
    $sheet3.name = "sp_helprotect"
    $sheet4 = $workbook.worksheets.Item(4)
    $sheet4.name = "HOLD1"
    $sheet5 = $workbook.worksheets.Item(5)
    $sheet5.name = "sysobjects"
    $sheet6 = $workbook.worksheets.Item(6)
    $sheet6.name = "sp_helpdb" 


    G. Samuel Hays, MCT, MCSE 2012, MCITP: Enterprise Admin

    Blog:gsamuelhays.blogspot.com

    twitter:twitter.com/gsamuelhays

    • Marked as answer by Badpig522 Tuesday, October 22, 2013 7:01 PM
    Friday, October 18, 2013 7:44 PM
  • I was able to create 20 worksheets with no issues:

    $MissingType = [System.Type]::Missing
    $WorksheetCount = 20
    $excel = New-Object -ComObject excel.application
    $excel.Visible = $True
    # Add a workbook
    $Workbook = $Excel.Workbooks.Add()
    $Workbook.Title = 'Something'
    #Add worksheets
    $null = $Excel.Worksheets.Add($MissingType, $Excel.Worksheets.Item($Excel.Worksheets.Count), 
    $WorksheetCount - $Excel.Worksheets.Count, $Excel.Worksheets.Item(1).Type)
    1..20 | ForEach {
        $Excel.Worksheets.Item($_).Name = "Name - $($_)"
    }


    Boe Prox
    Blog | Twitter
    PoshWSUS | PoshPAIG | PoshChat | PoshEventUI
    PowerShell Deep Dives Book

    • Marked as answer by Badpig522 Tuesday, October 22, 2013 7:01 PM
    Friday, October 18, 2013 8:04 PM