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.
If there are more than nine workbooks open and you click the Switch Windows tool, Excel displays an option that says "More Windows." Click the option and you can see a display of all open workbooks. Selecting a workbook from this list ends up in that workbook being displayed.
Click the File tab of the ribbon. At the left side of the screen, click Info. (This is probably displayed by default.) Immediately under the file name is the location for the file.
How can I tell if a sheet is protected? The Protect Sheet option on the ribbon changes to Unprotect Sheet when a sheet is protected. To view this option, click the Review tab on the ribbon, and in Changes, see Unprotect Sheet.
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.
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.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With