This month, Ken Getz takes on but one question: allowing a user to choose a directory from a list of directories. He supplies the answer for both formats: Access 2.0 and Access 95/97. The 16-bit demonstration database is QA970416.MDB, and the 32-bit version is QA970432.MDB. The 32-bit database is in Access 95 format, but can easily be converted for Access 97.
As part of my application, I need to be able to allow users to select a path in which to save files. Currently, they have to type the full path, and then I verify that the path is valid. They all complain that it’s too difficult to remember the paths on their computers, and I agree. I tried using the Windows common File Open dialog box, but this doesn’t have an option to allow me to show only paths (and not files), as far as I can tell. Please help! This is driving me nuts.
You’re right — there’s nothing you can do to make the common dialog box show you only drives and paths. You could, of course, write code using the Dir function to fill a list box with all the directories, but it doesn’t help if you want to allow users to select drives as well. In addition, this method isn’t as fast as you might like.
It turns out that the Windows API can again come to your rescue. If you read the February 1997 edition of this column (“Sorting Arrays of Strings”), you’ll see that I previously used the Windows API CreateWindowEx function to create a list box that will sort items for you. You can also use the same technique to create a list box that fills itself with directory, drive, or file information. I’ll use that technique here.
In case you missed the previous column, the CreateWindowEx API function allows you to create a control of any built-in Windows type. You can make it visible or invisible, set its parent, fill it with data, and retrieve data from it. By specifying the LBS_SORT flag when you create a list box, you can cause it to sort its data alphabetically. Once you’ve created the list box, you can send messages to it (using the SendMessage API function) to add or retrieve specific items.
In addition to the techniques shown in that issue, you can use the SendMessage function to cause the list box to fill itself with a list of drives, directories, and files. Because you’ve created the list box with the LBS_SORT flag, it’ll automatically sort the items for you as well. Can you just place this newly created list box on an Access form and use it to allow users to select a drive or directory? Not quite. Although you could show the list box on a form (simply by setting its parent to be the form’s hWnd property when you create the list box using CreateWindowEx), there’s no reasonable way to react to any user events associated with the list box. That is, it would just show you the list of items, but it wouldn’t be able to do anything once you’d clicked on a specific item.
If you want to use a list box created with CreateWindowEx, you’ll want to use it invisibly, and pull all its items into a local array. Once you have an array full of drive and path names, you can use Access’ list-filling callback function mechanism to fill a real Access list box with the items the hidden list box found for you. For more information on using list-filling callback functions, see my column in the December 1995 issue (“How to Fill Lists, Sleep on the Job, and Tell When a Subform is Really a Form”). To demonstrate this functionality, try frmTestGetPath in the sample database. This form, shown in Figure 1, pops up frmGetPath, waits for you to choose a path, and then displays the selected location. The “chooser” form, frmGetPath, starts out at the current location and allows you to navigate to any other drive and directory.
The example includes several technologies that I’m not going to cover in detail in this answer — they’ve been covered previously, and aren’t the point of this answer. For example, as mentioned earlier, this solution uses a list-filling callback function to get the items from an array into the list box on frmGetPath. It also uses a common technique for displaying pop-up forms and retrieving information: A wrapper function, GetPath, in basFillDirList, opens frmGetPath as a pop-up form and waits for you to either close the form (using the Cancel button) or hide the form (using the OK button). This technique makes it possible to open a form, retrieve some information from a user, and gather that information once the user has finished with the form. [This technique was discussed in an article by Mike Gilbert in the February 1995 issue. — Ed.] In addition, the 32-bit version of the sample provides a public property of frmGetPath, the Path property, that makes it easy to retrieve the selected path. Because Access 2.0 doesn’t support user-defined properties on forms, the 16-bit version of the sample database requires its GetPath function to investigate the value of the txtFullPath text box on frmGetPath directly. Finally, to fully understand this solution, you’ll need to investigate the CreateWindowEx API function, discussed in my February 1997 column. If you’re missing that issue, you can also go back to the same source I did for this information: I used the MSDN CD to look up how Windows creates windows, and used the WIN32API.TXT file that comes with Visual Basic (it’s available free from a number of sources and is included in the accompanying Download file).
The code examples and screen shots in this answer are taken from the 32-bit version of the sample database. The 16-bit solution is quite similar, but details in the code were changed to accommodate the different features of the product. The explanation presented here still applies to the 16-bit version, but the code won’t quite match what you see here.
The GetDirArray function, in basFillDirList, is the key to this solution. This function, shown in Listing 1, allows you to pass in an array to be filled in and a starting path (optional in Access 95/97 where it defaults to the current directory). Once it has checked its parameters to make sure they make sense, it starts by creating the list box:
hwnd = CreateWindowEx(0, "LISTBOX", _ "SortingListBox", LBS_SORT, _ CW_USEDEFAULT, CW_USEDEFAULT, _ CW_USEDEFAULT, CW_USEDEFAULT, _ 0, 0, 0, 0)
For information on all the various parameters, you’ll need to consult a Windows API reference, but the only one of concern to you is the LBS_SORT flag used in the fourth parameter. This indicates to Windows that this list box is to alphabetically sort all its entries. Because you’ll be filling this list box with directory names, it’ll sort those names for you. Because this list box is invisible, you don’t care about its parent or its location. (These are some of the other settings you can control using the parameters for CreateWindowEx.)
Listing 1. The GetDirArray function.
Function GetDirArray(avarItems As Variant, _ Optional ByVal varPath As Variant) As Integer Dim hwnd As Long Dim strBuff As String Dim intLen As Integer Dim intI As Integer Dim intCount As Integer On Error GoTo HandleErr If Not IsArray(avarItems) Then GoTo ExitHere End If hwnd = CreateWindowEx(0, "LISTBOX", _ "SortingListBox", LBS_SORT, _ CW_USEDEFAULT, CW_USEDEFAULT, _ CW_USEDEFAULT, CW_USEDEFAULT, _ 0, 0, 0, 0) ' Can only continue if CreateWindowEx succeeded. If hwnd <> 0 Then ' Add the directory and drive items ' to the list box. intCount = SendStringMessage(hwnd, LB_DIR, _ DDL_DIRECTORY Or DDL_DRIVES Or DDL_EXCLUSIVE, _ FixPath(varPath) & "*.*") If intCount > 0 Then ReDim avarItems(1 To intCount + 1) strBuff = Space(conMaxWidth) For intI = 1 To intCount + 1 ' Retrieve each item from the list box ' and add it to the array. intLen = SendStringMessage(hwnd, _ LB_GETTEXT, intI - 1, strBuff) avarItems(intI) = Left$(strBuff, intLen) Next intI End If End If ExitHere: GetDirArray = intCount + 1 ' Destroy the list box if necessary. If hwnd <> 0 Then Call DestroyWindow(hwnd) Exit Function HandleErr: Select Case Err.Number Case Else MsgBox "Error: " & Err.Description & _ " (" & Err.Number & ")" End Select Resume ExitHere End Function
CreateWindowEx returns a window handle (a unique identifier) for the newly created control, and you’ll use that window handle with the SendMessage API function to tell the control to show directories and drives, but not files. If the window handle is 0, the only disallowed value, the code just exits. If it’s a legal value, the code continues and calls a version of SendMessage that has been aliased to allow it to send a string in its final parameter, named SendStringMessage:
intCount = SendStringMessage(hwnd, LB_DIR, _ DDL_DIRECTORY Or DDL_DRIVES Or DDL_EXCLUSIVE, _ FixPath(varPath) & "*.*")
In this line of code, the LB_DIR constant tells the list box control to fill itself with files, drives, and directories. The next parameter indicates which of these items the list box should contain. The DDL_DIRECTORY constant tells it to load directory names, DDL_DRIVES tells it to include drives, and the DDL_EXCLUSIVE tells it to load only the items previously noted. Without this final constant, the list box would also have loaded its default set of values: all the files in the selected directory. The final parameter indicates which path and set of child directories to load. (The FixPath function ensures that its parameter ends with a trailing “\” character. This function is called throughout this sample. In this case, it’s using the varPath value passed as a parameter to the function, indicating the directory in which to search.) The call to SendStringMessage returns the index of the final item it added to the list box. Because the list box item indexes are zero-based, the value is one less than the number of items in the list box.
The next step is to copy the items out of the filled list box into a local array. This makes it possible for the GetDirArray function to return an array filled with the items in the list box and to destroy the list box once it’s done. Certainly no other portion of your application should need to know how to retrieve the items from the hidden list box, so it’s best to isolate that technology in this routine. If the previous call to SendStringMessage didn’t place any items in the list box, intCount will be 0 and the code stops there. If intCount is greater than 0, however, it executes the following code:
ReDim avarItems(1 To intCount + 1) strBuff = Space(conMaxWidth) For intI = 1 To intCount + 1 ' Retrieve each item from the list box ' and add it to the array. intLen = SendStringMessage(hwnd, _ LB_GETTEXT, intI - 1, strBuff) avarItems(intI) = Left(strBuff, intLen) Next intI
This code resizes the array passed into GetDirArray to be large enough to contain all the items in the list box, and then “puffs up” the string buffer so it can hold up to 260 characters. To retrieve the items from the list box, the code then calls the SendStringMessage function once again, using the LB_GETTEXT message to cause the list box to return its items, one at a time. By specifying the index of the item you want to retrieve in the function call, SendStringMessage will return each item. All that’s left, then, is to use the Left$ function to extract just the portion of the text before the first null character.
Once you’ve executed those steps, you have an array full of all the drives on the machine, and all the directories under the selected directory. Just so you know, the list box returns directory names surrounded by square brackets (such as “[ADTC]”) and drive names in bracket/hyphen pairs (such as “[-c-]”). It’s up to your code to extract the portion you need, and this sample includes the FixEntry function in frmGetPath’s module to extract just the part you need. More on that later.
The sample form, frmGetPath, centers all its activity around the text box that displays the current path, txtFullPath. The form’s Load event procedure starts out by storing away the current path (so it can restore it when the form closes), and checking to see if code that loaded the form passed in a value in the OpenArgs parameter. If so, it uses that as the initial directory. If not, it uses the current directory. Either way, it sets the value of txtFullPath and then sets the RowSourceType property of the main list box on the form. By setting the property value, the form avoids problems that would otherwise have occurred when the function called by the list box to fill itself tried to retrieve a value from txtFullPath and found none there; setting the property value also causes it to requery the list box once there’s a value in txtFullPath. Here’s the Load event procedure:
Private Sub Form_Open(Cancel As Integer) Dim strTemp As String ' Store away the current path. mstrCurDir = CurDir() ' If the caller passed in some info, use it ' as the starting path. Otherwise, use the ' current path. If Len(Me.OpenArgs) > 0 Then strTemp = Me.OpenArgs Else strTemp = CurDir() End If Me!txtFullPath = strTemp Me!lstPath.RowSourceType = "FillDirList" End Sub
The FillDirList function, used by the list box on frmGetPath to provide its list of values, is a standard list-filling callback function. It’ll be called by Access every time the program needs to get information about or provide a value for the list box. When you first set the RowSourceType property for the list box (in the form’s Load event), Access will come through this procedure with intCode set to acLBInitialize, calling the GetDirArray function discussed earlier. This function returns the number of items it placed into the array passed to it, and FillDirList stores that value away for later, in the intCount variable. Here’s FillDirList:
Function FillDirList(ctl As Control, lngID As Long, _ lngRow As Long, lngCol As Long, intCode As Integer) _ As Variant Static avarItems() As Variant Static intCount As Integer Select Case intCode Case acLBInitialize intCount = GetDirArray(avarItems, _ Me!txtFullPath) FillDirList = (intCount > 0) Case acLBOpen FillDirList = Timer Case acLBGetRowCount FillDirList = intCount Case acLBGetValue FillDirList = avarItems(lngRow + 1) Case acLBEnd Erase avarItems FillDirList = 0 End Select End Function
The other interesting chunk of code in FillDirList occurs in the acLBGetValue case. Access sends the acLBGetValue code when it needs to retrieve an item to display, and in this case it gets the item it needs directly from the array you filled in the acLBInitialize case. This explains why the array must be declared using the Static keyword: otherwise, it wouldn’t contain any data when Access tried to get the values to display.
The frmGetPath form contains two other interesting procedures: FixEntry and the double-click event procedure for the list box, lstPath_dblClick. The first, FixEntry, takes the selected value from the list box and, along with the current path, fills txtFullPath with the selected new path. If the selected value was a drive letter in the form “[-a-]”, the code pulls out the drive letter, tacks on a colon (:), and exits. FixEntry is shown here:
Private Function FixEntry(strCurPath As String, _ strNewPath As String) As String ' strNewPath comes in as either a path ' [NewPath] ' or as a drive ' [-d-] ' or as a file without a leading "[". ' If this code gets a drive, go to it. ' If it gets a path, go to that path. If Left$(strNewPath, 2) = "[-" Then ' We got a drive. FixEntry = Mid(strNewPath, 3, 1) & ":" ElseIf Left$(strNewPath, 1) = "[" Then FixEntry = FixPath(strCurPath) & Mid(strNewPath, _ 2, Len(strNewPath) - 2) Else FixEntry = FixPath(strCurPath) & strNewPath End If End Function
If the selected value was a path in the form “[newpath]”, FixEntry pulls out the directory name, tacks it onto the end of the current path, and returns that. If, for example, the current directory is this:
and the user selects “..”, this will be the return value from FixEntry:
The problem, then, is that you can end up with weird current paths such as this because the list box uses the “..” to allow you to move up a directory level:
Although this is a perfectly legal path, it looks odd. To resolve that path back to the correct, normalized display, the code uses the GetFullName function, found in basFillDirList and shown here:
Function GetFullName(strPath As String) As String ' Given a relative path name, convert it to ' an absolute path. Dim intLen As Integer Dim strBuffer As String strBuffer = Space(conMaxWidth) intLen = GetFullPathName( _ strPath, conMaxWidth, strBuffer, "") GetFullName = Left(strBuffer, intLen) End Function
The lstPath_DlbClick event procedure has three jobs:GetFullName uses the GetFullPathName API function to retrieve a normalized version of any path you send it. This API function takes into account the current directory if necessary, and returns the path you send it, but with a drive letter, and no “..” or “.” values in the path. Using the GetFullName function guarantees that the display of the path is always “clean.” (The 16-bit example can’t call the GetFullPathName function — it exists only in 32-bit Windows — so it must use a slower, “brute-force” technique. It simply stores the current directory, changes to the new location, and then uses the CurDir function to do the normalization. Finally, it switches back to the original location. Slow, but effective.)
- Fill in the value of txtFullPath.
- Change the current location to the selected drive and path.
- Requery the list box.
There’s nothing unusual about the code, but it has to happen in order for the list box to be able to navigate correctly:
Private Sub lstPath_DblClick(Cancel As Integer) ' Fix up the name on the current path and ' the new path (or drive). ' Then requery the list box, causing it to be ' refilled with the current path/drive info. Me!txtFullPath = _ GetFullName(FixEntry(Me!txtFullPath, Me!lstPath)) ChDrive Left(Me!txtFullPath, 1) ChDir Me!txtFullPath Me!lstPath.Requery End Sub
So that’s it! This example uses the CreateWindowEx function to create a list box every time you move to a new location and fills the list box with the local directories and all the drives. It then copies the items from the hidden list box into an array for later use. The pop-up form’s list box takes the items from the array and displays them. Once you make a choice by double-clicking on the list box, it moves to the new location and starts all over again.
Windows 95 issues
The 32-bit version of this solution works beautifully under Windows NT. Unfortunately, I discovered a problem running it under Windows 95: long paths are not handled properly. In fact, if the starting directory contains a long path, you might be unable to navigate correctly on that drive. I made several attempts to get around these issues, but was unable to find a fix. Thus, if you plan to run the 32-bit version under Windows 95, you might wish to use an alternate solution.
There are other alternatives to solving this problem, of course. As I mentioned earlier, you can perform all this work using the Dir function (not in Access 2.0, however; this won’t work with Dir there). But that’s slow, and requires more code. Another alternative is to use the same tools the Access Setup Wizard uses. The Setup Wizard uses the SWU2016 (or SWU7032) DLL, and you can call functions in that DLL as well. Microsoft Access MVP Steve Thompson worked out the 16-bit version, and he and I collaborated on the 32-bit version (see SDIR16.ZIP and SDIR32.ZIP, which are included the accompanying Download file). This method still requires a bit of code (which is worked out for you in the examples), but to take advantage of these packaged solutions you must own a copy of the Access Developer’s Toolkit that corresponds to your version of Access. Because this technique depends on a DLL that’s only shipped with the Developer’s Toolkit, you won’t be able to use SDIR16 or SDIR32 unless you own a copy.
Whichever technique you use, you should now be able to present users with a list of directories and drives, allow them to navigate to the path they want to use, and return that path back to your application. This is the second solution I’ve come up with in the past few months that uses the CreateWindowEx function to create a standard Windows control to use as a crutch to work around an Access limitation. I wonder how many more uses there are for this technique?
SideBar – Directories and Files Keyword Summary
|Change directory or folder.||ChDir|
|Change the drive.||ChDrive|
|Copy a file.||FileCopy|
|Make directory or folder.||MkDir|
|Remove directory or folder.||RmDir|
|Rename a file, directory, or folder.||Name|
|Return current path.||CurDir|
|Return file date/time stamp.||FileDateTime|
|Return file, directory, label attributes.||GetAttr|
|Return file length.||FileLen|
|Return file name or volume label.||Dir|
|Set attribute information for a file.||SetAttr|
SideBar 2 – Copying all Files in A Directory
This command can work, test the command by using Run on the Windows menu and typing cmd
Shell "xCopy C:\temp\*.* C:\temp3\*.*"