I have used the "GetSaveAsFilename method" to let the user give a file name.
But now I need a "GetFolder method" that will let the user give a folder
name. The "GetFolder method" does not exist in VBA. Has anyone programmed
something that can do the trick ? - or can you give me a hint on how to do
it ?

Helge,

Download Jim Rech's BrowseForFolder example at
http://www.bmsltd.co.uk/DLCount/DLCo...eForFolder.zip .


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Helge V. Larsen" wrote in message
...
I have used the "GetSaveAsFilename method" to let the user give
a file name.
But now I need a "GetFolder method" that will let the user give
a folder
name. The "GetFolder method" does not exist in VBA. Has anyone
programmed
something that can do the trick ? - or can you give me a hint
on how to do
it ?

The easiest way to do this might be by calling on a Windows API function:

Sub ShowBrowseForFolder()
Dim Fld As Folder
Dim sh As New Shell
Set Fld = sh.BrowseForFolder(0, "Select a Folder", 1, "c:\")
If Not Fld Is Nothing Then
MsgBox Fld.Self.Path
End If
End Sub

For this to work you have to set a Tools, Reference to SHELL32 - "Microsoft
Shell Controls and Automation".

--
Jim Rech
Excel MVP

John Walkenbach shows you how to display the standard folders selection
dialog - sample code at:

http://j-walk.com/ss/excel/tips/tip29.htm
Selecting a directory

If you want to start from a specific location as the pre-selection, you can
use a more complex version provided by Jim Rech on Stephen Bullen's site on
the MVP page.

http://www.BMSLtd.co.uk/MVP/default.htm


Regards,
Tom Ogilvy


"Helge V. Larsen" wrote in message
...
I have used the "GetSaveAsFilename method" to let the user give a file
name.
But now I need a "GetFolder method" that will let the user give a folder
name. The "GetFolder method" does not exist in VBA. Has anyone programmed
something that can do the trick ? - or can you give me a hint on how to do
it ?

John has some code for allowing users to select a directory.

John Walkenbach : Selecting a Directory
http://j-walk.com/ss/excel/tips/tip29.htm

HTH
Paul
--------------------------------------------------------------------------------------------------------------
Be advised to back up your WorkBook before attempting to make changes.
--------------------------------------------------------------------------------------------------------------

I have used the "GetSaveAsFilename method" to let the user give a file name.
But now I need a "GetFolder method" that will let the user give a folder
name. The "GetFolder method" does not exist in VBA. Has anyone programmed
something that can do the trick ? - or can you give me a hint on how to do
it ?

FYI, Helge, the advantage of using the download that Chip cited is that it
lets you preselect a specific folder when you show the dialog box. This is
good if say you're asking the user for a "data save folder" and you want to
default it to his current choice.

The advantage of the Shell API route is that (1) it is far simpler and (2)
you can set a "root" folder (like C:\). The download doesn't let you do
this because I never figured out how<g.

--
Jim Rech
Excel MVP