Link to home
Start Free TrialLog in
Avatar of thomehm
thomehmFlag for United States of America

asked on

Accessing the array of Worksheets in a Workbook

I am developing a Visual Studio Tools for Office using Visual Studio 2008.  This is an Excel VSTO add-in, which means that it operates in the same process in as Excel.  It also adds a couple of buttons to the Excel ribbon.  That works fine.  What I am having problems with is accessing one of several Worksheets in a Workbook.  Here is how I get the Workbook:

Microsoft.Office.Interop.Excel.Application a1 = (Microsoft.Office.Interop.Excel.Application)System.Runtime.InteropServices.Marshal.GetActiveObject("Excel.Application");
Microsoft.Office.Interop.Excel.Workbook w1 = (Microsoft.Office.Interop.Excel.Workbook)a1.ActiveWorkbook;

Now, I can get get the ActiveSheet like this:
Microsoft.Office.Interop.Excel.Worksheet wsa = (Microsoft.Office.Interop.Excel.Worksheet)w1.ActiveSheet;

That works fine.  I can also access the count of the number of Sheets like this:
int i = w1.Sheets.Count;

However, if I try to access one of the worksheets using an array syntax, It gives me any exception:
Microsoft.Office.Interop.Excel.Worksheet wsa = (Microsoft.Office.Interop.Excel.Worksheet)w1.Sheets[0];

With this, I get the following exception:
0x8002000b, Invalid Index, DISP_E_BADINDEX

The thing is, I have previously done the same thing in another application using Microsoft Office Automation (the application is in a different process), and it worked.  It would seem that for some reason, the COM marshalling is not properly supporting using the array mechanism.  Can anyone tell me how to randomly access my WorkSheets from the Workbook?
ASKER CERTIFIED SOLUTION
Avatar of wdosanjos
wdosanjos
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of thomehm

ASKER

So obvious, now that I think of it.  But I just starred past that possibility!  Thanks!