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(" ");
foreach (string s in table)
{
XmlElement rte = dom.CreateElement("RotTableEntry");
rte.InnerText = s;
dom.DocumentElement.AppendChild(rte);
}
return dom.OuterXml;
}
///
/// 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().
///
public int RevokeActiveObject(int register)
{
int hr;
hr = RotNativeMethods.RevokeActiveObject(register, IntPtr.Zero);
return hr;
}
public RotTableEntry[] DetailedTableAsArray()
{
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)
{
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(" ");
foreach (RotTableEntry tb in table)
{
XmlElement rte = dom.CreateElement("RotTableEntry");
XmlElement dn = dom.CreateElement("DisplayName");
dn.InnerText = tb.displayName;
rte.AppendChild(dn);
XmlElement lcd = dom.CreateElement("LastChange");
DateTime.UtcNow.ToString("o");
lcd.InnerText = tb.lastChange.ToString("s", System.Globalization.CultureInfo.InvariantCulture);
rte.AppendChild(lcd);
XmlElement monType = dom.CreateElement("MonikerType");
monType.InnerText = tb.monikerDetails.monikerType;
rte.AppendChild(monType);
XmlElement clsNam = dom.CreateElement("ClassName");
clsNam.InnerText = tb.className;
rte.AppendChild(clsNam);
dom.DocumentElement.AppendChild(rte);
}
return dom.OuterXml;
}
public string DetailedTableAsJson()
{
RotTableEntry[] table = this.DetailedTableAsArray();
string wholeJson = "";
foreach (RotTableEntry tbe in table)
{
string jsonTE = "";
{
string displayNameString = tbe.displayName.Replace(@"\", @"\\");
jsonTE = jsonTE + "\"displayName\":\"" + displayNameString + "\",";
}
string jsonTEMonDetails = "";
{
jsonTEMonDetails = jsonTEMonDetails + "\"monikerType\":\"" + tbe.monikerDetails.monikerType + "\",";
jsonTEMonDetails = jsonTEMonDetails + "\"monikerClassId\":\"" + tbe.monikerDetails.monikerClassId + "\"";
jsonTEMonDetails = "{" + jsonTEMonDetails + "}";
jsonTE = jsonTE + "\"monikerDetails\":" + jsonTEMonDetails + "";
}
{
if (tbe.lastChange != DateTime.MinValue)
{
string lastChangeString = tbe.lastChange.ToString("s", System.Globalization.CultureInfo.InvariantCulture);
jsonTE = jsonTE + ",\"lastChange\":\"" + lastChangeString + "\"";
}
else
{
jsonTE = jsonTE + ",\"lastChange\":\"\"";
}
}
{
jsonTE = jsonTE + ",\"className\":\"" + tbe.className + "\"";
}
jsonTE = "{" + jsonTE + "}";
if (wholeJson.Length > 0) { wholeJson = wholeJson + ","; }
wholeJson = wholeJson + jsonTE;
}
return "[" + wholeJson + "]";
}
}
}
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
This comment has been removed by the author.
ReplyDeleteC For Windows Desktop: C - Com - Running Object Table Viewer - Example Of C Com Server Scriptable From Vba >>>>> Download Now
Delete>>>>> Download Full
C For Windows Desktop: C - Com - Running Object Table Viewer - Example Of C Com Server Scriptable From Vba >>>>> Download LINK
>>>>> Download Now
C For Windows Desktop: C - Com - Running Object Table Viewer - Example Of C Com Server Scriptable From Vba >>>>> Download Full
>>>>> Download LINK O1
Hi, Thanks for giving out this code - it looks like exactly what I need. However, I've been trying to get it to work and running into problems.
ReplyDeleteSo, starting off (as one who doesn't know much C#), I couldn't find a project of type '.NET Framework assembly project' in VS 2017, so chose 'Class Library (.Net Framework)' instead.
Then got errors on the lines 'List itemsList = new List();' - The error was 'Error CS0305 Using the generic type 'List' requires 1 type arguments'. So set the first instance in the code to have string type and the second to RotTableEntry type. This all seemed OK & managed to compile the dll. (EDIT: I see when I posted this note initially that the blog removed the actual type when I posted it, so clearly it is taking the angle brackets as HTML, which is probably why your original code lost the types as well)
Then opened Excel and the reference was there, so check it and pasted in your code. But when I ran it, get an error saying 'user-defined type not defined' on the line 'Dim oROT As RunningObjectTable.RotViewer'. And RunningObjectTable and its methods do not show up in intellisense. So even though I have the reference it is somehow not really connecting. I opened the object browser in Excel VBA and the dll is listed there and all the objects/methods appear. But somehow it is still not connecting in the VBA script. To check, I opened VB6, made the reference and all seems OK there (i.e. intellisense shows the methods & autocomplete works.) So it is just in Excel VBA where it wont work.
If it helps, I am running Win 10 64 but have Excel 32bit on my machine. Any suggestions on how to resolve this? Also, how would I distribute this dll and register it on other people's machines? (when I give them the workbook I am working on.)
Thanks very much!
Zos
And just managed to fix it :) I see I had to remove the class name & just use the method i.e. don't use Dim oROT As RunningObjectTable.RotViewer but instead Dim oROT As RotViewer etc. And now all OK.
DeleteBut I do have the question of how to register this dll on other peoples machines. Any suggestions? Thanks again.
... And through searching more, see the answer to the last piece is regasm.exe in one of the C:\Windows\Microsoft.NET\... frameworks. So no assistance needed and thanks again for the code! (I could delete all the above, but decided to leave it for anyone facing similar difficulty.)
DeleteC For Windows Desktop: C - Com - Running Object Table Viewer - Example Of C Com Server Scriptable From Vba >>>>> Download Now
ReplyDelete>>>>> Download Full
C For Windows Desktop: C - Com - Running Object Table Viewer - Example Of C Com Server Scriptable From Vba >>>>> Download LINK
>>>>> Download Now
C For Windows Desktop: C - Com - Running Object Table Viewer - Example Of C Com Server Scriptable From Vba >>>>> Download Full
>>>>> Download LINK