Browse For Folder

Demonstration of the Windows Shell Browse for Folder function for Excel 97 and Excel 2000. (Revision 1) By Jim Rech ([email protected])

Show status text Show files ✘

Allow direct entry * Validate direct entry *

✘

Use "New Style" dialog **

* Requires Shell32.dll version 4.71 and above ** Requires Shell32.dll version 5.0 and above (Windows 2000 amd ME)

✘

Center dialog on screen

About the Browse for Folder Demo The Windows Shell Browse For Folder function provides a means to have the user select a folder. Excel/VBA does not have this functionality built in and Excel developers frequently ask how to do this. Historically the answer has been to use the GetOpenFilename method. This is not ideal however because it requires the user to select a file within the desired folder, a confusing and awkward extra step. Excel developers have become pretty comfortable making calls to the Windows API. It is often the best or only way to get certain kinds of information, such as the user's hardware configuration, or to do certain things, like writing a Registry entry. But there is a class of Windows functions that has been entirely off-limits to Excel developers: callback functions. Callback functions are different than other functions in that they do not directly return useful information or perform actions. As the name implies Windows callback functions call subroutines in your project back. When they do so it is to deliver useful information about the Windows environment. One example is the EnumFonts function. It calls your callback function repeatedly, each time with information about an installed font. Another example is the SetTimer function. Similar to Excel's Application.OnTime method it calls your callback function after a time interval has elapsed. The Browse for Folder function has aspects of a regular function and of a callback function. Browse for Folder can be called without taking advantage of callbacks. In that case the folder browser dialog box will pop up and when the user clicks OK the function will return the selected folder to you as a string. But in order to interact with the dialog while it is displayed you must establish a callback function. A callback function enables you to set the initial folder, to update a status message and to validate the user's selection. Setting the initial folder is the main reason developers want to establish a callback function when using the Windows BrowseForFolder API. Without this the dialog always opens at the root folder. Ideally you would want to open the dialog with the user's previous selection already set. For example, if you are asking the user to choose a new data file directory it might be helpful to have the dialog open preset to the current data file directory. The only problem is that Excel 97 has no way to tell Windows what the callback function is, at least not officially. Windows wants the "address" of the callback function. A string like "MyCallbackFunction" has no meaning to Windows; it needs a location in memory. In Visual Basic 5.0 the AddressOf operator was added to answer this need. Excel 2000 has it too but Excel 97 does not. But all is not lost. Ken Getz and Michael Kaplan, worked out how to achieve the same result by directly calling into the dlls that Excel uses. It was published their article "Call Me Back" in the May 1998 issue of Microsoft Office & Visual Basic for Applications Developer magazine. This demo uses the Getz/Kaplan method when run under Excel 97 and the AddressOf operator under Excel 2000. This demo shows almost everything the Browse for Folder function can do as of version 5 of Shell32.dll. The additional functionality can be enabled with the checkboxes on the Demo worksheet. As you can see Browse for Folder can be used to get a file if you want. The status box option is also worthwhile I think. But the direct entry seems kind of half-baked to me. I suppose this could be used (with the

validity checking option on) to allow users to indicate they want to create a new folder. But it's an awkward interface for this at best in my opinion.

What's New in Revision 1 1. Support for the "new" style browse dialog. Windows 2000 and Windows ME install version 5 of Shell32.dll that provides a new style dialog. Per MS it " provides the user with a larger dialog box that can be resized. It has several new capabilities including: drag and drop capability within the dialog box, reordering, context menus, new folders, delete, and other context menu commands". 2. An option to center the browse dialog on screen. 3. Better memory handling. Repeatedly calling the browse dialog in the first version resulted in some weird results, particularly in conjunction with other macros.