Select Multiple Folders

[lljo40]

I have an Excel application, where the user can browse to a folder and
process all the documents in that folder. I use the Office 10
FileDialog object (FolderPicker) to allow the user to do this with an
easily recognizable dialog box.

The problem is, the users would like to select multiple folders at a
time (which FolderPicker does not allow).

I could allow them Browse and select a folder, add it to a list, then
Browse again and add to the list, then Browse again, but that would be
a long tedious process.

Is there anything that would allow me to select multiple folders at a
time, similar to how you can select multiple files at a time?

Thanks for any suggestions,
Larry

 

[Ken Wright]

If this is something they do regularly, can you not just store all those
folders under a single folder, and then have them select that folder and
just ensure your code processes all subfolders?

 

[lljo40]

This is a utility that is used very frequently, by many different
groups in our company. The data they use is on many different servers,
so the folders can't be combined into the same tree structure.

Thanks for the suggestion though.

 

[Dave Peterson]

Maybe you could create a userform that shows a listbox (multiselect) that allows
the users to choose the folders that they want to process.

If you limit your starting folder to something nice (or have a very fast pc), it
might be manageable. (I started at C:\ and got close to 3000 folders. I
wouldn't want to go through that list each time I ran your utility.)

Anyway...

I created a user form with a button and a listbox on it.

I had this code behind the userform:

Option Explicit
Private Sub CommandButton1_Click()
Dim iCtr As Long

For iCtr = 0 To Me.ListBox1.ListCount - 1
If Me.ListBox1.Selected(iCtr) Then
MsgBox Me.ListBox1.List(iCtr)
End If
Next iCtr

End Sub
Private Sub UserForm_Initialize()

With Me.ListBox1
.MultiSelect = fmMultiSelectMulti
Call GetFolders("C:\my documents\excel") '<--starting point
Call SortFolders(myFolders)
.List = myFolders
End With

End Sub

Then in a general module, I had this code:

Option Explicit
Public myFolders() As String
Dim fCtr As Long
Sub GetFolders(StartFolder As String)

Dim FSO As Object
fCtr = 1
ReDim myFolders(1 To 1)
myFolders(1) = StartFolder
Set FSO = CreateObject("Scripting.FileSystemObject")
ProcessOneFolder FSO.GetFolder(StartFolder)

End Sub
Sub ProcessOneFolder(Fldr As Object)
Dim OneFolder As Object

For Each OneFolder In Fldr.SubFolders
fCtr = fCtr + 1
ReDim Preserve myFolders(1 To fCtr)
myFolders(fCtr) = OneFolder.Path
ProcessOneFolder OneFolder
Next OneFolder

End Sub
Sub SortFolders(myArr As Variant)

Dim iCtr As Long
Dim jCtr As Long
Dim Swap As Variant


For iCtr = LBound(myArr) To UBound(myArr) - 1
For jCtr = iCtr + 1 To UBound(myArr)
If LCase(myArr(iCtr)) > LCase(myArr(jCtr)) Then
Swap = myArr(iCtr)
myArr(iCtr) = myArr(jCtr)
myArr(jCtr) = Swap
End If
Next jCtr
Next iCtr
End Sub
Sub testme()
UserForm1.Show
End Sub

(I modified some code by Chip Pearson to obtain the names of the folders. I
stole some more code from John Walkenbach to sort that list. I try not to do
any actual work myself!)

Chip's:
http://google.co.uk/groups?threadm=OaO9z#[email protected]

John's:
http://j-walk.com/ss/excel/tips/tip47.htm

 

[lljo40]

Thanks Dave.

I was thinking I would have to do something like this. Guess I'll give
the user the chance to choose their starting folder, it's just not
going to be pretty. And can be time consuming, since they will be
running these on servers with sometimes, thousands of folders.

 

[Ken Wright]

If it's just that they are different groups then in theory the different
groups will be accessing the same folders per group regularly surely, in
which case simply have them fill a series of cells with the paths that they
use regularly, and then have the code run through those cells using each
path in turn. They can add to or remove from the list at will, and the code
will just run through whatever their list is. They may have minor
maintenance in keeping the list updated, but they have to do something.