Get Folder Name

[Gary''s Student]

I can use the file open dialog to select a file:

Sub WhichOne()
MsgBox (Application.GetOpenFilename)
End Sub

How can I select a folder??
--
Gary''s Student - gsnu200780

[Dave Peterson]

Jim Rech has a BrowseForFolder routine at:
http://www.oaltd.co.uk/MVP/Default.htm
(look for BrowseForFolder)

John Walkenbach has one at:
http://j-walk.com/ss/excel/tips/tip29.htm

If you and all your users are running xl2002+, take a look at VBA's help for:
application.filedialog(msoFileDialogFolderPicker)

--

Dave Peterson

[Gary''s Student]

Thanks Dave.

--
Gary''s Student - gsnu200780

[Steve Yandl]

If your users are pre-xl2002 and you don't want to go the Windows API route,
here is one more option.

_______________________________

Sub FetchAfolderpath()
Const MY_COMPUTER = &H11&
Const WINDOW_HANDLE = 0
Const OPTIONS = 0

Set objShell = CreateObject("Shell.Application")
Set objFolder = objShell.Namespace(MY_COMPUTER)
Set objFolderItem = objFolder.Self
strPath = objFolderItem.Path

Set objShell = CreateObject("Shell.Application")
Set objFolder = objShell.BrowseForFolder _
(WINDOW_HANDLE, "Select a folder:", OPTIONS, strPath)

If objFolder Is Nothing Then
Exit Sub
End If

Set objFolderItem = objFolder.Self
objPath = objFolderItem.Path

MsgBox objPath

End Sub

______________________________

Steve Yandl

"Gary''s Student" <GarysS...@discussions.microsoft.com> wrote in message
news:A7B53743-4DB2-4FEC...@microsoft.com...

[Steve Yandl]

Gary's Student,

My copy and paste was a bit too quick.

If you test the routine above, you can drop the duplicate line (the second
time it appears)

Set objShell = CreateObject("Shell.Application")

Also, I pulled this from a vbs file of mine. In a script, it isn't
important to set the objects to nothing at the end of the sub but in VBA you
should include a line at the end of the sub that reads:
Set objShell = Nothing

Steve

"Steve Yandl" <syandl...@comcast.net> wrote in message
news:dcKdnYEog_fmr5fV...@comcast.com...

[Gary''s Student]

Thank you very much Steve!!

--
Gary''s Student - gsnu200780

[Steve Yandl]

You're welcome.

If you want to use a top level folder other that the MyComputer special
folder, this reference will provide the available constants.
http://www.microsoft.com/technet/scriptcenter/guide/sas_fil_higv.mspx?mfr=true

Steve

"Gary''s Student" <GarysS...@discussions.microsoft.com> wrote in message

news:58631FB7-1F69-466B...@microsoft.com...