ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Select multipler folders? (https://www.excelbanter.com/excel-programming/451217-select-multipler-folders.html)

Robert Crandal[_3_]

Select multipler folders?
 
Does Excel have a built-in dialog box that will allow
users to select multiple folders within the same directory?
Once the user selects a set of folders, my goal will be
to copy a file (or files) into each folder that was chosen.

Thanx




GS[_6_]

Select multipler folders?
 
Does Excel have a built-in dialog box that will allow
users to select multiple folders within the same directory?
Once the user selects a set of folders, my goal will be
to copy a file (or files) into each folder that was chosen.

Thanx


AFAIK, msoFileDialogFolderPicker doesn't support multi-select. Perhaps
a userform with a directory tree and a listbox is one way to go, where
the user can add selected paths to the listbox for batch processing.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

GS[_6_]

Select multipler folders?
 
Hmm.., seems you need a Visual Studio license to use the directory
control.

You could list all the subfolders under a specific parent folder in a
check style, multi-select listbox. This requires you (optionally) set
the parent folder in the normal way, then run a drill-down routine to
list that folder's subfolders in the listbox. Users can check the
folders to include for the batch processing.

Idea:
Have the userform prompt for parent folder in its 'Initialize' event.
This gives an abort option if no folder is selected.

When folder is chosen, list its subfolders and display the userform.

Provide a button for users to cancel.

Provide a button to call your batch processing routine.

FWIW:
One of my file lister utilities recursively drills down a top level
folder for subfolder using FileSystemObject. It could be easily
modified for your use with this scenario...

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Robert Crandal[_3_]

Select multipler folders?
 
"GS" wrote:

Idea:
Have the userform prompt for parent folder in its 'Initialize' event.
This gives an abort option if no folder is selected.

When folder is chosen, list its subfolders and display the userform.

Provide a button for users to cancel.

Provide a button to call your batch processing routine.


Yup, I pretty much had the same idea too. I have a loop that
reads just the folder names inside C:\Data. I will then add each
folder name string into the listbox by calling ListBox1.AddItem. The
user will then be able to select multiple folders. It turned out
to be easier than I expected.

Thanks Gary!



GS[_6_]

Select multipler folders?
 
That's great!

I decided to add a routine to my collection that just lists subfolder
paths under a parent folder in a userform. You're welcome to check it
out...

https://app.box.com/s/23yqum8auvzx17h04u4f

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Robert Crandal[_3_]

Select multipler folders?
 

"GS" wrote

I decided to add a routine to my collection that just lists subfolder
paths under a parent folder in a userform. You're welcome to check it
out...

https://app.box.com/s/23yqum8auvzx17h04u4f


Oh wow, that is very nice. It uses less code than my solution.
Thanks, I'm gonna study this further.




GS[_6_]

Select multipler folders?
 
"GS" wrote

I decided to add a routine to my collection that just lists
subfolder paths under a parent folder in a userform. You're welcome
to check it out...

https://app.box.com/s/23yqum8auvzx17h04u4f


Oh wow, that is very nice. It uses less code than my solution.
Thanks, I'm gonna study this further.


I appreciate the feedback! There's a companion enum for specifying a
start folder in the GetDirectory function's OpenAt arg...

Enum SpclFldrs
cmnDesktop = 25: cmnAppData = 35
myDesktop = 0: myPath = 40: myAppData = 26: myTempDir = -99: myDocs =
5: myPics = 39
osSysDir = 37: osSysWow = 41: osDrvs = 17: osFonts = 20: osRecBin =
10
ProgFiles = 38: AdminTools = 48
End Enum


Have fun...!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Robert Crandal[_3_]

Select multipler folders?
 

"GS" wrote:

I decided to add a routine to my collection that just lists subfolder
paths under a parent folder in a userform. You're welcome to check it
out...

https://app.box.com/s/23yqum8auvzx17h04u4f


If the listbox gets filled with more than 100+ listbox items, it
will become rather tedious manually checking off or selecting
all items in the list. What methods do you suggest for quickly
choosing the entire list?




Robert Crandal[_3_]

Select multipler folders?
 

"GS" wrote:

I appreciate the feedback! There's a companion enum for specifying a start
folder in the GetDirectory function's OpenAt arg...

Enum SpclFldrs
cmnDesktop = 25: cmnAppData = 35
myDesktop = 0: myPath = 40: myAppData = 26: myTempDir = -99: myDocs = 5:
myPics = 39
osSysDir = 37: osSysWow = 41: osDrvs = 17: osFonts = 20: osRecBin = 10
ProgFiles = 38: AdminTools = 48
End Enum



Pardon my ignorance, but what does that code do? How do I use it?




Robert Crandal[_3_]

Select multipler folders?
 
"GS" wrote:

I decided to add a routine to my collection that just lists subfolder
paths under a parent folder in a userform. You're welcome to check it
out...

https://app.box.com/s/23yqum8auvzx17h04u4f


Hi Gary. There is a slight problem with your example above.

Have you tried repeatedly selecting "on and off" one of the
same items in the listbox? If you click on the same item
multiple times it will add that same path item to the array.

Rather than using the code in the lstFldrs_Change() method,
wouldn't it be better just to write a loop that steps through
each item, check if it's selected, and then add it to the array?




Claus Busch

Select multipler folders?
 
Hi Robert,

Am Wed, 16 Dec 2015 04:59:23 -0700 schrieb Robert Crandal:

Rather than using the code in the lstFldrs_Change() method,
wouldn't it be better just to write a loop that steps through
each item, check if it's selected, and then add it to the array?


create a Userform with a ListBox and a Button.
Then try it with following code:

Private Sub UserForm_Initialize()
Dim fs, f, f1, fc, s

Const myPath = "C:\data\"

Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFolder(myPath)
Set fc = f.SubFolders
For Each f1 In fc
Me.ListBox1.AddItem f1.Name
Next
Me.ListBox1.MultiSelect = fmMultiSelectMulti
End Sub

Private Sub CommandButton1_Click()
Dim nameOld As String, nameNew As String
Dim FN As String
Dim i As Long
Const myPath = "C:\data\"

'File that should be copied
nameOld = "C:\Users\Claus\Desktop\Form.xlsm"
FN = Mid(nameOld, InStrRev(nameOld, "\"))
Workbooks.Open (nameOld)

For i = 0 To ListBox1.ListCount - 1
If Me.ListBox1.Selected(i) = True Then
nameNew = myPath & ListBox1.List(i) & FN
ActiveWorkbook.SaveAs nameNew
End If
Next
ActiveWorkbook.Close
End Sub

You have to modify the path and the file name. Or look here for
"FolderSearch" and download the file (macros are disabled in OneDrive)


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

GS[_6_]

Select multipler folders?
 
"GS" wrote:

I appreciate the feedback! There's a companion enum for specifying
a start folder in the GetDirectory function's OpenAt arg...

Enum SpclFldrs
cmnDesktop = 25: cmnAppData = 35
myDesktop = 0: myPath = 40: myAppData = 26: myTempDir = -99:
myDocs = 5: myPics = 39
osSysDir = 37: osSysWow = 41: osDrvs = 17: osFonts = 20: osRecBin
= 10
ProgFiles = 38: AdminTools = 48
End Enum



Pardon my ignorance, but what does that code do? How do I use it?


This 'enum' provides the ability to use Intellisense when writing code
that uses the GetDirectory() procedure so you can specify its 'OpenAt'
arg for the common Windows 'namespace' folders.

Example:
To have the folder browser dialog open with 'Computer' as the top level
directory and only show drives...

sPath = GetDirectory(SpclFldrs.osDrvs)

You can also pass a path string to specify a specific location where
you want users to select/create a subfolder...

sPath = GetDirectory(ThisWorkbook.Path)

...under the project file's directory.

If you add the enum to the module with the GetDirectory() procedure you
can use Intellisense in the Immediate Window to see the results.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

GS[_6_]

Select multipler folders?
 
"GS" wrote:

I decided to add a routine to my collection that just lists
subfolder paths under a parent folder in a userform. You're welcome
to check it out...

https://app.box.com/s/23yqum8auvzx17h04u4f


Hi Gary. There is a slight problem with your example above.

Have you tried repeatedly selecting "on and off" one of the
same items in the listbox? If you click on the same item
multiple times it will add that same path item to the array.

Rather than using the code in the lstFldrs_Change() method,
wouldn't it be better just to write a loop that steps through
each item, check if it's selected, and then add it to the array?


The example only shows selection. IOW, it's not a 'working example'
that demos if a user changes their selections. Such would require more
coding as desired for your needs. I suppose a generic sample that
handles any 'working real-world' scenario could be made, ..but I don't
have the time (or energy) right now to do so. I see Claus has offered
something for your consideration...

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

GS[_6_]

Select multipler folders?
 
"GS" wrote:

I decided to add a routine to my collection that just lists
subfolder paths under a parent folder in a userform. You're welcome
to check it out...

https://app.box.com/s/23yqum8auvzx17h04u4f


If the listbox gets filled with more than 100+ listbox items, it
will become rather tedious manually checking off or selecting
all items in the list. What methods do you suggest for quickly
choosing the entire list?


Add a 'Select All' button and set its 'List' to selected...


Private Sub btnSelectAll_Click()
Dim n&
With Me.lstFldrs
For n = 0 To .ListCount - 1
.Selected(n) = True
Next 'n
End With
End Sub

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion


All times are GMT +1. The time now is 08:24 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com