thomehm
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.E xcel.Appli cation a1 = (Microsoft.Office.Interop. Excel.Appl ication)Sy stem.Runti me.Interop Services.M arshal.Get ActiveObje ct("Excel. Applicatio n");
Microsoft.Office.Interop.E xcel.Workb ook w1 = (Microsoft.Office.Interop. Excel.Work book)a1.Ac tiveWorkbo ok;
Now, I can get get the ActiveSheet like this:
Microsoft.Office.Interop.E xcel.Works heet wsa = (Microsoft.Office.Interop. Excel.Work sheet)w1.A ctiveSheet ;
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.E xcel.Works heet wsa = (Microsoft.Office.Interop. Excel.Work sheet)w1.S heets[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?
Microsoft.Office.Interop.E
Microsoft.Office.Interop.E
Now, I can get get the ActiveSheet like this:
Microsoft.Office.Interop.E
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.E
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER