getting worksheet by name in vsto workbook application

3
votes

This code add a worksheet to my workbook in a VSTO excel workbook application via a ribbon:

Globals.ThisWorkbook.Worksheets.Add();
Excel.Window window = e.Control.Context;
Excel.Worksheet activeWorksheet =((Excel.Worksheet)window.Application.ActiveSheet);

The added worksheet is also the active one (obviously). How can I get existing worksheets in workbook application - ideally by name?

This gives me (before I add the worksheet above) 2 correctly as there are 2 worksheets:

var nows = Globals.ThisWorkbook.Worksheets.Count;

One would think that I can access the worksheet at least by index like so:

var ws = Globals.ThisWorkbook.Worksheets[0];

but this throws this exception:

$exception {"Invalid index. (Exception from HRESULT: 0x8002000B (DISP_E_BADINDEX))"} System.Runtime.InteropServices.COMException

Any ideas? Ideally, I would like to access the worksheets via name. Thanks!

2
c#vstooffice-interop

2 Answers

2
votes

UPDATE: Sorry I missed your last line where you say:

Ideally, I would like to access the worksheets via name. Thanks!

Actually Excel Ole Automation allows you to access a worksheet by name by the same indexer:

Instead of:

var ws = Globals.ThisWorkbook.Worksheets[1];

...just use:

var ws = Globals.ThisWorkbook.Worksheets["Foo"];

The c# looking indexer is actually short-hand in this case for:

var ws = Globals.ThisWorkbook.Worksheets.Item("Foo");

...where the argument passed in Item() is an object (well Variant as far as COM is concerned). Convention for COM collections is to expose an Item property that takes a parameter (yes you read that right) but VB and .NET simplify it.

Tell me more

Original Answer:

Excel Interop via .NET is by way of Ole Automation which historically goes way back to the days where most things was accesssed via Visual Basic (no not VB.NET) applications which used 1-based arrarys and not 0.

The correct syntax to access the first element is therefor:

var ws = Globals.ThisWorkbook.Worksheets[1];
2
votes

You could use LINQ to query the collection for the name of the Worksheet you're interested in.

The following returns the single matching Worksheet or null.

var worksheet =
    Globals.ThisWorkbook.Worksheets.Cast<Worksheet>()
                                   .SingleOrDefault(w => w.Name == "worksheet_name");

If there could be several with the same name, use Where() and ToList() instead.

var worksheet =
    Globals.ThisWorkbook.Worksheets.Cast<Worksheet>()
                                   .Where(w => w.Name == "worksheet_name")
                                   .ToList();