Piggy backing on an Excel Instance - even when there are a few in Task Manager

Piggy backing on an Excel Instance – even when there are a few in Task Manager

So I found 1 great article that looked promising but it didn’t pan out because it was written before a change by Excel. Basically the problem is that GetObject (or c# GetActiveObject) only gets a RANDOM copy of a running instance. If there is more than one instance of Excel, for instance, and you are searching for a workbook (in my case one with a specific named range) you will not find the workbook unless you iterate through all the Application instances of Excel. If you look in Task Manager … why are there two? Simple. If you are in explorer and you click on a .xls* file and the preview pane is on – it starts an instance of Excel (one example).

Here is the article that did not work and here is a complicated one that is just as good as the GetObject (it gets only once instance) … HOWEVER … combining the two … now that is magic. I got it working. This article is a sub-article of the last post in which I iterated over all apps searching for a .visible instance in which to piggy back and run VBA the code in Excel. Why? Because I had LOTS of previous code already written in Excel – I just needed it to run. It starts and drives AutoCAD.

Final Code – mostly the same as the 2nd link above.

look for my lines called // SUBSTITUTE and // NEW to understand the before and after picture. It works. I also return a List<Application> object which is different from the original which simply spit out the results to the Console. It is these sets that I iterate over trying desperately to use a .Visible application object over an hidden one.

using System;
using System.Collections.Generic; //NEW
using System.Globalization;
using System.Reflection;
using System.Runtime.InteropServices;
using System.Text;

namespace WspAlertTestCL
{

 /// &lt;summary&gt;
 /// Interface definition for Excel.Window interface
 /// &lt;/summary&gt;
 [Guid("00020893-0000-0000-C000-000000000046")]
 [InterfaceType(ComInterfaceType.InterfaceIsIDispatch)]
 public interface ExcelWindow
 {
 }

 /// &lt;summary&gt;
 /// This class is needed as a workaround to http://support.microsoft.com/default.aspx?scid=kb;en-us;320369
 /// Excel automation will fail with the follwoing error on systems with non-English regional settings:
 /// "Old format or invalid type library. (Exception from HRESULT: 0x80028018 (TYPE_E_INVDATAREAD))" 
 /// &lt;/summary&gt;
 class UILanguageHelper : IDisposable
 {
 private CultureInfo _currentCulture;

 public UILanguageHelper()
 {
 // save current culture and set culture to en-US 
 _currentCulture = System.Threading.Thread.CurrentThread.CurrentCulture;
 System.Threading.Thread.CurrentThread.CurrentCulture = new CultureInfo("en-US");
 }

 public void Dispose()
 {
 // reset to original culture 
 System.Threading.Thread.CurrentThread.CurrentCulture = _currentCulture;
 }
 }

 //class Program //SUBSTITUTE
 class ExcelWIndowIterator //NEW
 {
 [DllImport("user32.dll", SetLastError = true)] //SUBSTITUTE - we probably don't need this any longer.
 static extern IntPtr FindWindow(string lpClassName, string lpWindowName); //SUBSTITUTE - we probably don't need this any longer.

 [DllImport("Oleacc.dll")]
 static extern int AccessibleObjectFromWindow(int hwnd, uint dwObjectID, byte[] riid, out ExcelWindow ptr);

 public delegate bool EnumChildCallback(int hwnd, ref int lParam);

 [DllImport("User32.dll")]
 public static extern bool EnumChildWindows(int hWndParent, EnumChildCallback lpEnumFunc, ref int lParam);

 [DllImport("User32.dll")]
 public static extern int GetClassName(int hWnd, StringBuilder lpClassName, int nMaxCount);


 [DllImport("User32")] // NEW
 public static extern int FindWindowEx( 
 int hwndParent, int hwndChildAfter, string lpszClass,
 int missing);

 public static bool EnumChildProc(int hwndChild, ref int lParam)
 {
 StringBuilder buf = new StringBuilder(128);
 GetClassName(hwndChild, buf, 128);
 if (buf.ToString() == "EXCEL7") // SUBSTITUTE - I will leave this here but perhaps this might last longer in time without a re-write in the future. Only time will tell ... buf.StartsWith("EXCEL")
 {
 lParam = hwndChild;
 return false;
 }
 return true;
 }

 //static void Main(string[] args) //SUBSTITUTE
 public List&lt;object&gt; MainItems() //NEW (instanciate it w. as a class and access this method to return a list of Excel.apps running
 {
 // I got this from here http://stackoverflow.com/questions/779363/how-to-use-use-late-binding-to-get-excel-instance
 // but this version only got ONE instance. What if there are several instances of Excel running? Well, the following
 // link http://stackoverflow.com/questions/13807102/find-all-open-excel-workbooks iterated over a few instances, but 
 // didn't quite work. So ... but ... putting both ideas together ... BINGO!! as we can iterate over all instances
 // look for my lines called // SUBSTITUTE and // NEW

 // Use the window class name ("XLMAIN") to retrieve a handle to Excel's main window.
 // Alternatively you can get the window handle via the process id:
 // int hwnd = (int)Process.GetProcessById(excelPid).MainWindowHandle;

 List&lt;object&gt; appList = new List&lt;object&gt;();

 //int hwnd = (int)FindWindow("XLMAIN", null); //SUBSTITUTE
 int hwnd = FindWindowEx(0, 0, "XLMAIN", 0);

 // if (hwnd != 0) // SUBSTITUTE
 while (hwnd != 0) // NEW
 {
 int hwndChild = 0;

 // Search the accessible child window (it has class name "EXCEL7") 
 EnumChildCallback cb = new EnumChildCallback(EnumChildProc);
 EnumChildWindows(hwnd, cb, ref hwndChild);

 if (hwndChild != 0)
 {
 // We call AccessibleObjectFromWindow, passing the constant OBJID_NATIVEOM (defined in winuser.h) 
 // and IID_IDispatch - we want an IDispatch pointer into the native object model.
 //
 const uint OBJID_NATIVEOM = 0xFFFFFFF0;
 Guid IID_IDispatch = new Guid("{00020400-0000-0000-C000-000000000046}");
 ExcelWindow ptr;

 int hr = AccessibleObjectFromWindow(hwndChild, OBJID_NATIVEOM, IID_IDispatch.ToByteArray(), out ptr);

 if (hr &gt;= 0)
 {
 // We successfully got a native OM IDispatch pointer, we can QI this for
 // an Excel Application using reflection (and using UILanguageHelper to 
 // fix http://support.microsoft.com/default.aspx?scid=kb;en-us;320369)
 //
 using (UILanguageHelper fix = new UILanguageHelper())
 {
 object xlApp = ptr.GetType().InvokeMember("Application", BindingFlags.GetProperty, null, ptr, null);

 object version = xlApp.GetType().InvokeMember("Version", BindingFlags.GetField | BindingFlags.InvokeMethod | BindingFlags.GetProperty, null, xlApp, null);
 //Console.WriteLine(string.Format("Excel version is: {0}", version)); //SUBSTITUTE
 Console.WriteLine(string.Format("Excel handle is: {1} version is: {0}", version, hwndChild.ToString()));

 appList.Add(xlApp); //NEW
 }
 
 }
 }
 hwnd = FindWindowEx(0, hwnd, "XLMAIN", 0); //SUBSTITUTE //get the next instance if there is one
 }
 return appList; //NEW
 }
 }
}