Open files in folder by keyword, use wildcard *

**jscott15** · 01-19-2011, 02:40 PM

Hello, I want to open all excel files in a folder on SharePoint that include the words "Detail Form" in the filename. I can get my code to work if I hard-code the filenames, but the wildcard isn't working. Here is what I'm trying to use right now:

Please Login or Register  to view this content.

The DeptFolder variable works, but as soon as I add a wildcard instead of the full filename, I get errors.

Thanks in advance for your help.

**JBeaucaire** · 01-19-2011, 07:06 PM

More code is needed to make the wildcard applicable. Using the DIR() method is a common way to cycle through all the files in a particular folder using wildcards, like so:

Please Login or Register  to view this content.

**jscott15** · 01-21-2011, 05:37 PM

Wow that is great, thanks a lot for your help!

**jscott15** · 01-25-2011, 05:20 PM

Sorry, I should have tried the code before I said something...

I am doing something wrong with the Dir() command. I consistently am getting a "Bad file name or number" error when using Dir(). I wrote the following sample script to illustrate my problem. The WB will open, so clearly the path and filename are correct, but then when I get to DirTest, it breaks and gives the error message.

Please Login or Register  to view this content.

I really appreciate any help, and will refrain from saying something is working until I have actually tried it...

**JBeaucaire** · 01-25-2011, 10:32 PM

Use the first method. I don't know that the DIR() call will actually pass to the internet, and since the first syntax works and solves your problem, don't fret the time one the DIR() method. Save that for when you want to cycle through many files in a local folder.

**Poolio19** · 09-22-2011, 05:01 AM

I've got a similar, (I think) simpler query... (should I post this elsewhere? i.e. not on a solved thread?)

I have a file, which is always saved with the same name in the same directory folder, however it is routinely updated and renamed as a new version with relevant date i.e. "FILE_NAME_vxx_DD-MM-YYYY"... the older versions are dragged to a superseded folder, so they won't interfere with a search performed in the main folder.

I've set up a toolbar that has a Macro which opens the file (as I work on it a lot) but whenever the file name changes I have to update the Macro to reflect the new version number and date....

So I'm after some code that will find: FOLDER/FOLDER/FOLDER/FILE_NAME*

where * represents the varying version/date text... I've had a tinker with the code above but don't fully understand what I'm doing - anyone able to sort this please?

Thanks JP

My attempt:

Please Login or Register  to view this content.

**arthurbr** · 09-22-2011, 06:04 AM

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.

**trixam** · 11-15-2011, 11:34 AM

Originally Posted by JBeaucaire

More code is needed to make the wildcard applicable. Using the DIR() method is a common way to cycle through all the files in a particular folder using wildcards, like so:

Please Login or Register  to view this content.

Thank you for this great script. It is short and simple. It has helped me tremendously.

The line Workbooks.Open wbName does not work for me. I have to change it to Workbooks.Open (MyPath & wbName).