Dialog box for user to select a folder

[Guest]

I would like to include the GetOpenFilename method in a dialog box, in order
for the user to select a folder.

1. Can the method indeed be used to select a folder name?
2. Can I specify filtered browsing, that will display only folders?

Thanks.

 

[Bob Phillips]

XL2002 has a browse folder dialog.


With Application.FileDialog(msoFileDialogFolderPicker)
.Show


MsgBox .SelectedItems(1)


End With


Look up FileDialog in the VBA help


--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

 

[Jim Rech]

And if you want to use the Windows 'Browse for Folder':

http://www.oaltd.co.uk/DLCount/DLCount.asp?file=BrowseForFolder.zip

Quite a bit more complicated however.

--
Jim
|I would like to include the GetOpenFilename method in a dialog box, in
order
| for the user to select a folder.
|
| 1. Can the method indeed be used to select a folder name?
| 2. Can I specify filtered browsing, that will display only folders?
|
| Thanks.

 

[Guest]

Bob,

Thanks for the help.

I have Excel 2000. Is there any way it can be done in Excel 2000?

 

[Bob Phillips]

The pre XL2002 way is


Private Declare Function SHGetPathFromIDList Lib "shell32.dll" _
Alias "SHGetPathFromIDListA" _
(ByVal pidl As Long, _
ByVal pszPath As String) As Long


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


Private 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


'-------------------------------------------------------------
Function GetFolder(Optional ByVal Name As String = _
"Select a folder.") As String
'-------------------------------------------------------------
Dim bInfo As BROWSEINFO
Dim path As String
Dim oDialog As Long


bInfo.pidlRoot = 0& 'Root folder = Desktop


bInfo.lpszTitle = Name


bInfo.ulFlags = &H1 'Type of directory to Return
oDialog = SHBrowseForFolder(bInfo) 'display the dialog


'Parse the result
path = Space$(512)


GetFolder = ""
If SHGetPathFromIDList(ByVal oDialog, ByVal path) Then
GetFolder = Left(path, InStr(path, Chr$(0)) - 1)
End If


End Function


--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

 

[Guest]

Thanks to all for your answers, but whew! I may just use GetOpenFilename,
asking the user to select any file in the folder, then parsing the path
string for the folder. Glad that later versions of Excel simplify the task.

 

[Bob Phillips]

But if you tuck it away in a separate module, you can then forget about it.
That is effectively what all builtin functions do for you.

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)