I have a form with a text box for the user to input a directory path. They
would like to have a browse button that inserts the path. If they were
looking for a file this would be easy. But it is a folder that I need. How
would this be implemented? We are using XP for the operating system. The
Excels are a mix of 97 and XP, though if it were much easier under Excel XP
I could wait a few months.

Thanks, Don <donwiss at panix.com.

Don,

This is something I used a while back. I can't remember the original author
I'm afraid. It uses a couple of API calls to return the result, and as is
quite typical with these calls, a typed variable.

Option Explicit
Public Type BROWSEINFO
hOwner As Long
pidlRoot As Long
pszDisplayName As String
lpszTitle As String
ulFlags As Long
lpfn As Long
lParam As Long
iImage As Long
End Type

'32-bit API declarations
Declare Function SHGetPathFromIDList Lib "shell32.dll" _
Alias "SHGetPathFromIDListA" (ByVal pidl As Long, ByVal pszPath As
String) As Long

Declare Function SHBrowseForFolder Lib "shell32.dll" _
Alias "SHBrowseForFolderA" (lpBrowseInfo As BROWSEINFO) As Long

Sub Test()
MsgBox GetDirectory("Please select the directory where you would like to
save files")
End Sub

Function GetDirectory(Optional msg) As String
Dim bInfo As BROWSEINFO
Dim path As String
Dim r As Long, x As Long, pos As Integer

' Root folder = Desktop
bInfo.pidlRoot = 0&

' Title in the dialog
If IsMissing(msg) Then
bInfo.lpszTitle = "Select a folder."
Else
bInfo.lpszTitle = msg
End If

' Type of directory to return
bInfo.ulFlags = &H1
' Display the dialog
x = SHBrowseForFolder(bInfo)
' Parse the result
path = Space$(512)
r = SHGetPathFromIDList(ByVal x, ByVal path)
If r Then
pos = InStr(path, Chr$(0))
GetDirectory = Left(path, pos - 1)
Else
GetDirectory = ""
End If
End Function

HTH,

Robin Hammond
www.enhanceddatasystems.com


"Don Wiss" wrote in message
...
I have a form with a text box for the user to input a directory path. They
would like to have a browse button that inserts the path. If they were
looking for a file this would be easy. But it is a folder that I need. How
would this be implemented? We are using XP for the operating system. The
Excels are a mix of 97 and XP, though if it were much easier under Excel
XP
I could wait a few months.

Thanks, Don <donwiss at panix.com.

On Wed, 28 Apr 2004, Robin Hammond wrote:

This is something I used a while back. I can't remember the original author

' Root folder = Desktop
bInfo.pidlRoot = 0&

Thanks Robin. It works fine. A nice enhancement though would be to control
the root folder it opens with. Changing that line and giving it a string
gets a type mismatch. Anyone know how to change it from Desktop to a
specified folder as the root?

Don <donwiss at panix.com.

Don,

Reasonable question. I found a couple of answers in the groups from 1997
involving use of undocumented api calls in shell32, which are probably very
unreliable, then got carried away and went looking for something else. It
seems that there is a solution in the common controls replacement project
he

http://ccrp.mvps.org/ and look for the browse dialog server control.

Hopefully that will give you more control.

Robin Hammond
www.enhanceddatasystems.com


"Don Wiss" wrote in message
...
On Wed, 28 Apr 2004, Robin Hammond
wrote:

This is something I used a while back. I can't remember the original
author

' Root folder = Desktop
bInfo.pidlRoot = 0&

Thanks Robin. It works fine. A nice enhancement though would be to control
the root folder it opens with. Changing that line and giving it a string
gets a type mismatch. Anyone know how to change it from Desktop to a
specified folder as the root?

Don <donwiss at panix.com.

Hi Don,

Don Wiss wrote:
Thanks Robin. It works fine. A nice enhancement though would be to
control the root folder it opens with. Changing that line and giving
it a string gets a type mismatch. Anyone know how to change it from
Desktop to a specified folder as the root?

You can go to Stephen Bullen's page

http://bmsltd.ie/MVP/Default.htm

and look for BrowseForFolder.zip under Jim Rech. That one allows you to
specify an initial directory.

Alternatively, you can automate the Shell interface to do this:

Sub test()
Dim sh As Object
Dim fol As Object
Dim fi As Object

Set sh = CreateObject("Shell.Application")

Set fol = sh.BrowseForFolder(0, _
"Select Folder", 0, "C:\")

If Not fol Is Nothing Then
Set fi = fol.ParentFolder.ParseName(fol.Title)
If Not fi Is Nothing Then
MsgBox fi.Path
Else
MsgBox fol.Title
End If
Set fol = Nothing
Else
MsgBox "no folder selected"
End If
End Sub

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

And when all your users are upgraded to xl2002, you can use this new featu

Application.FileDialog(msoFileDialogFolderPicker). Show

(Look at VBA's help for lots more details.)

Won't work on xl97/xl2k at all!



Don Wiss wrote:

I have a form with a text box for the user to input a directory path. They
would like to have a browse button that inserts the path. If they were
looking for a file this would be easy. But it is a folder that I need. How
would this be implemented? We are using XP for the operating system. The
Excels are a mix of 97 and XP, though if it were much easier under Excel XP
I could wait a few months.

Thanks, Don <donwiss at panix.com.

--

Dave Peterson

Thanks Robin. It works fine. A nice enhancement though would be to control
the root folder it opens with. Changing that line and giving it a string
gets a type mismatch. Anyone know how to change it from Desktop to a
specified folder as the root?

Don <donwiss at panix.com.

You could use the Shell32.dll
See here

http://www.xcelfiles.com/Shell32_00.html

On Thu, 29 Apr 2004 20:52:08 -0500, Dave Peterson wrote:

And when all your users are upgraded to xl2002, you can use this new featu

Application.FileDialog(msoFileDialogFolderPicker) .Show

(Look at VBA's help for lots more details.)

I tried it. I prefer the simpler folder browser that appears in the other
posted examples. I already implemented the first one posted. While it would
be nice to control the opening folder, the other examples where much more
complicated. I prefer to keep things simple. And the use for this is fairly
low, so not worth the trouble going beyond having it start from the
desktop.

Don <donwiss at panix.com.