How to iterate through Excel worksheets in C#?

Question

I have a workbook with multiple sheets and I was trying to iterate through them but it's causing me problems. The code below throws the error:

Microsoft.Office.Tools.Excel.Worksheet'. This operation failed because the QueryInterface call on the COM component for the interface with IID '{297DC8D9-EABD-45A1-BDEF-68AB67E5C3C3}' failed due to the following error: No such interface supported (Exception from HRESULT: 0x80004002 (E_NOINTERFACE)).

but I don't really understand what that even means.

The error occurs when trying to assign the sheet variable the first sheet in my sheets variable which is just a workbook

public List<Worksheet> GetAllDatasheets()
    {
        var datasheets = new List<Worksheet>();
        var sheets = _book;
        foreach (Worksheet sheet in sheets.Worksheets)
        {
            if (sheet.Name.StartsWith("$"))
                datasheets.Add(sheet);
        }
        return datasheets;
    }

EDIT: Here's my full code:

using Workbook = Microsoft.Office.Tools.Excel.Workbook;
using Worksheet = Microsoft.Office.Tools.Excel.Worksheet;

private Workbook _book;

    public ExcelObjectDAL()
    {
        _book = Globals.Factory.GetVstoObject(Globals.ThisAddIn.Application.ActiveWorkbook);
    }

public List<Worksheet> GetAllDatasheets()
    {
        var datasheets = new List<Worksheet>();
        var sheets = _book;
        foreach (var sheet in sheets.Worksheets)
        {
            var actualSheet = (Worksheet) sheet;
            if (actualSheet.Name.StartsWith("$"))
                datasheets.Add(actualSheet);
        }
        return datasheets;
    }

Does this answer your question : https://stackoverflow.com/questions/16938467/why-cannot-i-cast-my-com-object-to-the-interface-it-implements-in-c/16943296#16943296 ? — Philippe B., Jan 08 '20 at 10:23
Not if I understand it correct. VSTO AddIns don't even have a main method but I might not understand the link properly — NotTheSmartestInTheRoom, Jan 08 '20 at 10:33
I feel like this link https://learn.microsoft.com/en-us/visualstudio/vsto/how-to-programmatically-list-all-worksheets-in-a-workbook?view=vs-2019 is doing what I'm trying to do but I'm failing at it — NotTheSmartestInTheRoom, Jan 08 '20 at 10:40
Are you sure that your worksheet in `List` is the same type as `sheets.Worksheets` (sheet) ? — Philippe B., Jan 08 '20 at 10:44
@PhilippeB. They are both of type Microsoft.Office.Tools.Excel — NotTheSmartestInTheRoom, Jan 08 '20 at 10:47
You don't provide enough context in the code to be sure what kind of objects we're dealing with. We need to know *exactly* which data types all these objects are. You can use the [edit] link under the question to provide the information *in the question, itself`, rather than partial information scattered about in comments. A [MCVE] that targets exactly what's going on, plus some description of the project would be useful. I *suspect* you may be trying to "mix-and-match" interop and tools objects, but can't be sure based on the info provided. — Cindy Meister, Jan 08 '20 at 11:05
@CindyMeister I have updated and provided the full code. There's really not much else than my previous but it should be the only important information. — NotTheSmartestInTheRoom, Jan 08 '20 at 11:42
VSTO "wraps" the native "interop" objects to provide extended functionality. As long as you don't want to use the extensions, it's better to work with the native "interop" objects, rather than the "Tools" objects. Change the `using` statements to `Microsoft.Office.Interop.Excel` and the line to get the Workbook to `_book = Globals.ThisAddIn.Application.ActiveWorkbook;` and see if that makes a difference. — Cindy Meister, Jan 08 '20 at 11:51
@CindyMeister That did work but I gotta admit I don't really understand why. Thanks — NotTheSmartestInTheRoom, Jan 08 '20 at 11:59
As the "Microsoft.Office.Tools.Excel.Workbook" is an extended wrapper around the Interop equivalent - then I am guessing the Workheets (& other properties) are Interop objects. — PaulF, Jan 08 '20 at 12:03
My area of expertise is more Word, which means I don't know the Excel "Tools" objects by heart, but I think it may not provide `Worksheets`. You might have gotten compile errors if you'd strongly typed all the objects, rather than relying on `var` to do the thinking about what data type is being assigned. With `var`, such problems only show up at run-time. — Cindy Meister, Jan 08 '20 at 12:08

score -1 · Answer 1 · answered Jan 08 '20 at 10:27

-1

I think this is what you are looking for:

 public List<string> GetAllDatasheets()
        {
            Excel.Application excelApp = new Excel.Application();
            Excel.Workbook workBook = excelApp.Workbooks.Open(filePath);

            var datasheets = new List<string>();
            foreach (Excel.Worksheet sheet in workBook.Worksheets)
            {
                if (sheet.Name.StartsWith("$"))
                    datasheets.Add(sheet.Name);
            }

            excelApp.Quit();
            return datasheets;
        }

If you have the worksheet name you know enough you don't have to make the list with type Worksheet

answered Jan 08 '20 at 10:27

Joeri E

111
9

I'm working on a VSTO addin and gets the workbook through the Globals.Factory.GetVstoObject(Globals.ThisAddIn.Application.ActiveWorkbook); - why would I need to access the workbook through Excel.Application? Also I need the full worksheets as my return since the calling method is a method toggling the sheet.Visible property. – NotTheSmartestInTheRoom Jan 08 '20 at 10:33
Could the following be usefull to you? https://www.codeproject.com/Articles/8096/C-Retrieve-Excel-Workbook-Sheet-Names – Joeri E Jan 08 '20 at 10:38

How to iterate through Excel worksheets in C#?

1 Answers1