Renaming multiple files
Renaming multiple files
This is my first post, forgive me if there are any issues, I will be happy to rectify them if need be.
Please note, I am not a VBS or VBA programmer, I am learning as I go and at the moment I have been stuck on this for 3 weeks.
I am aware that there are dozens of threads all over Google with SIMILAR queries, and I have been through A LOT, but none of them can specifically answer my query.
I have a folder with 8 excel files with the following naming convention:
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.
Heres the code I have:
CODE --> VBS
Dim fso, folder, file Dim folderName, searchFileName, renameFile1, renameFile2, renameFile3, renameFile4, renameFile5, renameFile6, renameFile7, renameFile8 ' Parameters '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 loop 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.
If the query is unclear or if there is actually a thread already open that can answer this, please do tell.
Thank you in advance. :)