API Calls That You Can Use From Access

The Windows API is a huge collection of functions and sub procedures. There's no way that we can give you even a cursory overview in a single chapter. Instead, we've chosen to just demonstrate a few of the things that you can do with the API:

• Determine whether an application is running

• Retrieve the current username

• Get the executable for a data file

Determining Whether an Application Is Running

Sometimes you might like to know whether a particular application is already running on a user's system. For example, if you're using automation with Microsoft Excel, you can check to see whether Excel is already running before making automation calls. The FindWindow API call does this for you:

 Declare Function FindWindow Lib "user32" Alias _  "FindWindowA" (ByVal lpCLassName As String, _  ByVal lpWindowName As String) As Long Function IsAppRunning(strClassName As String, _  strwindowname As String) As Boolean   ' Determine whether an application is running   ' by class name or title bar text   If strClassName = "" Then     IsAppRunning = ( _      FindWindow(vbNullString, strwindowname) <> 0)   Else     IsAppRunning = ( _      FindWindow(strClassName, vbNullString) <> 0)   End If End Function 

FindWindow takes either a class name or a window caption and checks to see whether it can find a matching window. If it finds one, it returns the window handle of the window. Window handles are numbers that Windows itself uses to keep track of things. The important thing here is that a window handle is never zero.

Any window on the screen is identified to Windows by its class name. This is a name assigned by the developers of the application. For example, Excel's class name is xlMain, whereas Word's is OpusApp (Opus the Penguin was the mascot of the original Word development team). If you don't know the class name for an application, you can query by the title bar text instead. However, in this case, you must exactly match what's displayed on the screen. For example, with Excel open and a new default workbook loaded, you might get the following results:

 ?IsAppRunning("","Excel") False ?IsAppRunning("","Microsoft Excel - Book1") True 

FindWindow expects you to pass it a null string for the argument that you're not using. VBA doesn't use null strings, but it provides the vbNullString constant for precisely this reason.

Retrieving the Current Username

Suppose you're writing VBA code that needs to maintain an audit trail of which actions happened when. One important piece of information for such a procedure is the current username. Here again the Windows API can come to the rescue:

 Declare Function GetUserName Lib "advapi32.dll" Alias _  "GetUserNameA" (ByVal lpBuffer As String, _  nSize As Long) As Long Function GetCurrentUserName() As String   ' Retrieve the name of this user using   ' the Windows API   Dim strName As String   Dim lngChars As Long   Dim lngRet As Long   strName = Space(255)   lngChars = 255   lngRet = GetUserName(strName, lngChars - 1)   If lngChars > 0 Then     GetCurrentUserName = Left(strName, lngChars)   Else     GetCurrentUserName = "Unable to retrieve name."   End If End Function 

As you can see, the code for returning the username is almost identical to the code for returning the computer name. The only difference is in knowing which API call to use and which library it's found in. The overall pattern of setting up a string argument, passing its length, and determining the valid string on return remains the same.

TIP

You might be wondering why so many actual function names end in A. Actually, there are two versions of these functions. For example, Windows supplies GetUserNameA and GetUserNameW. The A version uses ANSI characters, whereas the W version uses Unicode characters. VBA works only with ANSI characters, so that's the version that you want.

Getting the Executable for a Data File

Does a user have Word installed, or might .doc files on their system be opened by WordPad? If they open an .html file, which Web browser is it displayed in? Both of these questions can be answered by using the FindExecutable API call, which tells you which application is associated with a particular data file.

 Declare Function FindExecutable Lib "shell32.dll" _  Alias "FindExecutableA" (ByVal lpFile As String, _  ByVal lpDirectory As String, ByVal lpResult As String) _  As Long Function GetMatchingApp(strDataFile As String, _  strDir As String) As String   ' Get the executable for the specified   ' data file in the specified location   Dim strApp As String   Dim lngRet As Long   strApp = Space(260)   lngRet = FindExecutable(strDataFile, strDir, strApp)   If lngRet > 32 Then     GetMatchingApp = strApp   Else     GetMatchingApp = "No matching application found."   End If End Function 

To use this function, you supply the name of a data file (which must actually exist) and the name of the working directory. The result is the name and path of the application that is launched when you go to Explorer and double-click on the data file:

 ?GetMatchingApp("c:\temp\foo.txt","c:\temp") C:\WINDOWS\system32\NOTEPAD.EXE 

Notice the magic number 260 that we used when setting up the string to hold the return value. This is the maximum length of a path and filename under Windows. As you can see, FindExecutable also breaks one of the previous rules; instead of returning zero for failure, it returns some number less than 32 for failure.