After Hours

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

When a VBA solution falls short of your needs, there's probably a Windows API function that can handle the job. Susan Harkins lists 10 handy APIs and explains how you can put them to work.

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

Constant

Integer Value

Explanation

SW_FORCEMINIMIZE 11 Minimizes a window.
SW_HIDE 0 Hides the window and activates another window.
SW_MAXIMIZE 3 Maximizes a window.
SW_MINIMIZE 6 Minimizes the specified window and activates the next top-level window.
SW_RESTORE 9 Activates and displays the window.
SW_SHOW 5 Activates the window.
SW_SHOWMAXIMIZED 3 Activates the window and displays it as a maximized window.
SW_SHOWMINIMIZED 2 Activates the window and displays it as a minimized window.
SW_SHOWMINNOACTIVE 7 Displays the window as a minimized window (without activating the window).
SW_SHOWNA 8 Displays the window in its current size and position (without activating the window).
SW_SHOWNOACTIVATE 4 Displays a window in its most recent size and position (without activating the window).
SW_SHOWNORMAL 1 Activates and displays a window.

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

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

8 comments
jy76
jy76

Cool post. But instead of GetUserName and GetComputerName, etc., why not just use the existing Environ function? The syntax is just Environ({envstring | number}), e.g. Environ("USERNAME") or Environ(31) (full help from VBA help file below) That returns a variety of different environment variables, dependent on the envstring / number provided. Various envstrings available on my system are shown below: ALLUSERSPROFILE APPDATA AVENGINE CLIENTNAME CommonProgramFiles COMPUTERNAME ComSpec FP_NO_HOST_CHECK HOMEDRIVE HOMEPATH INCLUDE INOCULAN LIB LOGONSERVER NUMBER_OF_PROCESSORS OS Path PATHEXT PROCESSOR_ARCHITECTURE PROCESSOR_IDENTIFIER PROCESSOR_LEVEL PROCESSOR_REVISION ProgramFiles SESSIONNAME SystemDrive SystemRoot TEMP TMP USERDOMAIN USERNAME USERPROFILE VS71COMNTOOLS WecVersionForRosebud.FF0 windir For code on how to find what is available, see this post: http://www.utteraccess.com/forum/Environ-Function-List-t693880.html The function returns many of the informational values from above but is easier to use, i think. I've only used it up to Office 2003, so i hope it is available in subsequent versions. Please see full help details below: Returns the String associated with an operating system environment variable. Not available on the Macintosh Syntax Environ({envstring | number}) The Environ function syntax has these named arguments: Part Description envstring Optional. String expression containing the name of an environment variable. number Optional. Numeric expression corresponding to the numeric order of the environment string in the environment-string table. The number argument can be any numeric expression, but is rounded to a whole number before it is evaluated. Remarks If envstring can't be found in the environment-string table, a zero-length string ("") is returned. Otherwise, Environ returns the text assigned to the specified envstring; that is, the text following the equal sign (=) in the environment-string table for that environment variable. If you specify number, the string occupying that numeric position in the environment-string table is returned. In this case, Environ returns all of the text, including envstring. If there is no environment string in the specified position, Environ returns a zero-length string.

Dave O
Dave O

I always relied on API calls to open and save files and folders, but Office 2007 seems to have fixed that with the FileDialog object. Maybe calling the API is becoming a thing of the past. And death to spammers!

pgit
pgit

How would you implement a call for pizza? Or Chinese food?

Tony Hopkinson
Tony Hopkinson

is the last one I engineered out, badly designed poorly implemented drivel, especially the ones you 'can't do any other way'. Cause as many problems as they solve in my experience. You bumped into a swallower yet. Call it, it throws up a dialogue box saying it didn't work and then returns success. Too much muppet code in there for my comfort.

Slayer_
Slayer_

Good times all around

chuckwolf
chuckwolf

The following function is one I include in any Excel application where I want some dialog box to appear (containing calendar if cell has date format, for example) but I don't want the dialog box to have focus (arrow keys continue cursor across columns of data, for example) Friend Sub XLFocus() Dim lExcelHwnd As Long On Error GoTo ErrCtrl lExcelHwnd = FindWindow("XLMAIN", Application.Caption) SetForegroundWindow lExcelHwnd Exit Sub ErrCtrl: Select Case Err.Number Case 0: ' no error Case Else: MsgBox Err.Description & vbCrLf & vbCrLf & Err.Number, vbCritical, "XL Projects (Set Focus to Excel)" End Select End Sub

toms45
toms45

use the telephone API and pass it the value of cell or home.

Editor's Picks