Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 959
  • Last Modified:

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?
0
thomehm
Asked:
thomehm
1 Solution
 
wdosanjosCommented:
I think Workbook.Sheets is 1-based not 0-based, so to get the first Worksheet it should be:

Microsoft.Office.Interop.Excel.Worksheet wsa = (Microsoft.Office.Interop.Excel.Worksheet)w1.Sheets[1];
0
 
thomehmAuthor Commented:
So obvious, now that I think of it.  But I just starred past that possibility!  Thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now