Renaming multiple files in a loop

Question

I have a folder with 8 Excel files with the following naming convention:

date_All_Groups
date_HRFull_Status_All
date_RME_Groups_Excluded

These files are used for monthly reports, therefore the date will obviously always be different. I will be using a macro to manipulate the data in each worksheet, however I cannot create the macro due the changing file name (the date) - the only guarantee I have is that each of these files will DEFINITELY contain a partial string match.

I have a script that finds the files in the location and will rename the file, but it only renames 1 file and its not the first file in the folder.

My issue is using the For Each loop effectively.

Here's the code I have:

Dim fso, folder, file
Dim folderName, searchFileName, renameFile1, renameFile2, renameFile3, renameFile4, renameFile5, renameFile6, renameFile7, renameFile8

'Path
folderName = "C:\test\"

'Future FileName
renameFile1 = "All Groups.csv"
renameFile2 = "Groups Excluded.csv"
renameFile3 = "No Exclusions.csv"
renameFile4 = "HR.csv"
renameFile5 = "AD Users.csv"
renameFile6 = "Encryption Status.csv"
renameFile7 = "ePO4 Not Encrypted.csv"
renameFile8 = "ePO5 Not Encrypted.csv"

' Create filesystem object and the folder object
Set fso = CreateObject("Scripting.FileSystemObject")
Set folder = fso.GetFolder(folderName)

' Loop over all files in the folder until the searchFileName is found
For Each file In folder.Files
    ' See If the file starts with the name we search
    If InStr(file.Name, "All_Groups") then
        file.Name = renameFile1
    End If

    If InStr(file.Name, "Groups_Excluded") Then
        file.Name = renameFile2
    End If

    If InStr(file.Name, "No_Exclusions") Then
        file.Name = renameFile3
    End If

    If InStr(file.Name, "HR") Then
        file.Name = renameFile4
    End If

    If InStr(file.Name, "AD_Users") then
        file.Name = renameFile5
    End If

    If InStr(file.Name, "Encryption_Status") then
        file.Name = renameFile6
    End If

    If InStr(file.Name, "ePO4") then
        file.Name = renameFile7
    End If

    If InStr(file.Name, "ePO5") then
        file.Name = renameFile8
    End If

    Exit For

    ' echo the job is completed
    WScript.Echo "Completed!"
Next

The original code I found was exactly as above, but with only one If statement inside the For Each loop and the Exit For was inside the If statement.

Currently when I execute the script, the code renames only one file and its always the HR file first. If I execute the script again, it then starts with All Groups, then Groups Excluded, and so on.

And the "Echo Completed" does not do anything either.

Well, you can start by removing the `Exit For` at the bottom, which tells it to run exactly once and then exit the for loop before it ever reaches `Next`. You can then Google *vbscript file wildcards* to learn how to do partial matches on filenames. — Ken White, Feb 01 '18 at 13:53
It's not clear to me what your desired result is. Do you want to rename the files to the exact names from your `renameFile#` variables (removing the date from the filename)? Why do you want to rename them in the first place? — Ansgar Wiechers, Feb 01 '18 at 15:44
@KenWhite Thank you for the help, it worked! Much appreciated. — Eitel Dagnin, Feb 02 '18 at 05:26
@AnsgarWiechers I just needed the files to be renamed with the names in the renameFile# because each month when these files are generated I will run the script to do the renaming which will allow the macro to work correctly because the filename is an exact match. :) — Eitel Dagnin, Feb 02 '18 at 05:29
Then, as Ken mentioned, all you need to do is remove the `Exit For`, so that your loop won't terminate after the first file. There are more elegant approaches, though, see my answer below. — Ansgar Wiechers, Feb 02 '18 at 09:23

score 1 · Accepted Answer · answered Feb 02 '18 at 09:23

1

If you just want to rename your files to "canonical" names you could do something like this, assuming that you just want the date from the beginning of the filename removed and the underscores replaced with spaces:

Set re = New RegExp
re.Pattern = "\d{4}-\d{2}-\d{2}_(.*\.csv)"

For Each f In folder.Files
    For Each m In re.Execute(f.Name)
        f.Name = Replace(m.Submatches(0), "_", " ")
    Next
Next

answered Feb 02 '18 at 09:23

Ansgar Wiechers

193,178
25
254
328

Thank you, I will give this a try too! :) – Eitel Dagnin Feb 02 '18 at 12:21
Sorry I am only getting back to this now, but it worked brilliantly! Thank you – Eitel Dagnin Feb 20 '18 at 12:06

Ricardo Galain · Answer 2 · 2018-02-01T23:06:14.423

0

If the files have the same "date" you only need Find for that, for excample (if the date is a iso date "YYYYMMDD") (Date Returns "today" date)

IsoDate=CStr(Year(Date)) & Right("0" & CStr(Month(Date)),2) & Right("0" & CStr(Day(Date)),2)

And the for each:

For Each file In folder.Files
    If InStr(file.Name, IsoDate) = 1 then 'if is in the start of the string
        file.Name = Mid(file.Name, Len(IsoDate)+1) 'The same name with out date
    End IF
Next

edited Feb 01 '18 at 23:06

answered Feb 01 '18 at 17:59

Ricardo Galain

152
6

Its not a sure thing that the files will have the same date Some might be extracted today whereas others will be extracted tomorrow.. But nevertheless, my question has been answered above. Thank you for the help! – Eitel Dagnin Feb 02 '18 at 05:32

Renaming multiple files in a loop

2 Answers2