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.
[c language=”#”]
using System;
using System.Collections.Generic; //NEW
using System.Globalization;
using System.Reflection;
using System.Runtime.InteropServices;
using System.Text;
namespace WspAlertTestCL
{
/// <summary>
/// Interface definition for Excel.Window interface
/// </summary>
[Guid("00020893-0000-0000-C000-000000000046")]
[InterfaceType(ComInterfaceType.InterfaceIsIDispatch)]
public interface ExcelWindow
{
}
/// <summary>
/// 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))"
/// </summary>
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<object> 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<object> appList = new List<object>();
//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 >= 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
}
}
}
[/c]