Create VBA Application Objects by using the Windows API

Create VBA Application Objects by using the Windows API

from Excel Ticker by Mourad Louha

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.

The Windows operating system manages and addresses visible and invisible windows (for example Excel Application Windows, Toolbars or Workbook windows) by using handles. The windows are arranged in a hierarchy, with the desktop on the top level. The handles are created dynamically and each handle is unique. To communicate, for example to inform a windows that a mouse click was done or request a screen update, Windows uses a special messaging system.

Ok, let's have a look on the windows hierarchy. I am a happy owner of Microsoft Visual Studio 2010, which includes a tool (Microsoft Spy++) to identify and display all current opened windows. The screenshot shows the hierarchy with an opened Excel instance.

The Excel 2010 application window and the workbook window "Dummy.Excel.2010.xlsx" are respectively represented by the hexadecimal handles "0015058A" and "00020642" in the screenshot. The workbook window is hierarchically located below the main window, as it depends on the main window. Additionally, we can see some designations on the right side of the handles, namely the title and the class name of the window. The class name can be used later for getting access to the Excel application window.

If you download the sample application, you will find a UserForm in the VBA project containing a TreeView for displaying the windows hierarchy, several EditBoxes and Labels for displaying some information about a selected item in the TreeView and a ListView for enumerating the opened workbooks of the selected Excel instance. The VBA project also includes the three modules "MLP_Api", "MLP_Controls" and "MLP_Run". The first module contains all imported Windows API functions and some help functions. The second module includes code for populating the TreeView. And the last module just contains code for running the UserForm or quitting the application. The UserForm itself includes the usual event handlers and some private help functions.

Enumerating the windows handles and populating the TreeView

How can we retrieve the windows handles? The first thing we have to do is to import the appropriate Windows API functions. The first window in the hierarchy is the desktop window, for getting a handle to this window, we can use the function "GetDesktopWindow()". For retrieving the title, the class name and the parent window handle of a window, we can respectively use "GetWindowTextA()", "GetClassNameA()" and "GetParent()". And, as we would like to traverse the hierarchy, we will need the Windows API function "EnumChildWindows()". Finally, for searching a specific window, we can use the "FindWindowExA()" function. Ok, I know that the Windows API really provides many API functions, but a little bit searching on the internet and reading the documentation on MSDN helps to find the right API function. And the API functions are generally well named, so searching for the purpose of the API function often quickly leads to success. The code below shows an extract of the imported Windows API function:

Public Declare Function mlfpApiEnumChildWindows Lib "user32" _ Alias "EnumChildWindows" (ByVal hWndParent As Long, _ ByVal lpEnumFunc As Long, _ ByVal lParam As Long) As Long Public Declare Function mlfpApiFindWindow Lib "user32" _ Alias "FindWindowExA" (ByVal hWnd1 As Long, _ ByVal hWnd2 As Long, _ ByVal lpsz1 As String, _ ByVal lpsz2 As String) As Long Public Declare Function mlfpApiGetClassName Lib "user32" _ Alias "GetClassNameA" (ByVal hwnd As Long, _ ByVal lpClassName As String, _ ByVal nMaxCount As Long) As Long Public Declare Function mlfpApiGetDesktopWindow Lib "user32" _ Alias "GetDesktopWindow" () As Long Public Declare Function mlfpApiGetParent Lib "user32" _ Alias "GetParent" (ByVal hwnd As Long) As Long Public Declare Function mlfpApiGetWindowText Lib "user32" _ Alias "GetWindowTextA" (ByVal hwnd As Long, _ ByVal lpString As String, _ ByVal cch As Long) As Long

After I had imported the API functions, I created the UserForm and placed the controls on it. Then I added the main event handlers and implemented the function for populating the TreeView. The code, as mentioned before, is located in the module "MLP_Controls". The reason is that I would be able to reuse this function. The code window below shows only an excerpt the function "mlfpControlsTreeviewWindows()".

Public Function mlfpControlsTreeviewWindows(Form As Object, _ Control As String, _ Code As String, _ Childs As Boolean, _ Excel As Boolean) _ As Long Dim e As Long Dim h As Long Dim n As Long Dim r As Long Dim c As String Dim t As String ' Errors... On Error Resume Next ' : ' : ' : ' Desktop... r = mlfpApiGetDesktopWindow ' Desktop... mlvhFormObject.Controls(mlvhFormControl).Nodes.Add , , "K" & _ CStr(r), mlcpControlsStringDesktop ' : ' : ' : ' Loop... Do ' Increase... n = n + 1 ' Handle... h = mlfpApiFindWindow(mlfpApiGetDesktopWindow, h, _ Code, vbNullString) ' Check... If h <> 0 And h <> Application.hwnd Then ' : ' : ' : ' Childs... If Childs Then ' Key... mlvhFormControlKey = "K" & CStr(h) ' Enumerate... mlfhChildEnumerator h End If End If Loop While h <> 0 ' Reset... Set mlvhFormObject = Nothing ' Return... mlfpControlsTreeviewWindows = 0 End Function

This function firstly gets the handle of the desktop window and adds a new node to the TreeView, representing the root. Then, all other windows are enumerated in a loop. For each window found, the code adds a new TreeView node. And, the keys for each new node in the TreeView are systematically set to "K & Window Handle". This allows to quickly find the appropriate parent node in the TreeView (we just need to call "mlfpApiGetParent()" representing the API function "GetParent()"). The Tag property of each node is used to store some information about the window. When performing the loop, the function "mlfhChildEnumerator()" is called:

Private Function mlfhChildEnumerator(Handle As Long) As Long Dim r As Long ' Errors... On Error Resume Next ' Enumerate... r = mlfpApiEnumChildWindows(Handle, AddressOf mlfhChild, 0) ' Return... mlfhChildEnumerator = r End Function

At this point, it is important to know that the API function expects a callback function, which also must provide two arguments. Every time the callback is executed by the Windows operating system, the arguments are filled and we can extract the needed information from there. My callback function "mlfhChild()" is very similar to the main function for populating the tree. New nodes are created and the Tag property is filled for each new node. That's all for displaying the windows handle hierarchy in the TreeView.

Create VBA Application Objects from the window handles

Andrew Whitechapel has written on his blog a very interesting article how to get an Application Object in a Shimmed Automation Add-In. Although the code is implemented for .NET, we can easily transfer the code to VBA. As the Office applications allow COM Automation, we will just need to find an appropriate window handle and "transform" it to a VBA object. If this object will be a child object or another object differing from the main application object, this will not be a problem, as we can reach the main application object by using the "Application" property of the found object.

The Windows API function "AccessibleObjectFromWindow()" allows to check, if a valid interface can be found and associated to a VBA object. The trick is here to call the function by passing a VBA object and a previously filled data structure to its arguments. If the API function succeeds, then we can referenciate a VBA object.

Public Function mlfpApiAccessibleObjectCheck(Handle As Long, _ Key As String) _ As Boolean Dim e As Long Dim r As Boolean Dim a As Object Dim o As Object Dim t As apiUUID ' Errors... On Error Resume Next ' Try... e = apiIIDFromString(StrPtr(Key), t) e = apiAccessibleObject(Handle, apiObjIDNativeOM, t, o) ' Check... If Not e <> 0 Then ' Try... Set a = o.Application ' Result... r = Not CBool(Err.Number <> 0) ' Clear... Set a = Nothing Else r = False End If ' Return... mlfpApiAccessibleObjectCheck = r End Function

The sample application mainly implements the calls to the function above in the "mlfhTreeviewIterate()" function, which can be found in the code module for the UserForm. The function also populates the ListView control with the documents open in the selected Excel instance. By clicking on a button in the UserForm, you can create an application object for the selected instance and some information about the instance is displayed.

The previously described method allows retrieving the application objects for the opened Excel instances in which a document is open. In case no documents are opened in the instance, the method fails.

Creating a new document by using the Windows API

So, how can this problem resolved? If we would be able to send a message to the Excel instance for creating a new document and the parse again the windows hierarchy of this instance, this should be successful. The simplest way to create a new document in Excel is to use the shortcut "Ctrl + N". In this case, no dialogue is displayed and the document created immediately.

The first thing I tried, was to send a message to the Excel instance by using the Windows API "PostMessage()" function. Before I sent the message, I had stored the current keyboard state in a variable, then modified the state of the Control key and sent the message. After sending the message, I restored the original keyboard state. However, this did not work. Excel did not recognize the keyboard state; perhaps Excel is continuously checking the keyboard state and therefore just before sending the message for the "N" key, the "Ctrl" is already resetted.

So, I had to find another solution. The Windows API function "SendInput()" allows to send a key sequence. The function expects an array of "Input" structures, which can be mouse events, keyboard events or hardware events. The code below shows parts of the code from the "MLP_Api" module:

' Private... Private Const mlchInputKeyboard As Long = 1 Private Const mlchEventKeyDown As Long = &H0 Private Const mlchEventKeyUp As Long = &H2 ' Private... Private Declare Sub apiCopyMemory Lib "kernel32" _ Alias "RtlMoveMemory" (pDst As Any, _ pSrc As Any, _ ByVal ByteLen As Long) Private Declare Function apiSendInput Lib "user32.dll" _ Alias "SendInput" (ByVal nInputs As Long, _ pInputs As apiInput, _ ByVal cbSize As Long) As Long Private Declare Function apiSetForegroundWindow Lib "user32" _ Alias "SetForegroundWindow" (ByVal hwnd As Long) As Long ' Types... Private Type apiInput dwType As Long xi(0 To 23) As Byte End Type Private Type apiInputKeyboard wVk As Integer wScan As Integer dwFlags As Long time As Long dwExtraInfo As Long End Type ' Send... Public Function mlfpApiSendCreateDocument(Handle As Long, _ Control As Long, _ Key As Long) As Long Dim t(0 To 3) As apiInput Dim k(0 To 3) As apiInputKeyboard ' Errors... On Error Resume Next ' Inputs... k(0).wVk = Control k(1).wVk = Key k(2).wVk = Key k(3).wVk = Control k(0).dwFlags = mlchEventKeyDown k(1).dwFlags = mlchEventKeyDown k(2).dwFlags = mlchEventKeyUp k(3).dwFlags = mlchEventKeyUp t(0).dwType = mlchInputKeyboard t(1).dwType = mlchInputKeyboard t(2).dwType = mlchInputKeyboard t(3).dwType = mlchInputKeyboard ' Copy... apiCopyMemory t(0).xi(0), k(0), Len(k(0)) apiCopyMemory t(1).xi(0), k(1), Len(k(1)) apiCopyMemory t(2).xi(0), k(2), Len(k(2)) apiCopyMemory t(3).xi(0), k(3), Len(k(3)) ' Activate... apiSetForegroundWindow Handle ' Message... apiSendInput 4, t(0), Len(t(0)) ' Activate... apiSetForegroundWindow Application.hwnd ' Return... mlfpApiSendCreateDocument = Err.LastDllError End Function

The key sequence we should use is "Control key down", "N key down", "N key up" and "Control key up". This is done by populating the four array items of the variable "k". Then, we have to assign the event type to the variable "t" (corresponding to the "Input" data structure from the Windows API) and perform a copy operation of the items from "k" to the appropriate elements of "t". Then the targeted Excel instance should be put to the foreground and the key sequence sent to that window. Finally, the Excel instance which is executing the code is reactivated. That's all. This works fine on my computer, a Windows 7 System and having Office 2003, 2007 and 2010 installed.

The last thing I have done is to try if the code also works with Word instances. Yes, it does. If you would like to instantiate VBA Word Application objects, you may use "OpusApp" as class name instead of "XLMAIN". However, on my first runs, I got some unnamed Word windows and therefore I added some code for filtering out these entries.

Conclusion

In summary, I can say, at least on my computer, it is possible to create a VBA Application object from every opened Excel or Word instance. Finally an important note: the sample application is provided as is and no warranty or support is given. And the sample application is an experiment, so please do not expect that all things are also working on your system. You may also not have important documents open when experimenting with Windows API functions. And please consider that the incorrect use of Windows API function can lead to unexpected results, for example blue screens. The sample application will not work on Windows 64 Bit systems.

• Download of the sample application

• Microsoft Support, GetObject and CreateObject behavior, English
• Wikipedia, Windows Programming/Handles and Data Types, English
• MSDN, Windows Reference, English
• MSDN, EnumChildProc Callback Function, English
• MSDN, AccessibleObjectFromWindow, English
• MSDN, Component Object Model, English
• MSDN, Windows API, SendInput Function, English
• MSDN, Windows API, INPUT Structure, English
• MSDN, Windows API, KEYBDINPUT Structure, English
• Blog from Andrew Whitechapel, English
• Wikipedia, Component Object Model, English
• AllApi, SendInput Function including a sample, English

This article has also been published in two parts on the German Version of the Excel Ticker blog, links to Part 1 and Part 2.