Checking if an Excel Workbook is open

Question

Is there a way to see if an Excel Workbook, say DataSheet.xls, is open (in use) or not? I would like to close that Workbook if it is opened.

Answer 1

The right way is to examine the Application.Workbooks object. In VBA you would write:

Dim wb as Workbook
On Error Resume Next                       '//this is VBA way of saying "try"'
Set wb = Application.Workbooks(wbookName)
If err.Number = 9 then                     '//this is VBA way of saying "catch"'
    'the file is not opened...'
End If

In other words, Workbooks is an array (or in VBA terms, Collection) of all open workbooks.

In C# the following code works:

    static bool IsOpened(string wbook)
    {
        bool isOpened = true;
        Excel.Application exApp;
        exApp = (Excel.Application)System.Runtime.InteropServices.Marshal.GetActiveObject("Excel.Application");
        try
        {
            exApp.Workbooks.get_Item(wbook);
        }
        catch (Exception)
        {
            isOpened = false;
        }
        return isOpened;
    }

You will probably want to pass the reference to Excel.Application yourself.

Answer 2

Try this:

try
{
   Stream s = File.Open(FileName, FileMode.Open, FileAccess.Read, FileShare.None);

   s.Close();

   return true;
}
catch (Exception)
{
   return false;
}

This will tryand open the file exclusively. If the file is already open it will throw an exception, where you can then (try to) close it and carry on.

Answer 3

For anyone interested in a one liner that avoids using a try-catch...

bool wbOpened = ((Application)Marshal.GetActiveObject("Excel.Application")).Workbooks.Cast<Workbook>().FirstOrDefault(x => x.Name == "Some Workbook.xlsx") != null;

Or with fully qualified names...

bool wbOpened = ((Microsoft.Office.Interop.Excel.Application)System.Runtime.InteropServices.Marshal.GetActiveObject("Excel.Application")).Workbooks.Cast<Microsoft.Office.Interop.Excel.Workbook>().FirstOrDefault(x => x.Name == "Some Workbook.xlsx") != null;

Of course, you may want to split this up a little. The main point is to use LINQ instead of try-catch to check for the workbook's existence.

Note 1: Marshal.GetActiveObject("Excel.Application") will throw an error if no instance of Excel is open. So unless otherwise guarantied or handled this should always be within a try-catch.

bool wbOpened = false;
try
{
   wbOpened = ((Application)Marshal.GetActiveObject("Excel.Application")).Workbooks.Cast<Workbook>().FirstOrDefault(x => x.Name == "Some Workbook.xlsx") != null;
}
catch
{
...
}

Note 2: Marshal.GetActiveObject("Excel.Application") will only return one instance of Excel. If you need to search any possible instance of Excel then the code below may be a better alternative.

---

Better Alternative

If you don't mind adding a helper class the code below may be a better alternative. Aside from being able to search any opened instance of Excel, it also allows you to check the full path and return the actual workbook object if found. It also avoids throwing an error if no instance of Excel is opened.

usage would be like this...

If (IsOpenedWB_ByName("MyWB.xlsx"))
{
   ....
}

or

Workbook wb = GetOpenedWB_ByPath("C:\MyWB.xlsx")
if (wb.obj == null) //If null then Workbook is not already opened
{
  ...
}

---

using System;
using System.Collections.Generic;
using System.Linq;
using Microsoft.Office.Interop.Excel;
using System.Runtime.InteropServices.ComTypes;

public class WBHelper
{
    public static bool IsOpenedWB_ByName(string wbName)
    {
        return (GetOpenedWB_ByName(wbName) != null);
    }

    public static bool IsOpenedWB_ByPath(string wbPath)
    {
        return (GetOpenedWB_ByPath(wbPath)  != null);
    }

    public static Workbook GetOpenedWB_ByName(string wbName)
    {
        return (Workbook)GetRunningObjects().FirstOrDefault(x => (System.IO.Path.GetFileName(x.Path) == wbName) && (x.Obj is Workbook)).Obj;
    }

    public static Workbook GetOpenedWB_ByPath(string wbPath)
    {
        return (Workbook)GetRunningObjects().FirstOrDefault(x => (x.Path == wbPath) && (x.Obj is Workbook)).Obj;
    }

    public static List<RunningObject> GetRunningObjects()
    {
        // Get the table.
        List<RunningObject> roList = new List<RunningObject>();
        IBindCtx bc;
        CreateBindCtx(0, out bc);
        IRunningObjectTable runningObjectTable;
        bc.GetRunningObjectTable(out runningObjectTable);
        IEnumMoniker monikerEnumerator;
        runningObjectTable.EnumRunning(out monikerEnumerator);
        monikerEnumerator.Reset();

        // Enumerate and fill list
        IMoniker[] monikers = new IMoniker[1];
        IntPtr numFetched = IntPtr.Zero;
        List<object> names = new List<object>();
        List<object> books = new List<object>();
        while (monikerEnumerator.Next(1, monikers, numFetched) == 0)
        {
            RunningObject running;
            monikers[0].GetDisplayName(bc, null, out running.Path);
            runningObjectTable.GetObject(monikers[0], out running.Obj);
            roList.Add(running);
        }
        return roList;
    }

    public struct RunningObject
    {
        public string Path;
        public object Obj;
    }

    [System.Runtime.InteropServices.DllImport("ole32.dll")]
    static extern void CreateBindCtx(int a, out IBindCtx b);
}

I adapted the GetRunningObjects() method in the code above from here.