31 December,2010 by Jack Vamvas
Question: Using platform Powershell 2 CTP & Excel 2003, how can I add multiple worksheets to an Excel spreadsheet?
Answer: I was troubleshooting a Powershell problem –- on a script that creates a Excel spreadsheet. All worksheets up to 4 were OK- but soon as I past 5 the following error was displayed.
Exception getting "Item": "Invalid index. (Exception from HRESULT: 0x8002000B (DISP_E_BADINDEX))" At C:\projects\testing\security\scripts\general_users.ps1:141 char:40 + $sheet5 = $workbook.worksheets.Item <<<< (5) Property 'name' cannot be found on this object; make sure it exists and is settable.
The line of code throwing the error was :
$sheet5 = $workbook.worksheets.Item(5)
This problem is caused by the Default worksheets amount set in Excel .
Adjusting the default worksheets to the amount required will solve the problem as a quick solution.
I will post a function where programmatically the worksheet amounts can be increased