unable to loop through open Excel workbooks in C#

Question

I am currently consistently failing at a very simple task:

• I want to loop through all currently open Excel-files (Workbooks)

Of course I did the usual googling for code snipes and found quite a bunch of ready-to-use code that should work according to the comments in the discussion threats and ratings. But for me they all just don’t work

I also saw people mentioning releasing COM Objects, but I didn’t really understand it and all the ready-to-go code examples don’t include it neither.

I am using:

• Windows 10
• Office 16
• Visual Studio 2017 (V15.9.41)
• COM-Reference 'Microsoft Excel 16.0 Object Library'

I modified the code snippets I found online, so my current code looks like this:

using System;
using Microsoft.Office.Interop.Excel;

private void PrintListOfOpenWB()
{
    Microsoft.Office.Interop.Excel.Application xlApp;
            
    xlApp = new Microsoft.Office.Interop.Excel.Application();

    foreach (Workbook item in xlApp.Workbooks)
    {
        Console.WriteLine(item.Name);
    }
}

I am using it as part of a WindowsFormsApp, so there is also using System.Windows.Forms etc, but I removed this part, so only the relevant code is shown here.

I do have multiple workbooks open and I do not get any error while compiling nor while running the code. But there is absolutely no output generated, because it directly jumps to the end of the foreach loop.

Any ideas what I am doing wrong?

Answer 1

I looked further into it and found the following solution that works for me.

using System;
using System.Runtime.InteropServices;
using Excel = Microsoft.Office.Interop.Excel;

private void PrintListOfOpenWorkbooks()
{
    Excel.Application xlApp;
 
    xlApp = (Excel.Application)Marshal.GetActiveObject("Excel.Application");

    foreach(Excel.Workbook xlWorkBook in xlApp.Workbooks)
    {
        Console.WriteLine(xlWorkBook.Name);
    }
}

Maybe this will help other people in the future as well.

Answer 2

This probably returns no output because the variable is empty, doing what you did you only created the variable that let you access the library method, and you can't populate that with an actual excel file, to do that do this:

Workbook workBook = xlApp.Workbooks.Open(yourExcelFilePath);

//Cycle through the workBook and display the sheets

workBook.Close(false, yourExcelFilePath, null);

Remember that the hierarchy of working with excel files (and this is practically the same with all the different libraries that helps you in doing this) is structured like this:

• test = new Microsoft.Office.Interop.Excel.Application(); : This is used just to interface with the library methods
• WorkBook : This is the actual equivalent of the full excel file you want to work on, and you need to populate it first
• Worksheet: This is an entity that represent a single excel "page" inside the file

P.S: Re-reading the question I maybe have understood it wrong, can you specify if you want to show the name of excel files inside a folder or show the names of the single sheets inside a sigle excel file

To show the names of different excel files somewhere you can do this:

string[] temp = Directory.GetFiles(directoryPath);
List<string> ExcelFilesInDirectory = new();

foreach (var item in temp)
{
    if (item.Contains(".xlsx"))
    {
        ExcelFilesInDirectory.Add(item);
    }
}

Then with that list you can make the user select the file he wants to modify and then you can use the first code sample I sent to make it work