Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Accessing the array of Worksheets in a Workbook

Posted on 2011-03-17
2
Medium Priority
?
951 Views
Last Modified: 2013-12-17
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
Comment
Question by:thomehm
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 23

Accepted Solution

by:
wdosanjos earned 2000 total points
ID: 35162190
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
 

Author Closing Comment

by:thomehm
ID: 35166924
So obvious, now that I think of it.  But I just starred past that possibility!  Thanks!
0

Featured Post

Learn by Doing. Anytime. Anywhere.

Do you like to learn by doing?
Our labs and exercises give you the chance to do just that: Learn by performing actions on real environments.

Hands-on, scenario-based labs give you experience on real environments provided by us so you don't have to worry about breaking anything.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Many of us here at EE write code. Many of us write exceptional code; just as many of us write exception-prone code. As we all should know, exceptions are a mechanism for handling errors which are typically out of our control. From database errors, t…
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question