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

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 2

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

s.Close();

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

Answer 3

This is not especially nice - we'll try and open the file and examine the exception if it fails. I'm not sure you have any other choices in C#.

However, it is important to only handle the correct exception: Basically we try open the file with no sharing allowed. If it fails, AND we get the correct type of exception AND we get the correct message in the exception, then we know it is open.

// open the file with no sharing semantics (FileShare.None)
using (FileStream stream = new FileStream(file, FileMode.Open, FileAccess.Read, FileShare.None))
{
    try
    {
        stream.ReadByte();
        return false;
    }
    catch (IOException ex)
    {
        // catch ONLY the exception we are interested in, and check the message too
        if (ex.Message != null 
            && ex.Message.Contains("The process cannot access the file"));
        {
            return true;
        }

        // if the message was incorrect, this was not the IOException we were looking for. Rethrow it.
        throw;
    }
}

Obviosuly this sort of approach is brittle with respect to the exception message being changed in a future release of .Net. You may wish to complement such functionality with a test that purposely locks a file and then calls this to check it correctly detects the message.

Answer 4

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.