Solved

Accessing the array of Worksheets in a Workbook

Posted on 2011-03-17
2
947 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 500 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

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
linq, c# 8 35
Unlocking a column in excel using C# 17 27
insert an Jpeg/bmp image onto the visio drawing using VBA/C# 5 27
Trigger C# code inside the SQL Server 6 35
More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

733 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