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

 

[Jerry Sullivan]

Hi Richard,

I really appreciate your efforts on this, and those of anyone else who looks into it.

This isn't really a showstopper for the typical use of the program - I only discovered it because I was creating temporary folders for testing and found I couldn't delete them immediately.

That being said, my app will be used by many people at my company, so I'd like it to work as well as possible. I'll use your ChDir "C:\" workaround unless another solution comes along.

BTW the best thing about this possible bug is that I discovered this site and community. I've always tried to figure things out on my own and been reluctant to post questions on help sites. Since I joined the site a few days ago, I've really enjoying learning from others and sharing what I know.

Thanks again,
-Jerry

 

[ExcelAnalyst]

A "better" way of handling the locked folder issue is to grab the initial folder at the start of the function and reset at the end. The problem is caused by FileDialog changing the current directory upon clicking ok. Try the following amended code.

Function GetFolder(strPath As String) As String
Dim fldr As FileDialog
Dim sItem As String
Dim sInitDir as String
sInitDir = CurDir ' Store initial directory
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:
' Reset directory before exit
ChDrive sInitDir ' Return to the Initial Drive
ChDir sInitDir ' Resets directory for Initial Drive
GetFolder = sItem
Set fldr = Nothing
End Function

 

[Jerry Sullivan]

That is better. Thank you, ExcelAnalyst.

 

[puma1133]

The previous codes had some problems in 64-bit Excel 2010. Here is the solution:

Function GetFolder(InitDir As String) As String
Dim fldr As FileDialog
Dim sItem As String
sItem = InitDir
Set fldr = Application.FileDialog(msoFileDialogFolderPicker)
With fldr
.Title = "Select a Folder"
.AllowMultiSelect = False
If Right(sItem, 1) <> "\" Then
sItem = sItem & "\"
End If
.InitialFileName = sItem
If .Show <> -1 Then
sItem = InitDir
Else
sItem = .SelectedItems(1)
End If
End With
GetFolder = sItem
Set fldr = Nothing
End Function

 

[BrendanDixon]

Hi,

Can someone tell me how I can open the folder picker and then once selection the folder it will store the folder path in a cell of my document?

Thanks

 

[RSXchin]

Do I need to enable any libraries?
The dialog never comes up, and it treats fldr as a string

Set fldrs = Application.FileDialog(msoFileDialogFolderPicker)

ACTUALLY makes fldr say "Application.FileDialog(msoFileDialogFolderPicker)"

The dialog never comes up.

 

[RSXchin]

bump?

 

[albert5268]

Hi,

Can someone tell me how I can open the folder picker and then once selection the folder it will store the folder path in a cell of my document?

Thanks

Hi

This is my first post I hope its helpful.

Insert this code at the end. It will set the value of B1 in sheet1 as the selected folder. I tested in XL2007 (x64)

    Sheet1.Cells(1, 2).Value = BrowseFolder.SelectedItems(1)

 

[albert5268]

Do I need to enable any libraries?
The dialog never comes up, and it treats fldr as a string

Set fldrs = Application.FileDialog(msoFileDialogFolderPicker)

ACTUALLY makes fldr say "Application.FileDialog(msoFileDialogFolderPicker)"

The dialog never comes up.

Hi

This line of code just sets 'fldrs' as application.FileDialog(msoFileDialogFolderPicker)
You need to declare fldrs as a FileDialog variable.

dim fldrs as FileDialog

To bring up the dialog you need to use.

fldrs.show

The chosen folder will be returned as fldrs.SelectedItems(1).

 

[papageorgio326]

As a follow-up question, how is it possible to utilize the .allowmultiselect?

I set this variable equal to true, but it seems that the functionality isn't available.

Edit: I'm holding the Ctrl button while multi-selecting.