prompt user to specify a directory

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

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



"mini" wrote:

> 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

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

"AA2e72E" wrote:

> 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
>
>
>
> "mini" wrote:
>
> > 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

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



"mini" wrote:

> 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

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



"JMB" wrote:

> 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
>
>
>
> "mini" wrote:
>
> > 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

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


"mini" wrote:

> Thanks for the reply. Could you please elaborate more on how to do it.
> What's API?
> Mini
>
> "AA2e72E" wrote:
>
> > 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
> >
> >
> >
> > "mini" wrote:
> >
> > > 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