Someone asked in the German Microsoft Answers Forum, if it is possible to get access to an Excel instance and then execute code from this instance. This gave me the idea to enumerate all open Excel instances by using the windows API and then try to access them with VBA.
The article describes how to create VBA Application Objects using the Windows API. Please note, that this article is a little bit shortened translated version of the two articles I wrote in the German section of this blog. The descriptions of the code in this article require that you are familiar with VBA and the use of controls like TreeViews, ListViews and Windows API functions. A sample application can be downloaded at the end of this post. The VBA code is unprotected.
A certainly well-known way to retrieve an object reference in VBA to another Excel Application Instance is the use of the “GetObject()” method. For example, by using the statement Set xlApp = GetObject(, “Excel.Application”), you can get a reference to the Excel instance, which was firstly opened. However, it is not possible to retrieve a reference to more Excel instances, if you do not know the name of an opened workbook in these instances.