Rename multiple files in a folder with a macro - Existing Macro update

[nobleprince]

Hi,

My original thread is here:
http://www.excelforum.com/excel-prog...h-a-macro.html

I used Bernie Deitrick macro solution. See attached file.

I have multiple files in a folder and I want to rename them automatically via a macro. I use Excel 2016. Could you please update the macro for the following output:

BEFORE

The existing file names are:

Firstname Surname_SOMETEST.html
OR
Firstname Middlename Surname_SOMETEST.html

AFTER

I would like the macro to rename the files to:

Firstname Surname _ Test.html
OR
Firstname Middlename Surname_Test.html

Basically the macro should replace any text after the _ with the word "Test". If there are duplicate files with the same name, the macro should ignore those records.

Could you please help me with the requirement?

Thank you,
Ash

[jolivanes]

To borrow Bernie's code, this should work.
Might be a good idea to try it with copied files in a temp folder first.

Please Login or Register  to view this content.

[nobleprince]

Hi,

I tried only the code you sent above and it did not work. When I run the macro nothing happens. Please check:

Sub RenameFiles()
Dim objShell As Object
Dim objFolder As Object
Dim objFile As Object

Set objShell = CreateObject("Shell.Application")
Set objFolder = objShell.Namespace(ThisWorkbook.Path)

For Each objFile In objFolder.Items
If objFile.Name Like "*.html" Then
objFile.Name = Split(objFile.Name, "_")(0) & "_Test.html"

End If
Next objFile

End Sub

[jolivanes]

Re: "Please check"
Unfortunately there is very little to check at this end because it works as advertised.
Is your excel file with the code in the same folder as all the files that need their name changed?

[nobleprince]

Yes, the file is on the same folder.

Do I need to just use the above code? Or does it need to be used with in conjunction with existing Bernie's Macro?

[jolivanes]

All files, the .htm and the excel file with the code in it, need to be in the same folder.
Open the excel file with the code in it from Post #2 and run it.
Note that it is .htm in the code. If your extension is different, change the two lines to whatever your extensions are.
To answer your question, the code from Post #2 should do what you've requested.

[nobleprince]

Hi, still does not work. I have attached the macro file. I am unable to attach a html file here. You may download any html file from the web to check. Please advise?

[jolivanes]

I don't know what the problem could be. It works like a dandy here.
The only thing I did was save files from outlook in .htm format in the folder. I did not, and will not, download and save from the web.

See if this is any better. There in no error checking in the code so you need to add that.
Only the excel file and the files to be renamed in Folder.

Please Login or Register  to view this content.