Evaluate end of folder name

**cindy75** · 03-25-2008, 02:25 PM

I need help with a project. Excel 2003.

I have a directory that has multiple folders in it with a dealer name and a four digit number at the end, i.e. Hampton Marine (2609). The dealer name length will vary, but the folder will always end with (####)

I will have a new workbook containing a report that is for a specific dealer. I will know the dealer's four digit number and the name. But, that name may not match the folder name exactly if it exists. The number will match.

I want to evaluate the four digit dealer number at the end of the folder name and save my new file into that folder if it exists. If it does not, create the folder and save my new file into that folder. This will loop thru for multiple dealers.

help!

**Leith Ross** · 03-25-2008, 09:36 PM

Hello cindy75,

Welcome to the Forum!

This macro takes 3 arguments. First is the folder suffix, or in your case, the dealer code. Second is the name to give the folder if the folder doesn't exist. The suffix will be added to this name automatically. The last argument is optional. This is the directory path. If you don't supply one, the macro will use the current directory.

Macro Code

Please Login or Register  to view this content.

The directory path is searched for folders that match the dealer code. On the first match, the macro exits. If there are no matching folders then the folder is created by using the new name argument and appending the folder suffix to it.

Usage Example
The worksheet has the following cell values:
A1 = "(2609)"
A2 = "Hampton Marine Inc."
A3 = "C:\Documents and Settings\Vendors\Reports"

Please Login or Register  to view this content.

Adding the Macro
1. Copy the macro above pressing the keys CTRL+C
2. Open your workbook
3. Press the keys ALT+F11 to open the Visual Basic Editor
4. Press the keys ALT+I to activate the Insert menu
5. Press M to insert a Standard Module
6. Paste the code by pressing the keys CTRL+V
7. Make any custom changes to the macro if needed at this time
8. Save the Macro by pressing the keys CTRL+S
9. Press the keys ALT+Q to exit the Editor, and return to Excel.

Sincerely,
Leith Ross