Browse for a folder in VBA

[DHS100]

Hi,

I'm having a bit of trouble getting this to work. All I want is for a dialogue box to pop up so the user can select a folder and to be able to default to the last folder they visited. I've seen lots of ways to do this but there is a problem with the best method I've found so far. I'm using this:

Set ShellApp = CreateObject("Shell.Application").BrowseForFolder(0, "Please choose a folder", 0, "C:\test\")

The problem is that there is no way to go up a level in the directory structure. The above code mean you couldn't go to "C:\". Any help with this would be very much appreciated.

Thanks

 

[Richard Schollar]

Hi

Here's some code (requires xl2002 or later) to choose a folder starting from the passed in argument path. Because it's a standard dialog, you can navigate where you want from it:

Function GetFolder(strPath As String) As String
Dim fldr As FileDialog
Dim sItem As String
Set fldr = Application.FileDialog(msoFileDialogFolderPicker)
With fldr
    .Title = "Select a Folder"
    .AllowMultiSelect = False
    .InitialFileName = strPath
    If .Show <> -1 Then GoTo NextCode
    sItem = .SelectedItems(1)
End With
NextCode:
GetFolder = sItem
Set fldr = Nothing
End Function

 

[DHS100]

Thanks Richard...again you've saved my bacon...Happy Xmas

 

[DHS100]

Out of interest, with the line:

Set fldr = Application.FileDialog(msoFileDialogFilePicker)

is it possible to replace msoFileDialogFilePicker with a variable? It's a bit neurotic but I have two very similar pieces of code which involve the msoFileDialogFilePicker and the msoFileDialogFolderPicker. I'd like to have something like:

Set fldr = Application.FileDialog(MyVariable)

where MyVariable says something like "msoFileDialogFolderPicker".

The problem is that, after trying various dims (object, string...,etc.), I get "Type Mismatch".

Thanks again

 

[DHS100]

Sorry...ignore last message....I've figured it out

 

[Airfix9]

Hi

Here's some code (requires xl2002 or later) to choose a folder starting from the passed in argument path. Because it's a standard dialog, you can navigate where you want from it:

Function GetFolder(strPath As String) As String
Dim fldr As FileDialog
Dim sItem As String
Set fldr = Application.FileDialog(msoFileDialogFolderPicker)
With fldr
    .Title = "Select a Folder"
    .AllowMultiSelect = False
    .InitialFileName = strPath
    If .Show <> -1 Then GoTo NextCode
    sItem = .SelectedItems(1)
End With
NextCode:
GetFolder = sItem
Set fldr = Nothing
End Function

GENIUS! Just what I needed. Thanks.

 

[Jerry Sullivan]

Folder temporarily locked after FileDialog(msoFileDialogFolderPicker)

Hello,

I'm using the code Richard Schollar posted below in my app to return the name of a folder selected by the user. The code works perfectly for that function; however I am getting a side effect of not being able to delete or rename that folder until after exiting Excel. (I'm using MS Excel 2007.)

This would seem to be a memory leak or handle that needs to be cleared within my app. I thought the expression "Set fldr = Nothing" would release the only object created; but I must be missing something.

Thanks in advance for any help.
- Jerry

Hi

Here's some code (requires xl2002 or later) to choose a folder starting from the passed in argument path. Because it's a standard dialog, you can navigate where you want from it:

Function GetFolder(strPath As String) As String
Dim fldr As FileDialog
Dim sItem As String
Set fldr = Application.FileDialog(msoFileDialogFolderPicker)
With fldr
    .Title = "Select a Folder"
    .AllowMultiSelect = False
    .InitialFileName = strPath
    If .Show <> -1 Then GoTo NextCode
    sItem = .SelectedItems(1)
End With
NextCode:
GetFolder = sItem
Set fldr = Nothing
End Function

 

[Richard Schollar]

Re: Folder temporarily locked after FileDialog(msoFileDialogFolderPicker)

Jerry

Please post the full code you are using and indicate which line(s) doesn't work (state what, if anything, it does do) and state any errors you get returned by VBA. Please confirm what you do want to happen.

 

[Jerry Sullivan]

Re: Folder temporarily locked after FileDialog(msoFileDialogFolderPicker)

Richard,

The code is posted below. The core function, GetFolder, is unchanged from the example you shared. I've tried to isolate this by inserting it into a new module in a new file saved as "TEST.xlsm

Here are my steps and what is happening:

1. After rebooting my computer, I create two folders:
"C:\TEST_INITIAL"
"C:\TEST_TARGET"

2. I open TEST.xlsm and run Macro "Test"
3. The folderpicker dialog box comes up with TEST_INITIAL as the default.
4. I browse to C:\TEST_TARGET and click OK
5. The msgbox comes up with "C:\TEST_TARGET"
6. After clicking OK, the msgbox closes.
7. The code appears to have ended, since I can enter values into cells.

So far, so good. As mentioned in my post, this is exactly what I wanted and expected the code to do. However, if I open Windows Explorer and try to rename the folder C:\TEST_TARGET, a Windows error message comes back:
Error Renamining File or Folder
Cannot rename TEST_TARGET: It is being used by another person or program. Close any programs that might be using the file and try again."

Similarly, the folder TEST_TARGET cannot be deleted at this point.
The other folders including TEST_INITAL can be renamed or deleted

I can rename or delete TEST_TARGET after:
Exiting Excel or;
Running the macro again and select a different folder (that folder then becomes locked).

'--------------The code
Option Explicit

Sub Test()
MsgBox GetFolder("C:\TEST_INITIAL\")
End Sub

Function GetFolder(strPath As String) As String
Dim fldr As FileDialog
Dim sItem As String
Set fldr = Application.FileDialog(msoFileDialogFolderPicker)
With fldr
.Title = "Select a Folder"
.AllowMultiSelect = False
.InitialFileName = strPath
If .Show <> -1 Then GoTo NextCode
sItem = .SelectedItems(1)
End With
NextCode:
GetFolder = sItem
Set fldr = Nothing
End Function
'--------------

I'm running Excel 2007 with Widows XP Professional.
I've tried this on a second PC and had the same effect.

Thank you for any help you can provide.
-Jerry

 

[Richard Schollar]

Re: Folder temporarily locked after FileDialog(msoFileDialogFolderPicker)

Hi Jerry

This is a very unusual problem - I can recreate it using the xl version and OS you are using, but if I use xl2003 the problem isn't there. I have had some success with appending the following line to the end of the function:

'rest of function code above....
ChDir "C:\"
End Function

But you must then run this code fresh (ie you can't have had the old code run in the existing xl2007 instance - you need to close down and reopen Excel and then run the code with this ChDir statement). Then the locking doesn't seem to occur.

I have asked about this problem on a forum of VB experts to see if they can 1. Repropduce 2. Suggest why this might be happening.

Because it doesn't happen in 2003, it could be a bug.