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??

 

[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)

 

[Gary''s Student]

Thanks Dave.
--
Gary''s Student - gsnu200780

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)

 

[Gary''s Student]

Thank you very much Steve!!
--
Gary''s Student - gsnu200780

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

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