Powershell – adding multiple worksheets to Excel spreadsheet

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

Read More

SQL Server – Export Excel data to SQL Server with Powershell

Powershell - run script on all sql servers

SQL Server – Powershell Excel to HTML