Summary: Migrated from our sister Excel blog, this source code builds to a .NET class library that also serves as a COM Server. The logic inspects and reports on the Running Object Table.
So COM has a machine wide table for registering objects that can be acquired by other processes, called the Running Object Table. Processes that are COM-enabled like Excel, Word and Internet Explorer can both deposit and fetch objects from the ROT. There used to be a ROT viewer that shipped with earlier versions of Visual Studio but it went away. So I wrote a new one, or at least a core dll, COM enabled assembly that can be scripted from Excel VBA and other COM clients. I put the code on StackOverflow here but I've had some of my questions deleted so I'm placing a copy here as well. And there was a bug to fix so that is fixed here.
Also I am including some sample VBA client script.
This code base is actually a good example of a C# Com server. We define interfaces separately from the classes. Use this project as a template for your own COM Dlls.
The C# .NET Framework assembly
Start a .NET Framework assembly project, in AssemblyInfo.cs change to [assembly: ComVisible(true)] and in the Project Properties->Build tab under the Output section ensure the 'Register for COM interop' checkbox is checked.
You will need to run Visual Studio with admin rights so it can register the dll.
using Microsoft.Win32;
using System;
using System.Collections.Generic;
using System.IO;
using System.Runtime.InteropServices;
using System.Runtime.InteropServices.ComTypes;
using System.Xml;
// Courteously curated and posted to Stack Overflow by S Meaden from MSDN samples and documentation
namespace RotViewerScriptable
{
internal static class RotNativeMethods
{
[DllImport("ole32.dll", PreserveSig = false)]
internal static extern void CreateBindCtx(uint reserved, out IBindCtx ppbc);
[DllImport("ole32.dll")]
internal static extern int GetRunningObjectTable(int reserved,
out IRunningObjectTable prot);
[DllImport("ole32.dll")]
internal static extern int CreateFileMoniker([MarshalAs(UnmanagedType.LPWStr)] string lpszPathName, out System.Runtime.InteropServices.ComTypes.IMoniker ppmk);
[DllImport("oleaut32.dll")]
internal static extern int RevokeActiveObject(int register, IntPtr reserved);
[DllImport("kernel32.dll")]
internal static extern bool FileTimeToLocalFileTime([In] ref System.Runtime.InteropServices.ComTypes.FILETIME lpFileTime,
out System.Runtime.InteropServices.ComTypes.FILETIME lpLocalFileTime);
[DllImport("ole32.dll", CharSet = CharSet.Unicode, ExactSpelling = true, PreserveSig = false)]
[return: MarshalAs(UnmanagedType.LPWStr)]
internal static extern string StringFromCLSID([MarshalAs(UnmanagedType.LPStruct)] Guid rclsid);
[DllImport("ole32.dll")]
internal static extern int IIDFromString([MarshalAs(UnmanagedType.LPWStr)] string lpsz,
out Guid lpiid);
}
public interface IMonikerDetails
{
string monikerType { get; set; }
string monikerClassId { get; set; }
}
[ClassInterface(ClassInterfaceType.None)]
[ComDefaultInterface(typeof(IMonikerDetails))]
public class MonikerDetails : IMonikerDetails
{
public string monikerType { get; set; }
public string monikerClassId { get; set; }
}
///
/// We have to make this a class instead of a struct so VBA can script against it
///
public interface IRotTableEntry
{
string displayName { get; set; }
DateTime lastChange { get; set; }
string className { get; set; }
MonikerDetails monikerDetails { get; set; }
}
[ClassInterface(ClassInterfaceType.None)]
[ComDefaultInterface(typeof(IRotTableEntry))]
public class RotTableEntry : IRotTableEntry
{
public string displayName { get; set; }
public DateTime lastChange { get; set; }
public string className { get; set; }
public MonikerDetails monikerDetails { get; set; }
}
///
/// Be a good COM citizen and define the interface separately.
/// In AssemblyInfo.cs we have [assembly: ComVisible(true)].
/// In Project Properties->Build tab under the Output section we have checked the 'Register for COM interop' checkbox.
///
public interface IRotViewer
{
RotTableEntry[] DetailedTableAsArray();
string DetailedTableAsXml();
string DetailedTableAsJson();
string[] TableAsStringArray();
string TableAsXml();
string TableAsJson();
object GetObject(string sRotEntry);
bool IsExcelFileName(string filename);
int[] RegisterObject(object obj, string stringId);
int RevokeActiveObject(int register);
}
///
/// A single class that reads the Running Object Table and is instantiable/creatable from VBA, JScript and scripting clients.
///
[ClassInterface(ClassInterfaceType.None)]
[ComDefaultInterface(typeof(IRotViewer))]
public class RotViewer : IRotViewer
{
///
/// This is the equivalent of VBA's GetObject where one supplies a filename or other moniker to retrieve an interface pointer to object.
///
///
/// A COM interface pointer to object in running object table specified by entry.
public object GetObject(string sRotEntry)
{
Object retVal = null;
IBindCtx ctx;
IRunningObjectTable table;
IEnumMoniker mon;
IMoniker[] lst = new IMoniker[1];
RotNativeMethods.CreateBindCtx(0, out ctx);
ctx.GetRunningObjectTable(out table);
table.EnumRunning(out mon);
while (mon.Next(1, lst, IntPtr.Zero) == 0)
{
string displayName;
lst[0].GetDisplayName(ctx, lst[0], out displayName);
if (displayName == sRotEntry)
{
table.GetObject(lst[0], out retVal);
}
}
return retVal;
}
///
/// For VBA Clients we can use COM interop compatible types to return table as string array.
///
/// An string array of all the entries in the Running Object Table.
public string[] TableAsStringArray()
{
List itemsList = new List();
IBindCtx ctx;
IRunningObjectTable table;
IEnumMoniker mon;
IMoniker[] lst = new IMoniker[1];
RotNativeMethods.CreateBindCtx(0, out ctx);
ctx.GetRunningObjectTable(out table);
table.EnumRunning(out mon);
while (mon.Next(1, lst, IntPtr.Zero) == 0)
{
string displayName;
lst[0].GetDisplayName(ctx, lst[0], out displayName);
itemsList.Add(displayName);
}
return itemsList.ToArray();
}
///
/// For clients that prefer to work with Xml documents call this.
///
/// An Xml string of a document of all the entries in the Running Object Table.
public string TableAsXml()
{
string[] table = this.TableAsStringArray();
XmlDocument dom = new XmlDocument();
dom.LoadXml("
/// Call this from Javascript and then use JSON.parse() to get the entries as an array.
///
/// A JSON string of all the entries in the Running Object Table.
public string TableAsJson()
{
string[] table = this.TableAsStringArray();
string json = "";
foreach (string s in table)
{
string text = s.Replace(@"\", @"\\");
if (json.Length > 0) { json = json + ","; }
json = json + "\"" + text + "\"";
}
return "[" + json + "]";
}
///
/// Utility method to help clients filter items.
///
/// An entry for the Running Object Table.
/// True if it is an Excel file.
public bool IsExcelFileName(string filename)
{
string extension = Path.GetExtension(filename).ToUpper();
return (extension == ".XLS" || extension == ".XLSX" || extension == ".XLSM");
}
///
/// This allows us to register an object in the Running Object Table
///
/// A Com interface pointer owned by the object
/// A display name for the object
/// Returns two integers as an array, the first is the HRESULT of system calls and if successful the second is the registration id (regid). You'll need the regid to revoke from the ROT when tidying up.
public int[] RegisterObject(object obj, string stringId)
{
int[] retval = new int[] { 0, 0 };
int regId = -1;
System.Runtime.InteropServices.ComTypes.IRunningObjectTable pROT = null;
System.Runtime.InteropServices.ComTypes.IMoniker pMoniker = null;
int hr;
if ((hr = RotNativeMethods.GetRunningObjectTable((int)0, out pROT)) != 0)
{
retval[0] = hr;
return retval; //(hr);
}
// File Moniker has to be used because in VBS GetObject only works with file monikers in the ROT
if ((hr = RotNativeMethods.CreateFileMoniker(stringId, out pMoniker)) != 0)
{
retval[0] = hr;
return retval; //(hr);
}
int ROTFLAGS_REGISTRATIONKEEPSALIVE = 1;
regId = pROT.Register(ROTFLAGS_REGISTRATIONKEEPSALIVE, obj, pMoniker);
retval[0] = 0;
retval[1] = regId;
return retval;
}
///
/// Removes an object previously registered with RegisterObject(,).
///
/// The registration id (regid) returned from RegisterObject().
/// itemsList = new List();
IBindCtx ctx;
IRunningObjectTable table;
IEnumMoniker mon;
IMoniker[] lst = new IMoniker[1];
RotNativeMethods.CreateBindCtx(0, out ctx);
ctx.GetRunningObjectTable(out table);
table.EnumRunning(out mon);
while (mon.Next(1, lst, IntPtr.Zero) == 0)
{
RotTableEntry item = new RotTableEntry();
item.monikerDetails = new MonikerDetails();
{
Guid guid;
lst[0].GetClassID(out guid);
item.monikerDetails.monikerClassId = RotNativeMethods.StringFromCLSID(guid);
switch (item.monikerDetails.monikerClassId)
{
case "{00000303-0000-0000-C000-000000000046}":
item.monikerDetails.monikerType = "FileMoniker";
break;
case "{00000304-0000-0000-C000-000000000046}":
item.monikerDetails.monikerType = "ItemMoniker";
break;
case "{00000305-0000-0000-C000-000000000046}":
item.monikerDetails.monikerType = "AntiMoniker";
break;
case "{00000306-0000-0000-C000-000000000046}":
item.monikerDetails.monikerType = "PointerMoniker";
break;
case "{00000308-0000-0000-C000-000000000046}":
item.monikerDetails.monikerType = "PackageMoniker";
break;
case "{00000309-0000-0000-C000-000000000046}":
item.monikerDetails.monikerType = "CompositeMoniker";
break;
case "{0000031A-0000-0000-C000-000000000046}":
item.monikerDetails.monikerType = "ClassMoniker";
break;
default:
{
RegistryKey monikerClassKey = Registry.ClassesRoot.OpenSubKey("CLSID\\" + item.monikerDetails.monikerClassId);
if (monikerClassKey == null)
{
item.monikerDetails.monikerType = "Failed to identify moniker";
}
else
{
item.monikerDetails.monikerType = monikerClassKey.GetValue(null).ToString();
}
}
break;
}
}
{
string displayName;
lst[0].GetDisplayName(ctx, lst[0], out displayName);
item.displayName = displayName;
item.className = "";
}
{
if (item.monikerDetails.monikerType == "FileMoniker")
{
System.Runtime.InteropServices.ComTypes.FILETIME ft;
table.GetTimeOfLastChange(lst[0], out ft);
long hFT2 = (((long)ft.dwHighDateTime) << 32) + ft.dwLowDateTime;
DateTime dte = DateTime.FromFileTime(hFT2);
item.lastChange = dte;
//http://snipplr.com/view/32409/
}
if (item.monikerDetails.monikerType == "ItemMoniker")
{
string coreGuid = "";
{
if ((item.displayName.Substring(0, 1) == "!") && (item.displayName.Length >= 38))
{
coreGuid = item.displayName.Substring(1, 38);
RegistryKey key = null;
key = Registry.ClassesRoot.OpenSubKey("CLSID\\" + coreGuid);
if (key == null)
{
key = Registry.ClassesRoot.OpenSubKey("Wow6432Node\\CLSID\\" + coreGuid);
}
if (key != null)
{
item.className = key.GetValue(null).ToString();
}
}
}
}
}
itemsList.Add(item);
}
return itemsList.ToArray();
}
///
/// For clients that prefer to work with Xml documents call this.
///
/// An Xml string of a document of all the detailed entries in the Running Object Table.
public string DetailedTableAsXml()
{
RotTableEntry[] table = this.DetailedTableAsArray();
XmlDocument dom = new XmlDocument();
dom.LoadXml("
The client VBA code
Once the C# project is built (with admin rights) then its type library should be visible in the VBA IDE Tools->References so you should now be able to run the following code...
Function FindARunningExcel() As Excel.Application
Dim oROT As RunningObjectTable.RotViewer
Set oROT = New RunningObjectTable.RotViewer
'Dim sTableAsStringArray() As String
'sTableAsStringArray() = oROT.TableAsStringArray
'Dim sTableAsJson As String
'sTableAsJson = oROT.TableAsJson
'Dim sTableAsXml As String
'sTableAsXml = oROT.TableAsXml
Dim v() As RunningObjectTable.RotTableEntry
v() = oROT.DetailedTableAsArray()
Dim lLoop As Long
For lLoop = LBound(v) To UBound(v)
Dim oEntryLoop As RunningObjectTable.RotTableEntry
Set oEntryLoop = v(lLoop)
If oROT.IsExcelFileName(oEntryLoop.DisplayName) Then
Debug.Print oEntryLoop.DisplayName
If TypeName(GetObject(oEntryLoop.DisplayName).Parent) = "Application" Then
Dim xlApp As Excel.Application
Set xlApp = GetObject(oEntryLoop.DisplayName).Parent
End If
End If
Next lLoop
Set FindARunningExcel = xlApp
End Function
