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

Luthius

Active Member
Joined
Apr 5, 2011
Messages
318
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.


VBA Code:
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
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

Luthius

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

Dan_W

Active Member
Joined
Jul 11, 2018
Messages
423
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,537
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Active Member
Joined
Jul 11, 2018
Messages
423
Office Version
  1. 365
Platform
  1. Windows
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

Active Member
Joined
Apr 5, 2011
Messages
318

ADVERTISEMENT

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

Active Member
Joined
Jul 11, 2018
Messages
423
Office Version
  1. 365
Platform
  1. Windows
Great. I'll have a re-read tomorrow and let you know if there is any further information I need.
 

Dan_W

Active Member
Joined
Jul 11, 2018
Messages
423
Office Version
  1. 365
Platform
  1. Windows
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

Active Member
Joined
Apr 5, 2011
Messages
318
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,816
Messages
5,638,503
Members
417,030
Latest member
baqer

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top