Vba to bulk rename files - Folder and Subfolder as per criteria - Regular Expressions

[Luthius]

In my personal documents I'm using a sort of Naming Convention where all my files are named as follows yyyy-mm-dd-FileName.xyz

• The first characters refer to the Last Modified Date that in many cases represent the Creation Date of the file.
• ".xyz" I don't change it because it is the file extension.
• I don't keep spaces or any special character such as accents or accented characters.

My wife is also using the same computer and she is generating some files there. But sometimes she forgets about the naming convention and spoils my control. Lol.

I would like your support on how to develop a VBScript that can:

1. Remove all spaces of the name by replacing it by "-" Character
2. Replace Accents & Accented Characters for English Equivalent character
3. Verify if the first 11 Characters of the File Name is following the Rule of the Naming Convention yyyy-mm-dd- based on Last Modified date of the file. If NOT then rename it as per my Naming Convention yyyy-mm-dd-FileName.xyz

Ps.: The script will verify all files inside a folder and inside of its subfolder. C:\Users\Juliano\Documents\MrExcelForumExample
Can someone help me to finish the code below? The idea is use Regular Expression to identify spaces and accented characters and rename the files.

Option Explicit
Dim FSO As Object, WindowsFolder As Object, FolderFiles As Object, FoundFile As Object
Dim RegularExpression As Object
Dim TargetFolder As String

Sub RenameFilesAsPerCriteria()
    Set FSO = CreateObject("Scripting.FileSystemObject")
    TargetFolder = "C:\Users\Juliano\Documents\MrExcelForumExample"
    Set WindowsFolder = FSO.GetFolder(TargetFolder)
    Set FolderFiles = WindowsFolder.Files
    Set RegularExpression = New RegExp
    RegularExpression.Global = True
    'RegularExpression.Pattern = white spaces, Accents & Accented Characters


    For Each FoundFile In FolderFiles
        If RegularExpression.Test(FoundFile.Name) = True Then
                '1-Remove Whitespaces replacing it by "-" character
                '2-Replace Accents & Accented Characters for English Equivalent character
                '3-Verify if the file name contains the file name convention yyyy-mm-dd-FileName.xyz
                    '  if NOT rename the file accordingly
        Else: End If
    Next
    VerifySubFolders FSO.GetFolder(TargetFolder)
End Sub

Sub VerifySubFolders(Folder)
    Dim Subfolder As Object
    For Each Subfolder In Folder.SubFolders
        Set WindowsFolder = FSO.GetFolder(Subfolder.Path)
        Set FolderFiles = WindowsFolder.Files
        Set RegularExpression = New RegExp
        RegularExpression.Global = True
        RegularExpression.Pattern = white spaces, Accents & Accented Characters

        For Each FoundFile In FolderFiles
            If RegularExpression.Test(FoundFile.Name) = True Then
                '1-Remove Whitespaces replacing it by "-" character
                '2-Replace Accents & Accented Characters for English Equivalent character
                '3-Verify if the file name contains the file name convention yyyy-mm-dd-FileName.xyz
                    '  if NOT rename the file accordingly
            Else: End If
        Next
        VerifySubFolders Subfolder
    Next
End Sub

 

[Luthius]

Any ideas guys?

 

[Luthius]

The idea is a regular Expression to find these accented characters "ŠŽšžŸÀÁÂÃÄÅÇÈÉÊËÌÍÎÏÐÑÒÓÔÕÖÙÚÛÜÝàáâãäåçèéêëìíîïðñòóôõöùúûüýÿ"
and replace them by english characters "SZszYAAAAAACEEEEIIIIDNOOOOOUUUUYaaaaaaceeeeiiiidnooooouuuuyy"

 

[Dan_W]

Hi. I'm sorry - I meant to reply to this when I saw it a week or so ago, but it completely slipped my mind. Do you still need help with it? I've not dealt with accented characters in a regular expression before, but happy to take a crack at it. Also, your post says VBScript - just to clarify, you're using VBA, no?

 

[Peter_SSs]

I've not dealt with accented characters in a regular expression before, but happy to take a crack at it.

Some of the data in it has not converted well from the old forum to the new, but you might find some helpful things throughout this thread.

 

[Dan_W]

Thank you very much for the steer, Peter. It seems as though quite a lot of work has gone into some of the code in this thread. As you say, this could prove to be helpful.

 

[Luthius]

Hi. I'm sorry - I meant to reply to this when I saw it a week or so ago, but it completely slipped my mind. Do you still need help with it? I've not dealt with accented characters in a regular expression before, but happy to take a crack at it. Also, your post says VBScript - just to clarify, you're using VBA, no?

Yes, I'm still interested to get a solution.
The final code will be allocated under a vbs file. But we can develop it n VBA and I can convert it in vbs afterwards.
Thank you for your time, and if you can assist on this problem, I really appreciate.

 

[Dan_W]

Great. I'll have a re-read tomorrow and let you know if there is any further information I need.

 

[Dan_W]

The idea is a regular Expression to find these accented characters "ŠŽšžŸÀÁÂÃÄÅÇÈÉÊËÌÍÎÏÐÑÒÓÔÕÖÙÚÛÜÝàáâãäåçèéêëìíîïðñòóôõöùúûüýÿ"
and replace them by english characters "SZszYAAAAAACEEEEIIIIDNOOOOOUUUUYaaaaaaceeeeiiiidnooooouuuuyy"

Actually, I do have a quick question, if that's ok - is this the exact range characters you're looking to replace? I ask because I looked at the solutions that Peter helpfully directed me too, and they don't seem to capture all the characters above - it missed about three or four. It's easy enough to fix, but it would useful to know if there are any others you expect you'll need. Thanks.

 

[Luthius]

Actually, I do have a quick question, if that's ok - is this the exact range characters you're looking to replace? I ask because I looked at the solutions that Peter helpfully directed me too, and they don't seem to capture all the characters above - it missed about three or four. It's easy enough to fix, but it would useful to know if there are any others you expect you'll need. Thanks.

Yes. Those are the charachters I want to replace.