Renaming multiple files 2

[Eitel13]

Hi all,

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:

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.

Heres the code I have:

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
[indent][/indent]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.

​

 

[SkipVought]

Hi,

Well you Exit For before the Echo statement.

Therefore, 1) only ONE file is renamed and 2) the Echo statement never executes.

The Echo statement must be outside of the loop to indicate that the renaming is complete.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[guitarzan]

Replace Exit For with Loop

Ughh I didn't read things clearly, Skip's answer will solve the problem, just remove the Exit For

 

[Eitel13]

Thank you so much! I cannot believe it was something so simple :/

 

[Eitel13]

Hi all,

Just an update to this thread, I got a brilliant working example for future references:

Dim fso, folder, file
Dim folderName

'Path
folderName     = "C:\Users\Desktop\New folder"


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

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

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

 

[mrmovie]

i see the solution which is nice, still not sure why you need to rename the files.
You could just enum files in the folder and open the file with the name Right(strFileName, "_ApproproateName"),,,and ignore the date so to speak

I Hear, I Forget
I See, I Remember
I Do, I Understand

Ronald McDonald