File Renaming Macro
Solved
catlea
Posted messages
1
Status
Member
-
Binzen -
Binzen -
Hello,
I need to rename a large number of files. I have the old name and the new name in an Excel sheet. There has to be a way to automatically rename these files from the Excel data.
I found the following VBA code on one of the forums as it seems suitable for my case.
Sub Ed()
Dim Path As String, File As String, Line As Integer
Dim OldName As String, NewName As String
Path = "C:\Folder1\Folder2\FolderX\"
For Line = 2 To 402
OldName = Range("A" & Line).Value
NewName = Range("B" & Line).Value
File = Dir(Path & OldName)
If File = Empty Then
MsgBox "the file " & OldName & " was not found"
Else
Name File As NewName
End If
Next Line
End Sub
Of course, I have customized what needed to be customized: the path where the files are located, the lines from the Excel file to be taken into account, and the columns where the old and new names are.
However, it doesn't work, and I don't understand why. I should mention that my files have different extensions.
Thank you for your help
Configuration: Windows XP / Internet Explorer 7.0
I need to rename a large number of files. I have the old name and the new name in an Excel sheet. There has to be a way to automatically rename these files from the Excel data.
I found the following VBA code on one of the forums as it seems suitable for my case.
Sub Ed()
Dim Path As String, File As String, Line As Integer
Dim OldName As String, NewName As String
Path = "C:\Folder1\Folder2\FolderX\"
For Line = 2 To 402
OldName = Range("A" & Line).Value
NewName = Range("B" & Line).Value
File = Dir(Path & OldName)
If File = Empty Then
MsgBox "the file " & OldName & " was not found"
Else
Name File As NewName
End If
Next Line
End Sub
Of course, I have customized what needed to be customized: the path where the files are located, the lines from the Excel file to be taken into account, and the columns where the old and new names are.
However, it doesn't work, and I don't understand why. I should mention that my files have different extensions.
Thank you for your help
Configuration: Windows XP / Internet Explorer 7.0
5 answers
-
Hello
I wanted to use this macro, but I admit that I am a bit struggling since I know nothing about it.
I have an Excel table with an "old name" column and a "new name" column.
I took the macro as follows:
Sub Ed()
Dim Path As String, File As Variant, Line As Integer
Dim OldName As String, newName As String
Dim Source As String, Destination As String
Dim objFSO As Object
Dim ShortPath As String
Path = "C:\Folder1"
ShortPath = "C:\Folder1"
ChDrive "C"
ChDir ShortPath
Set objFSO = CreateObject("Scripting.FileSystemObject")
With Sheets("Sheet1")
'For Line = 2 To 402
For Line = 2 To 62
OldName = .Range("A" & Line).Value
newName = .Range("B" & Line).Value
File = Dir(Path & OldName, 6)
If File = Empty Then
MsgBox "the file " & OldName & " was not found"
Else
Source = Path & File: Destination = Path & newName
objFSO.CopyFile Source, Destination
Kill (Path & OldName)
End If
Next Line
End With
Set objFSO = Nothing
End Sub
The macro returns that the old file name was not found.
My files are in c:\Folder1
Thank you for your help
Phil
