10+ of my favorite Windows API functions to use in Office applications

Office developers rely heavily on VBA. But even though it’s versatile and robust, it doesn’t do everything. Sometimes, a VBA solution is convoluted or difficult to implement. When that happens, turn to the Windows Application Programming Interface (API). You’ll find thousands of useful functions. Although Office developers should find the APIs in this article useful, they’re not just for Office. You can use them in most any Windows-based application. (All of these tips are specific to 32-bit systems.)

The VBA functions provided in this article aren’t real-world ready. They are simple calls to the API, so you can see how the pieces work together. However, a few could easily move into your code library as is. Execute the VBA procedures from the Immediate window, passing the necessary arguments, to see the results. Once you know how the VBA procedures call the API functions and what to expect in return, you can modify the procedures as necessary and use them in your own projects.

Note: This article is also available for download as a PDF, along with a module containing the code examples discussed here.

1: Sleep

The Sleep function suspends execution for a specified period. It places the running code into an inactive state for the number of milliseconds passed to the function. Simply declare the function and then call it as shown from a VBA procedure:

Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
Sleep milliseconds

2: GetUserName

If you need to know who’s logged into an Access database, use GetUserName. There are other ways to do this without calling an API, but this API is so simple, why would you bother writing your own code? GetUserName retrieves the name of the current system or the current user logged into the network. Declare it and call it, passing the appropriate information, as follows:

Private Declare Function GetUserName Lib "advapi32.dll" Alias _

"GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long

Function apicGetUserName() As String

  'Call to apiGetUserName returns current user.

  Dim lngResponse As Long

  Dim strUserName As String * 32

  lngResponse = GetUserName(strUserName, 32)

  apicGetUserName = Left(strUserName, InStr(strUserName, Chr$(0)) - 1)

End Function

3: GetComputerName

This next function, GetComputerName, is similar to GetUserName except it retrieves the system’s name. Declare it and call it, as follows:

Private Declare Function GetComputerName Lib "kernel32" Alias _

 "GetComputerNameA" (ByVal lpBuffer As String, nSize As Long) As Long

Function apicGetComputerName() As String

  'Call to apiGetUserName returns current user.

  Dim lngResponse As Long

  Dim strUserName As String * 32

  lngResponse = GetComputerName(strUserName, 32)

  apicGetComputerName = Left(strUserName, InStr(strUserName, Chr$(0)) - 1)

End Function

4: BringWindowToTop

This API function brings the specified window to the top. If the window is a top-level window, the function activates it. If the window is a child window, the function activates the associated top-level parent window. Simply pass the appropriate window handle. If the function fails, it returns 0; if it’s successful, it will return a nonzero value. Use the following declaration:

Private Declare Function BringWindowToTop Lib "user32" _

 (ByVal lngHWnd As Long) As Long

When calling the function, pass the window handle value as a Long variable.

5: FindWindow

BringWindowToTop requires a handle value. You’ll need FindWindow, another API, for that. This one can be a bit frustrating because it requires specialized information, and if you don’t get it just right, the function won’t work. Specifically, you need the window’s class or name, as shown in the simple call below. This function returns the handle for the Word document, December2010.docx.

Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" _

(ByVal lpClassName As String, ByVal lpWindowName As String) As Long

Function apicFindWindow(strClassName As String, strWindowName As String)

  'Get window handle.

  Dim lngWnd As Long

  apicFindWindow = FindWindow(strClassName, strWindowName)

End Function

The class names for the three main Office applications are as follows:

• Microsoft Access — OMain
• Microsoft Excel — XLMAIN
• Microsoft Word — OpusApp

The window name (lpWindowName) is usually the window’s caption; see the window’s title bar. If you’re lucky, passing the object in the following form will work:

object.Caption

For instance, you might try the following statement to return the handle of an Excel userform named ufrmEmployees:

FindWindow(vbNullString, ufrmEmployees.Caption)

Unfortunately, it doesn’t always work out that way. If the function returns 0, it didn’t work — a window handle is never 0. If you omit one of the parameters (both aren’t necessary), pass vbNullString.

If you have trouble getting the lpWindowName property, grab a copy of AutoIt. This tool returns the exact window name for open windows. Thanks to Stuart McLachlan of Lexacorp for recommending this neat tool!

6: FindExecutable

Have you ever needed to know what’s installed before running a specific Office application? FindExecutable can retrieve that information. This function requires the name of an existing data file and a working directory. The function returns the name and path of the application that Windows would launch if you double-clicked the data file. If the function fails, it returns a value of 32 or less. Declare it and use the following VBA function to call FindExecutable:

Private Declare Function FindExecutable Lib "shell32.dll" Alias _

 "FindExecutableA" (ByVal lpFile As String, ByVallpDirectory As String, _

 ByVal lpResult As String) As Long

Function apicFindExecutable(strDataFile As String, strDir As String) As String

  'Resturns exectuable for passed data file.

  Dim lgnApp As Long

  Dim strApp As String

  strApp = Space(260)

  lngApp = FindExecutable(strDataFile, strDir, strApp)

  If lngApp > 32 Then

    apicFindExecutable = strApp

  Else

    apicFindExecutable = "No matching application."

  End If

End Function

You must pass the entire path via strDataFile, not just the name of the data file. The function will return the path to the executable needed to run the data file.

7: GetActiveWindow

When working with more than one Office application, you’ll probably need to consider windows. Fortunately, there are plenty of API functions for working with them. GetActiveWindow retrieves the window handle for the currently active window — the new window you last clicked. If there is no active window associated with the thread, the return value is NULL. Declare it and call it as follows:

Private Declare Function GetActiveWindow Lib "user32" () As Long

Function apicGetActiveWindow()

  'Return window handle of active window.

  Dim lngWindow As Long

  apicGetActiveWindow = GetActiveWindow()

End Function

You can quickly learn whether a specific form is still the active window using the following expression if the host application supports a handle property (Access does):

GetActiveWindow() = | <> form.hWND

8: GetTempPath

GetTempPath returns the path to the system’s temporary folder. It requires two parameters: the length of a string to contain the pathname and the string itself. This function returns the length of the pathname measured in bytes or 0 if the function fails. Declare it and call it as follows:

Private Declare Function GetTempPath Lib "kernel32" Alias "GetTempPathA" _

 (ByVal nBufferLength As Long, ByVal lpBuffer As String) As Long

Public Function apicGetTempPath() As String

  'Returns path to system's temporary folder.

  Dim strPath As String * 512

  Dim lgnPath As Long

  lgnPath = GetTempPath(512, strPath)

  apicGetTempPath = Left(strPath, InStr(1, strPath, vbNullChar))

End Function

9: GetTempFileName

This function often works as a companion to GetTempPath. You might not use it often, but when you need it, you’ll find it handy. GetTempFileName creates a name for a temporary file. This function has four parameters: a string for the path for the file, a string prefix used to begin a unique filename, a unique number to construct the temporary name, and a string used to return the filename. Both the path and prefix strings are required and can’t be empty. The function returns the unique number used to create the file name or 0, if there’s an error. Declare it and call it as follows:

Declare Function GetTempFileName Lib "kernel32" Alias "GetTempFileNameA"
(ByVal lpszPath As String, ByVal lpPrefixString As String, ByVal wUnique As
Long, ByVal lpTempFileName As String) As Long

Public Function apicGetTempFileName(str As String) As String

  'Retrun a temporary file name.

  Dim strPath As String * 512

  Dim strName As String * 576

  Dim lngRet As Long

  lngRet = GetTempPath(512, strPath)

  If (lngRet > 0 And lngRet < 512) Then

     lngRet = GetTempFileName(strPath, str, 0, strName)

     If lngRet <> 0 Then

        apicGetTempFileName = Left(strName, _

           InStr(strName, vbNullChar))

     End If

  End If

End Function

The above function combines GetTempPath and GetTempFileName to return a temporary filename. Simply pass a prefix and the function generates a unique name using the prefix.

10: GetDesktopWindow

This function retrieves a handle to the desktop window, which covers the entire screen. All other windows are drawn on top of the desktop window. This is one of the easier functions to implement, as there are no parameters. You’ll seldom use it alone; rather, you’ll combine it with other API functions. For instance, you might combine it with others so you can temporarily drop files onto the desktop or enumerate through all the open windows on the desktop. Declare it and call it as follows:

Private Declare Function GetDesktopWindow Lib "user32" () As Long

Public Function apicGetDesktopWindow()

  'Retrieves the handle for the desktop.

  apicGetDesktopWindow = GetDesktopWindow

End Function

11: ShowWindow

After retrieving a window’s handle using FindWindow, you might want to manipulate the same window using ShowWindow. There are usually native methods you should use instead, but the capability exists — and sometimes, the native solutions just don’t provide what you want. Declare it and call it as follows:

Private Declare Function ShowWindow Lib "user32" _

 (ByVal hwnd As Long, ByVal nCmdSHow As Long) As Long

Function apicShowWindow(strClassName As String, strWindowName As String, lngState As Long)

  'Get window handle.

  Dim lngWnd As Long

  Dim intRet As Integer

  lngWnd = FindWindow(strClassName, strWindowName)

  apicShowWindow = ShowWindow(lngWnd, lngState)

End Function

The VBA function calls both FindWindow and ShowWindow. The third argument, lngState passes one of the constants shown in Table A.

Table A

I’d like to thank Stuart McLachlan, FPNGCS, and Jim Dettman, Access MVP for sharing their favorite APIs with me.