prompt user to specify a directory

[Guest]

Hi,
How do I prompt user to specify a directory? I don't want them to type in
the directory but instead they can browse around and select the correct path
just like in "Open File".
Thanks a lot,
Mini

 

[Guest]

Try the API call:

Declare Function SHBrowseForFolder Lib "shell32" (lpbi As BrowseInfo) As Long

BrowseInfo is a structure as follows:

Private Type BrowseInfo
hWndOwner As Long
pIDLRoot As Long
pszDisplayName As Long
lpszTitle As Long
ulFlags As Long
lpfnCallback As Long
lParam As Long
iImage As Long
End Type

 

[Guest]

Thanks for the reply. Could you please elaborate more on how to do it.
What's API?
Mini

 

[Guest]

Either one of these may be of some use to you.

Sub test()
Dim FName As String
Dim WkBk As Workbook

FName = Application.GetOpenFilename
If FName <> "False" Then _
Set WkBk = Workbooks.Open(Filename:=FName)
End Sub

Sub Test2()
Dim WkBk As Workbook

Application.Dialogs(xlDialogOpen).Show
If ActiveWorkbook.Name <> ThisWorkbook.Name Then _
Set WkBk = ActiveWorkbook
End Sub

 

[Guest]

I'm sorry, you said browse for folder, not browse for file. I use the API
call that has already been posted.

 

[Guest]

API is Application Programming Interface.

For SHBropwseForFolder, go to http://www.allapi.net/apilist/s.shtml and look
up the API.

An alternative way is:

Function aa()
Set sa = CreateObject("Shell.Application")
Set xx = sa.browseforfolder(0, "Select Folder", 0, 0)
aa = xx.Items.Item.Path
Set xx = Nothing
Set sa = Nothing
End Function