Try on a copy of your folder with files first.
Sub Maybe()
Dim c As Range
For Each c In Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).Row) '<----- Assumes data starts in A2
If InStr(c.Value, "_Translated") <> 0 Then c.Value = Left(c, Len(c) - 16) & ".docx"
Next c
End SubAnd without looping maybe.
Sub Maybe_A()
Columns(1).Replace What:="_Translated.docx", Replacement:=".docx", LookAt:=xlPart
End SubBTW, forgot to mention that in the future you're better off attaching a workbook.
That way people that are assisting you can try their suggestion before posting.
And another possibility.
Which one is the best depends on if there is a possibility of mistakes/differences in the naming of the files.
I guess you can always keep them in your library for future use.
Sub Maybe_C()
Dim c As Range
For Each c In Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row)
If InStr(c.Value, "_") <> 0 Then c.Value = Left(c, InStrRev(c.Value, "_") - 1) & ".docx"
Next c
End SubI want to rename a files as a batch, its not working, can you please review once.....
I've used this in the past, it uses 2 macros one to fetch names, the other to rename. Change the folder location and filetype as necessary. Run the first one, it will get your file names, return them to column A, make a copy in Column B, and search/replace _Translated.docx with nothing"". Run the second one it will rename your files. You might want to run a couple tests firsts on some sample files and folders.
Sub GetNames()
Dim myPath As String
Dim MyFile As String
myPath = "C:\filelocation\"
MyFile = Dir(myPath & "*.*")
r = 2
Do While MyFile <> "" Cells(r, 1).Value = MyFile r = r + 1 MyFile = Dir Loop
Columns("A:A").Select Selection.Copy Range("B1").Select ActiveSheet.Paste Columns("B:B").Select Selection.Replace What:="[B]_Translated[/B]", Replacement:="", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Range("B2").Select
End Sub
Sub Rename()
Dim myPath As String
myPath = "C:\filelocation\"
r = 2
Do Until IsEmpty(Cells(r, 1)) And IsEmpty(Cells(r, 2))
Name myPath & Cells(r, 1).Value As myPath & Cells(r, 2).Value
r = r + 1
Loop
End SubPlease review once and update and thank you so much for replying.......
Change references like Folder names and paths where required.
The original files to be moved are in Column A, starting at Row 2.
The nenamed files are in Column B with the original and new name in the same Row.
Sub Maybe()
Dim strPath_Old As String
Dim strPath_New As String
Dim i As Long
strPath_Old = "C:\Temp" '<---- Folder where the files to be renamed are in
strPath_New = "C:\Temp\Temp" '<---- Folder where the files are to be moved into
For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row
Name strPath_Old & "\" & Cells(i, 1) As strPath_New & "\" & Cells(i, 1).Offset(, 1).Value
Next i
End SubDid it work?
I guess this post does not rank under
"If anyone help me out am surely thankful to them but unnecessarily i will not thank....."
Still waiting for either a positive or negative answer.
Don’t have an account yet? Register yourself now and be a part of our community!