Hello,
I'm looking to create a macro to remove a suffix from many files stored on my hard drive within subfolders. My files are stored in the order listed below. Can it be done?
E: > Publishing > Catalog
inside "Catalog" their are "artist" folders. Inside the "artist" folders, their are two subfolders, "Vox" and "Ins" The files with the suffix I want to remove are located in "Vox" and "Ins".
The suffix starts with O-
I want to remove the O- and everything that comes after it.
hi Orangeworker,
Yes, it can be done
Here are some links that may help when modified & used in conjuction with the VBA "Name" statement:
http://www.mrexcel.com/forum/showpos...68&postcount=2
http://www.vbaexpress.com/forum/showthread.php?t=15194
http://www.codeforexcelandoutlook.co...der-using-vba/
Let us know if you need any help modifying them...
Leith,
I'm being a slacker & am off to bed so I'm sort of hoping you'll work your normal magic & give the OP a nice tidy finished product
hth
Rob
Rob Brockett
Kiwi in the UK
Always learning & the best way to learn is to experience...
Hello Orangeworker,
This macro will rename the files with the "O-"suffix in the folders "Vox" and "Ins".
Please Login or Register to view this content.
Sincerely,
Leith Ross
Remember To Do the Following....
1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.2. Thank those who have helped you by clicking the Starbelow the post.
3. Please mark your post [SOLVED] if it has been answered satisfactorily.
Old Scottish Proverb...
Luathaid gu deanamh maille! (Rushing causes delays!)
Nice one Leith
I really have to learn how to use Regular Expressions, I just don't seem to have a use for it often enough to make a better effort at learning them.
I may play with your code tomorrow to see if I can use the Reg Ex to improve my tests in http://www.excelforum.com/2451794-post13.html
When I first read the op I thought that there were many different folders (for different artists) inside the Catalog folder. On re-reading the op, I think it may be that there are many different folders below "E:\Publishing\" for different "catalogs" with even more sub folders for each artist. Perhaps an extra couple of For Each loops are needed...?
Rob
Hello Rob,
That thought had crossed my mind as well. I decided to go with the simpler option. If more sub-folders need to be searched then the code can be amended accordingly. We'll just have to wait for an answer.
hi Leith,
Good point
I've just been reading some software design pages including http://en.wikipedia.org/wiki/You_ain%27t_gonna_need_it which ties right in with your use of the simpler option. It's good to see theoretical principles used in the real world - "yagni" or KISS is one I often have to remind myself of
Rob
Tha's why I'd prefer:
Please Login or Register to view this content.
Hi Everyone,
Thanks for all the input on this...sorry it has taken me a few days to respond. I couldn't get any of the code to work just yet, but I think it's close. I believe the line that has "Artist" may be where the problem lies. For the sake of clarity, the directory is as such:
E:\Publishing\Catalog
inside "Catalog" their are artist folders/subfolders, like such:
The Examples >
Ins >
Song One O-0001_Instrumental
Song Two O-0002_Instrumental
Vox >
Song One O-0001
Song Two O-0002
Hello Orangeworker,
I have change the main folder path. It is marked in bold. Test this out and let me the results.
Please Login or Register to view this content.
Hi Leith,
The code seems to run in VB -- I don't get any errors, but it doesn't remove the O- and everything after it.
Nothing has changed.
After reading "broro183's" helpful links, maybe we are missing the file extensions? The file extensions below are going to be the only types of files listed in the "Vox" and "Ins" folders.
.aif
.aiff
.mp3
.wav
Hello Orangeworker,
I apologize for the error. It appears the problem is due to a typo I made in the code. The code below has been corrected. The change has been marked in bold type.
Please Login or Register to view this content.
Hi Leith,
No need to apologize; thank you for helping me! I tried the code and am still getting the same result.
I noticed that you added this in the code:
FileName = "Song One O-0001_Instrumental.mp3"
It's not an actual song title but an example. Maybe it would be better to descibe it as:
Example O-XXXX
I just don't know enough about code to understand what you were doing there.
Thanks
Hello Orangeworker,
I am not awake. That was a test line to check that the file name was parsing correctly, which it is. One possible cause would be an incorrect file path. Are the main folder path and the subfolder names correct?
You may have to step through the macro to see what is and isn't happening. Do you know how to insert break points into the code?
Leith,
The only thing I changed in the path was, "E:" to "C:" , for purposes of testing it out first. So, I moved a few folders to "C:\Publishing\Catalog"
I'm not sure how to insert break points into the code, is it something fairly easily understood?
Thanks
Hello Orangeworker,
Inserting Breakpoints
- Open the Visual Basic Editor from Excel using ALT+F11
- In the Project Window, Double Click the module the macro code is in.
- Move the mouse cursor down to the line Name FilePath & vFolder & FileName As FilePath & vFolder & NewFileName.
- Press F9 to set the breakpoint. Pressing F9 again will clear it.
- Run the macro using F5
- Move the mouse cursor to the variable NewFileName. This will display the contents.
- Go to the next file by pressing F5.
Leith,
Got it. Will insert the break points and see what happens. In the meantime, I've attached a PDF snapshot of my directory...should have done this from the beginning.
When I inserted break points and ran the code, I got yellow highlights on the following lines
Please Login or Register to view this content.
Hello Orangeworker,
Unfortunately, colors are not rendered in the code window. You will need to bold the lines or add comments so I know which to look at.
Sorry about that. The bold text is turning yellow when I run the code with the break points.
Please Login or Register to view this content.
Hello Orangeworker,
Did you change the file path? The PDF shows yet another path??? Which am I to believe?
Please Login or Register to view this content.
Hello Orangeworker,
How many break points to add? You only needed to add one where I specified to check if the file path and name were correct. What values did you see?
Okay, this is where we're going wrong. The path listed in the PDF is correct. "Alexander" is an artist, but their are many different artist names (folders) in that section of the directory. Does that clear it up?
Please Login or Register to view this content.
The values listed below are what I'm getting when inserting the break point on
Please Login or Register to view this content.
FilePath="D:\Publishing\Catalog\Alexander\"
FileName="Be Careful What You Wish For O-0007.wav"
vFolder= "Vox"
Hello Orangeworker,
I am still clear on on how you have these directories setup. If you various folders based on the artists names in the main path "D:\Publishing\Catalog\" then there needs to extra step in the macro to open these artists' folders, unless you are looking only in the folders "Vox" and "Ins".
My question is then which path should the macro be using: "D:\Publishing\Catalog\Alexander\Vox" or "D:\Publishing\Catalog\Vox" ? If artist needs to included then I will need to change the macro.
Hi Leith,
Sorry for the miscommunication. What I can do is just list all the file paths for all of my artists. Would the example below work?
Please Login or Register to view this content.
And so on for the other artists..
But with that said, this path still doesn't remove O-*
This is the code I'm using now
Please Login or Register to view this content.
Hello Orangeworker,
I created a directory with the names shown in the PDF. This macro works now. All the file names that matched changed. Copy this as is and run on it on your system.
Please Login or Register to view this content.
That worked
Thanks for your help and patience, Leith
I'm not sure why I always have problems finding where to mark the thread as "solved".
Hello Orangeworker,
I will do it for you.
Thanks again.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks