This is a trick I have given before on this blog but this time coded in C#. Windows allows applications to be queried for Accessibility APIs. Excel can return a COM pointer via such an API call. This means all one needs to acquire a callable COM pointer on an Excel.exe is its Hwnd.
Use case context - Competing with Microsoft.ACE.OLEDB.12.0
In yesterday's post I demonstrated a C# Custom Implementation for the OLEDB Simple Provider. This effectively allows us to write our own OLE DB Provider. The example code given was for in-memory arrays. There is no reason why we could not serve up blocks of Excel cells. Now, the Microsoft.ACE.OLEDB.12.0 OLEDB Provider already does serves Excel worksheets. However, there are a few minor issues that make me want to explore writing an alternative OLEDB Provider.
As demonstrated in yesterday's post we supply a connection string with the ProgId or our COM server (implemented as a COM Interoperable C# assembly). But as far as I can see the connection string does not provide any scope to specify details.
Instead of the connection string, details are specified in the string passed to the ADO.Recordset's Open() method. This means one cannot pass any pointers to Excel workbooks, sheets, ranges etc. One has to pack into one single string enough to identify (1) the Excel Session, (2) the Workbook, (3) the Worksheet, and (4) the Cell Range. This post focuses on identifying and acquiring an Excel session (a subsequent post will handle the remaining items).
Assumes Hwnd Known
Other code on this blog that does similar cycles through all the top windows registered to the Desktop. This code assumes that the Hwnd of Excel.exe session is known, so this is more efficient.
C# dynamic type is a lifesaver
Without the dynamic keyword much of this code would be twice as long with references to primary interop assemblies (PIA) etc. Of note is the fact that Excel.exe in fact returns a COM pointer to the Excel.Window object and thus we need to make a further call to acquire the Excel.Application.
Alternative to the Running Object Table
If all of this sounds like accessing the Running Object Table (ROT) then you'd be right. Normally, I suggest using the ROT. There are reports of a bug in Excel 2016, the GetObject() VBA function allows the accessing of objects registered in the ROT. GetObject() is reportedly broken. This bug would hinder access to ROT. Now, I have written a separate C# ROT Viewer and the source code is on my sister C# blog but if the Running Object Table is fading as a usable technique then finding another way to do cross process communication is useful.
Mind you, the following GetObject() code works for me ...
Sub TestGetObject()
Dim obj As Object
Set obj = GetObject(, "Excel.Application")
Debug.Assert obj Is Excel.Application
Set obj = GetObject(ThisWorkbook.FullName)
End SubC# Assembly Source Code
So this needs to be packed into a C# Assembly that is registered for Interop; Visual Studio will need to run with Administrator privileges to register.
using System;
using System.Runtime.InteropServices;
namespace XlMoniker
{
[ComVisible(true)]
public interface IXlMoniker
{
bool GetExcelByHwnd(int lhwndApp, ref object app);
//bool GetExcelRangeFromMoniker(string sMoniker, ref object rngRetVal);
//string ExcelRangeToMoniker(object rng);
}
[ClassInterface(ClassInterfaceType.None)]
[ComDefaultInterface(typeof(IXlMoniker))]
[ComVisible(true)]
public class XlMoniker : IXlMoniker
{
const char separator = '`';
[DllImport("user32.dll", SetLastError = true)]
static extern IntPtr FindWindowEx(IntPtr hwndParent, IntPtr hwndChildAfter, string lpszClass, string lpszWindow);
[DllImport("oleacc.dll", SetLastError = true)]
internal static extern int AccessibleObjectFromWindow(IntPtr hwnd, uint id, ref Guid iid,
[In, Out, MarshalAs(UnmanagedType.IUnknown)] ref object ppvObject);
bool IXlMoniker.GetExcelByHwnd(int lhwndApp2, ref object appRetVal)
{
bool bRetVal = false;
IntPtr lhwndApp = (IntPtr)lhwndApp2;
IntPtr lHwndDesk = FindWindowEx(lhwndApp, IntPtr.Zero, "XLDESK", "");
if (lHwndDesk != IntPtr.Zero)
{
IntPtr lHwndExcel7 = FindWindowEx(lHwndDesk, IntPtr.Zero, "EXCEL7", null);
if (lHwndExcel7 != IntPtr.Zero)
{
Guid IID_IDispatch = new Guid("{00020400-0000-0000-C000-000000000046}");
const uint OBJID_NATIVEOM = 0xFFFFFFF0;
object app = null;
if (AccessibleObjectFromWindow(lHwndExcel7, OBJID_NATIVEOM, ref IID_IDispatch, ref app) == 0)
{
dynamic appWindow = app;
appRetVal = appWindow.Application;
return true;
}
}
}
return bRetVal;
}
}
}Client VBA Source Code
I packed the C# code into the same Assembly as yesterday's post on OLE DB providers so it happes to share the same library name, SimpleOLEDBProvider1 .
Private Sub Test_XlMoniker_GetExcelByHwnd()
Dim oMoniker As SimpleOLEDBProvider1.XlMoniker
Set oMoniker = New SimpleOLEDBProvider1.XlMoniker
Dim obj As Excel.Application
If oMoniker.GetExcelByHwnd(Application.hwnd, obj) Then
Debug.Assert obj Is Application
End If
End SubCan I Implement the same IAccessible technique for my Applications?
So, given that this is a useful cross-process communication technique you might be thinking can I implement this technique for my code. The answer is yes, here is the link, Using OBJID_NATIVEOM to expose a native object model interface for a window _ Microsoft Docs. However, the article implies one has access to the windows message pump, one does in C++, in C# I'd have to research that. Asking on StackOverflow here.
