Find a folder by its name or wildcard

[bdb1974]

Hi all,

I found some code which determines if a folder/directory exists.
I'm trying to re-work it so I can use an input box to specify a specific folder name
or part of a folder name.

Here's what I'm play around with:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

I'm having trouble in incorporating the wildcard (*).
You can find the unaltered code here:
http://www.vboffice.net/sample.html?...2&cmd=showitem

Any help is appreciated.

Thanks,

BDB

[mojo249]

Maybe the following will give you the idea?

Sub RunCodeOnAllXLSFiles()
Dim lCount As Long
Dim wbResults As Workbook
Dim wbCodeBook As Workbook


Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False

Please Login or Register  to view this content.

[Leith Ross]

Hello bdb1974,

It seems to me you are over complicating a simple VBA procedure. Here is an example using only the Dir statement. This checks only the parent folder. It can be changed to search the sub folders if needed. You can include wildcards in the FolderName.

Please Login or Register  to view this content.

[bdb1974]

Thanks Leith.

With your help thus far, it's almost there with what I want it to search.
I made a few changes that allow me to seach for a specific folder within a given path, using the wildcard (*).
(See below)

However, I am not able to search subdirectories for the word or partial name entered.

Maybe this is my fault for not correctly stating my needs earlier.

I'd would also like for the subfolders names found to be display (either in a msgbox / sheet1 (in any column & row).

Please Login or Register  to view this content.

Please Login or Register  to view this content.

If you or anyone else can lend assistance in allowing subdirectories to be searched,
I would be most thankful.

Thanks again for all the help,

BDB

[Leith Ross]

Hello bdb1974,

Do you want to search only the sub-directories of the parent folders or do you want to search all sub-folders in the tree (sub-folders of sub-folders, ad nauseam)?

[bdb1974]

Yes, I'd like for it to search within subs as deeply as there maybe subs within a tree(branch). FYI, subs will probably not be more than 2 or 3 tiers deep.

Thanks,

BDB

[bdb1974]

I've found some other code, which I think might work.

http://www.vbforums.com/showthread.php?t=244915
Re: VB - List All The Files In A Directory by Paul M.
--------------------------------------------------------------------------------

Here is my shot it gets all files in sub directories to!

But, I'm also getting errors while attempting to get it to work.
See below for error received.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[Leith Ross]

Hello bdb1974,

Here is a macro I wrote using the File System Object to list all files in all folders on a worksheet. Play around with this to match only files with the wildcards. This works with Windows 2000 and up. If you have questions, ask me.

Please Login or Register  to view this content.

[bdb1974]

Thanks Leith,

I'm really not interested in finding files within the subfolders or at least
not at first.

I only want the names of subfolders returned that matches a users input.
The users input can be a complete or partial folder/directory name.


With your lastest macro offering, I'm not having much luck on getting it to work.


First off, just inserting the code into a module as is, I don't know how to call the maco. I tried rearranging the parameters, so the macro is visible to execute, but my attempts are triggering alarms for the (ByVal) in theheader setup.

Please Login or Register  to view this content.

Not being an expert, only a "learn as you go" guy, I'm not sure why this is happening. Next, a little further in, I didn't see where I would delegate a path to
search (unless it's using the active directory of the workbook).

Looking at the code, it's missing some lines that I'm familiar in seeing like:
...excerpt

Please Login or Register  to view this content.

So, If you can, please ellaborate or provide further assistance. Cause, I'm still sitting just as stuck and destraught as before.

Thanks,

BDB

BDB

[Leith Ross]

Hello bdb1974,

Sorry, I thought your far enough along in VBA to modify the macro. I modified another macro I wrote to list all folders and sub-folders. This uses the File System Object also. This is better choice because Microsoft no longer includes the Application.FileSearch in Excel 2007 and later. Change the path in the macro ListFolderList. This will list all folders that match the filter you supply on the active worksheet.

Please Login or Register  to view this content.

[bdb1974]

Hi Leith,

Thanks for your latest assistance, My attempt to use the code you provided has once again fallen into the unsuccessful category.

My first issue that I had encountered was with the use of
Public FolderList() As Variant

Please Login or Register  to view this content.

The following code is placed in a userform, where the commandbutton1 starts code execution.

Please Login or Register  to view this content.

Again any help is sincerely appreciated.

BDB

[Leith Ross]

Hello bdb1974,

My guess is you did not copy this code into a standard VBA module but into the General Declarations section of the UserForm. All of the code, with the exception of the macro "ListFolderList", must be in a standard VBA module. I have had no problems with code running under Excel 2000 or Excel 2003. My only other guess would be you whatever version of Excel you are running has a problem with the syntax. I don't recall you saying which version of Excel this running on.

[bdb1974]

Leith, FYI I'm using Excel 2003.

Here's an update on my findings.

I moved the code to a standard module. Still no success. Steping through the code,
with whatever word/value that I use for my filter, once the code steps in the
ReDim Preserve FolderList(Cnt) with the Folder Name is equal to the Filter, an error occurs for a mismatch on the line.

Please Login or Register  to view this content.

Filter value = "2007"
(DirPath) = "L:\Global\Elec Dept Projects\Completed"
For Each Folder = "L:\Global\Elec Dept Projects\Completed\2007"
Results in:
FolderList(Cnt) = <Type Mismatch>

HTH's
BDB

[Leith Ross]

Hello bdb1974,

If you post your workbook, I can track the problem down faster.

[bdb1974]

Leith, here's my practice workbook. It has codes in place to run from a standard module or from the userform. The code is "kindof" messy due to me experimenting around with it.

FYI, another discovery that I found,.

Running the code inside the userform with the following changes, allows the code to run completely through, I will get an output of "0" due to folderlist = "empty"

Please Login or Register  to view this content.

Regards,

BDB

[Leith Ross]

Hello bdb1974,

Okay, I corrected the errors in the Module1 code and the macro shows the folders. I didn't check any other code like the UserForms. Do not set the Cnt variable in the macro MapFolders. This is used by the macro to count the found folders when it calls itself recursively. To check all sub-folders follow the parent path with True. Otherwise, you only get a list of sub-folders in the parent directory.

Please Login or Register  to view this content.

Here is the revised Module1 code:

Please Login or Register  to view this content.

[bdb1974]

Thanks Leith,

Your assistance and patience is 2nd to none.
All I can say is Thanks. It's really nice to finish the week with some success.

The code does work as you state. I do have one remaining question in regards to this
posting. I would really like to start the macro through a userform control.
Is this not possible? I've tried several methods to call the macro from within a userform.
None of my attempts have been successful. I am able to start the code with a sheet button linking to the macro. I guess, if all ele fails than this will have to remain my
only option.

Again, Thank you my friend and have a great weekend.

BDB

[Leith Ross]

Hello bdb1974,

Here is an example using your command button on UserForm2.

Please Login or Register  to view this content.

[bdb1974]

Leith,

Once again thanks for your latest reply.

This morning, I have been working around with the bit of code you posted.

I've managed to come up with a few interesting results from it.
I am able to run the macro from my userform button and get the result listed on the worksheet, but just not in the listbox.
I was able to get some information returned to the listbox by placing this line

Please Login or Register  to view this content.

into the function code.

Please see my example sheet setup.

To test :
Place a Keyword(sub directory Name) into B1 of Sheet1
Place a directory path in B2 of Sheet 1.
Click "Start UserForm" Button on Sheet1.
This will open the userform.
The Search Box is currently not functioning however you'll use the "Search" Button to activate the macros.

Click the Search button with option button1 selected.
You'll See A result happen in the ListBox on the userform.

Next
Select Option Button2, then run the same search button,
This will allow the use of the Keyword and Path given on Sheet1.
The result will be on Sheet 1.

What I would like to have happen is the Key word be Typed into the Searchbox on the Userform and the Result be Shown in the Listbox found on the userform.

With everything you've provided, it's been a true Trial and Error for me to try to come up
with results I have thus far. It's so close to what I'm seeking.

I'm really hoping you can take another a look at my latest attempts and help me push
this post to completion.

Thanks again,

BDB

[Leith Ross]

Hello BDB,

I made quite a few corrections to the code. I believe it now functions like you want it to. Check it out and let me know if it is close to what you want.

[bdb1974]

I'm wondering why is it I can't assign a value for my Filter which is a string to
something like:

Please Login or Register  to view this content.

Currently all that is there is:

Please Login or Register  to view this content.

This basically assigns the "*" because the filter is ("") empty.
In the following:

Please Login or Register  to view this content.

When I try to assign a value to the filer I get an error for invalid argument /procedure call.
The following line of code get's highlighed in yellow.

Please Login or Register  to view this content.

If I could assign a value to the filter, then this would solve part of the problem of getting
my result into the listbox.
Currently all folders within the path are being listed.

Another problem seen is all the folder listed in the listbox are spread out by empty lines.
Is there a way to eliminate these empty lines and bring all the informaton collected to the top of the list?

BDB

[snb]

or
all (sub)folders in E:\ in combobox1

Please Login or Register  to view this content.

dependent on the complexity of drive E you need to build in some patience (Application.wait or DoEvents)

[bdb1974]

I apologize that I still have a few more questions plus I had to make a change
in a line of code in the userform1(Private Sub CommandButton1_Click () ), to get it to list folders within the listbox.

I had to change:

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

Reason: I need the path to always be set to "L:\Global\Elec Dept Projects\" without
having to type it into the Textbox1.Value
Also, the Textbox1.value should be the filter. The word/(Folder) or Partial Folder Name being sought.

However, that being said, with the change that I made to Cnt, I have to leave the TextBox1.Value empty. Otherwise, the code will error for mismatch. Leaving the TextBox1 empty allows no filtering and thus, all folders within the path are listed and without spaces. (Cool. But..)

This leads me back to my question in my previous post.
How do I set the filter to a key word and keep it from erroring?

Regards,

BDB

[snb]

With Textbox1.Text as filter:

Please Login or Register  to view this content.

[Leith Ross]

Hello BDB,

Sorry about not changing the path back. I needed to use local folders to troubleshoot the code. Did you want to use TextBox1 for the filter? I will recheck the code but that should not cause an error.

[bdb1974]

Sorry about not changing the path back
I needed to use local folders to troubleshoot the code.

That's what I figured, so for the conveinence, I tried to set it up so the path could be changed by placing it in B1 on Sheet(1) instead of the code (For Now).

But, yes, if you can change it so that the filter being used is through the text box1.value

Or should I say, "*" & textbox1.value & "*" .

That would be awesome, I think that will resolve this thread.

Thanks,

BDB

[Leith Ross]

Hello BDB,

If you want to use TextBox1 to apply the filter then you need to change the MapFolders macro in the UserForm to what is below.

Please Login or Register  to view this content.

[bdb1974]

Thanks SNB for the input,

I hate to try and change course right now, but maybe I can check this method out later.
Or it maybe the right choice for someone else.

Regards,

BDB

[bdb1974]

Leith,

That's IT !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

You're an absolute genius!

I made the small change of :

Please Login or Register  to view this content.

and wallah magic is happening..!..!!!...!!!!!!

Thank you soooo much,

BDB