Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    Leigh on Sea, Essex, England
    Posts
    263
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Getting user to select a path (Office 2003)

    Hi.

    I have Excel VBA macros where I do a lot of APPLICATION.GETOPENFILENAME calls to get files which are needed in calculations. Often, though, I would also like to be able to get the user to specify a FOLDER where I can then recursively search for things as I need them (e.g. for similarly named files organised in geographic folders). I can't use the GETOPENFILENAME method, though, because that looks for a file, and there doesn't seem to be an equivalent GETPATH or GETFOLDER function.

    I don't want to ask the user to type the full path because they wouldn't know it necessarily, and also it's too prone to error. Anyone know what I can do?

    Thanks.

    Stuart

    PS - There is a GETFOLDER method in the FileSystemObject, but it needs a full file path first before it will give you the folder. I don't want to go down that route, because it's not intuitive - having to select something that isn't necessarily applicable in the base folder you want in order get the folder name. It also doesn't work if there are no files in the base fodler!

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Getting user to select a path (Office 2003)

    You can use Application.FileDialog (introduced in Office XP):

    Dim strFolder As String
    With Application.FileDialog(msoFileDialogFolderPicker)
    ' Optional: set folder to start in
    .InitialFileName = "C:Excel"
    If .Show = True Then
    strFolder = .SelectedItems(1)
    End If
    End With

  3. #3
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    Leigh on Sea, Essex, England
    Posts
    263
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Getting user to select a path (Office 2003)

    Nice and simple. Thanks very much, Hans.

    Stuart

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •