×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Renaming multiple files
2

Renaming multiple files

Renaming multiple files

(OP)
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:

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. :)

RE: Renaming multiple files

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Renaming multiple files

Replace Exit For with Loop

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

RE: Renaming multiple files

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

RE: Renaming multiple files

(OP)
Hi all,

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

CODE --> vbs

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 

RE: Renaming multiple files

i see the solution which is nice, still not sure why you need to rename the files.afro
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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close