Powershell – adding multiple worksheets to Excel spreadsheet

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

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


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on Powershell – adding multiple worksheets to Excel spreadsheet


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer