Remove Suffix From Files

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

Hello Orangeworker,

This macro will rename the files with the "O-"suffix in the folders "Vox" and "Ins".

Sub RenameFiles()

  Dim FileName As String
  Dim FilePath As String
  Dim NewFileName As String
  Dim RegExp As Object
  Dim vFolder As Variant
  
    FilePath = "E:\Publishing\Catalog\artist\"
    
    Set RegExp = CreateObject("VBScript.RegExp")
    RegExp.IgnoreCase = True
    RegExp.Pattern = "(.+)(O\-.+)(\..*)"
        
      For Each vFolder In Array("Vox", "Ins")
        FileName = Dir(FilePath & vFolder & "*.*")
          While FileName <> ""
            If RegExp.Test(FileName) Then
               NewFileName = RegExp.Replace(FileName, "$1$3")
               Name FilePath & vFolder & FileName As FilePath & vFolder & NewFileName
            End If
            FileName = Dir()
          Wend
      Next vFolder
    
End Sub

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:

Sub tst()
  With CreateObject("scripting.filesystemobject")
    For Each fl In .getfolder("E:\publishing\Catalog\Artist").subfolders
      c01 = Dir(fl & "\*O-*.*")
      Do While c01 <> ""
        Name fl & "\" & c01 As Split(c01, "O-")(0) & "." & .getextensionname(fl & "\" & c01)
        c01 = Dir
      Loop
    Next
  End With
End Sub

Hello Orangeworker,

I have change the main folder path. It is marked in bold. Test this out and let me the results.

Sub RenameFiles()

  Dim FileName As String
  Dim FilePath As String
  Dim NewFileName As String
  Dim RegExp As Object
  Dim vFolder As Variant
  
    FilePath = "E:\Publishing\Catalog\"
    
    Set RegExp = CreateObject("VBScript.RegExp")
    RegExp.IgnoreCase = True
    RegExp.Pattern = "(.+)(O\-.+)(\..*)"
        
      For Each vFolder In Array("Vox", "Ins")
        FileName = Dir(FilePath & vFolder & "*.*")
          While FileName <> ""
            If RegExp.Test(FileName) Then
               NewFileName = RegExp.Replace(FileName, "$1$3")
               Name FilePath & vFolder & FileName As FilePath & vFolder & NewFileName
            End If
            FileName = Dir()
          Wend
      Next vFolder
    
End Sub

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.

Sub RenameFiles()

  Dim FileName As String
  Dim FilePath As String
  Dim NewFileName As String
  Dim RegExp As Object
  Dim vFolder As Variant
  
    Set RegExp = CreateObject("VBScript.RegExp")
    RegExp.IgnoreCase = True
    RegExp.Pattern = "(.+)(O\-.+)(\..*)"
        
    FilePath = "E:\Publishing\Catalog\"
    FileName = "Song One O-0001_Instrumental.mp3"
    
    RegExp.Test (FileName)
    NewFileName = RegExp.Replace(FileName, "$1$3")
    
    
     For Each vFolder In Array("Vox", "Ins")
        FileName = Dir(FilePath & vFolder & "\*.*")
          While FileName <> ""
            If RegExp.Test(FileName) Then
               NewFileName = RegExp.Replace(FileName, "$1$3")
               Name FilePath & vFolder & FileName As FilePath & vFolder & NewFileName
            End If
            FileName = Dir()
          Wend
      Next vFolder
    
End Sub

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?

Hello Orangeworker,

Inserting Breakpoints

1. Open the Visual Basic Editor from Excel using ALT+F11
2. In the Project Window, Double Click the module the macro code is in.
3. Move the mouse cursor down to the line Name FilePath & vFolder & FileName As FilePath & vFolder & NewFileName.
4. Press F9 to set the breakpoint. Pressing F9 again will clear it.
5. Run the macro using F5
6. Move the mouse cursor to the variable NewFileName. This will display the contents.
7. Go to the next file by pressing F5.

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.