Abstract
This is a collection of notes that are gathered as we try to construct an Add-in for Excel from within R. In other words, we will write R code that serves as an Add-In component for Excel. There are several things we might want to do.C:/Program Files/Common Files/DESIGNER
/
library(SWinTypeLibs) lib = LoadTypeLib("C:/Program Files/Common Files/DESIGNER/MSADDNDR.DLL")The IDTExtensibility2 entry in the library is an alias. We can resolve that using getElements and, as we might expect, it is simply an alias for the actual "_IDTExtensibility2" interface. So from now on, we talk about that interface. We can compute all the methods and member ids that we will implement in the usual way for an event server. The methods, as documented in many places, are
c("ext_cm_AfterStartup" = 0, "ext_cm_Startup" = 1, "ext_cm_External" = 2, "ext_cm_CommandLine" = 3)and we would be able to compare the value of mode in our function to either the integer value or the name.
OnConnection = function(app, mode, AddInInst, custom) { ExcelHost <<- app # Get the "Standard" toolbar, i.e. the main one. std = app[["CommandBars"]]$Item(3) # Add a new button to it.... btn = std$Controls()$Add(1) # and set its title. btn[["Caption"]] = "RDCOM Addin" btn[["Style"]] = 2 TRUE }Our next job is to make the add-in available to Excel. There are two steps this. The first is to make this a COM interface and register it. The second task is to tell Excel about it.
def = createCOMServerInfo(lib[["_IDTExtensibility2"]], methods = list(OnConnection = OnConnection), name = "RTestAddIn", help = "Test Add-in for Excel implemented in R") def@classId = "a8ddb932-7290-4077-05ae-b9a58db7a97f" library(RCOMServer) registerCOMClass(def, profile = "library(RDCOMEvents)")The result is an object of class COMServerInfo which contains the methods, the name-identifier mapping and the GUID of the interface being implemented. The registration stores this object so that it can be used when the R DCOM server is requested. Note that we add a profile argument. This is an R expression that is executed when the object is requested. Armed with this template, the function createCOMEventServer creates the low-level, native COM object which is implemented via the calls to the R functions. When the COM object is requested by the host application (e.g. Excel), the R COM class factory arranges to use this function to create an instance of the COM interface.
rpath = createRegistryPath(c("Software", "Microsoft", "Office", "11.0", "Excel", "Addins") "HKEY_CURRENT_USER") np = createRegistryKey(rpath, "RDCOMAddin") setRegistryValue(np, "LoadBehavior", 8) setRegistryValue(np, "CommandLineSafe", FALSE)
function() { addButton = function(app = Host) { std = app[["CommandBars"]]$Item(3) btn = std$Controls()$Add(1) btn[["Caption"]] = "RDCOM Addin" btn[["Style"]] = 2 } OnConnection = function(app, mode, AddInInst, custom) { Host <<- app } OnDisconnection = function(app, mode, AddInInst, custom) { Host <<- NULL } OnStartupComplete = function() { addButton(Host) } list(OnConnection = OnConnection, OnStartupComplete = OnStartupComplete) }