CODEWITHSUNDEEP
vbaexcel
Karan Neginhal
Karan Neginhal

Reputation: 45

Rename Files Using List in Excel

I am trying to rename a file using based on a list in excel such that column A has old file name and Column B has new file name. It worked for some files but its not renaming some files. THis is the macro that I used:

Sub RenameFiles()

Dim xDir As String

Dim xFile As String

Dim xRow As Long

With Application.FileDialog(msoFileDialogFolderPicker)

    .AllowMultiSelect = False

If .Show = -1 Then

    xDir = .SelectedItems(1)

    xFile = Dir(xDir & Application.PathSeparator & "*")

    Do Until xFile = ""

        xRow = 0
        On Error Resume Next
        xRow = Application.Match(xFile, Range("A:A"), 0)
        If xRow > 0 Then
            Name xDir & Application.PathSeparator & xFile As _
            xDir & Application.PathSeparator & Cells(xRow, "B").Value
        End If
        xFile = Dir
    Loop
End If
End With
End Sub

Here is a screenshot of my excel file names:

SCREENSHOTOFEXCEL

Can anyone tell me whats the issue here ? Any help would be appreciated. Thanks in advance

Upvotes: 1

Views: 6081

Answers (1)

kamleshrao
kamleshrao

Reputation: 435

I have undergone with similar requirement. I solved it using bit different approach.

Here's is exactly what I did:

1) I create Excel similar to yours. 2) Column A will have Old Filename 3) Column B will have New Filename 4) I will write DOS Command in Column C, using Excel Formula.

Once done, I will copy the data from Column C and create a BATCH File out of it. Once I run the BATCH File, it gets my work done. This formula assumes that you don't have any special characters.

Sharing the formula that I wrote in Column C:

="REN " & CHAR("34") & A2 & CHAR(34) & " " & CHAR(34) & B2 & "" & CHAR(34)

And also adding screenshot of my Excel as reference. enter image description here

Upvotes: 2

Related Questions