Rename multiple files using Excel VBA

Question

I am using these two codes:

Get_Files_Information: To pull up the file names from the folder for renaming

Option Explicit

Sub Get_Files_Information()

Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Sheet1")

Dim fso As New FileSystemObject
Dim fo As Folder
Dim f As File

Set fo = fso.GetFolder(sh.Range("H1").Value)

Dim last_raw As Integer

For Each f In fo.Files
     last_raw = sh.Range("A" & Application.Rows.Count).End(xlUp).Row + 1
     sh.Range("A" & last_raw).Value = f.Name
Next

MsgBox "Done"

End Sub

Rename_Files: This code is to rename the file

Sub Rename_Files()

Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Sheet1")

Dim fso As New FileSystemObject
Dim fo As Folder
Dim f As File

Dim new_name As String

Set fo = fso.GetFolder(sh.Range("H1").Value)

For Each f In fo.Files
    new_name = Application.VLookup(f.Name, sh.Range("A:B"), 2, 0)
    f.Name = new_name
Next

MsgBox "Done"

End Sub

When the Get_Files_Information is fetching the file name the result is coming with file extension. I want to exclude the file extension from the file name so that the renaming will not get stuck due to extension of the file.

Also when executing the rename code I get

Type Mismatch Runtime error 13.

on new_name = Application.VLookup(f.Name, sh.Range("A:B"), 2, 0)

Excel macro file for reference.

https://drive.google.com/open?id=1Zivo3aIn-Id9XtgQu-qpOstL_j7eacjv

declare new_name as string... have you tried looking at new_name with Debug.Printto see, what your Lookup returns? — MGP
– MGP, Commented Dec 16, 2019 at 9:59
Code is running for me. I'd recommend to add some example data to the post as like me most people will not download a file from a google drive. — Storax
– Storax, Commented Dec 16, 2019 at 10:42
column A will have the old file name and the column 2 will have new file name i am getting this error while executing the code i.sstatic.net/SEnuN.png — Sameer Farooqui
– Sameer Farooqui, Commented Dec 16, 2019 at 11:19
It would be great if you could add this information to the post, mark the line where the error occurs and add the data in the variables and cells. — Storax
– Storax, Commented Dec 16, 2019 at 11:21

Storax · Accepted Answer · 2019-12-16 13:06:10Z

Try the following code. It will check if Application.VLookup(f.Name, sh.Range("A:B"), 2, 0) returns an error (probably caused by a formula which returned an error like #VALUE)

Sub Rename_Files()

    Dim sh As Worksheet
    Set sh = ThisWorkbook.Sheets("Sheet1")

    Dim fso As New FileSystemObject
    Dim fo As Folder
    Dim f As File

    Dim new_name As String

    Set fo = fso.GetFolder(sh.Range("H1").Value)

    For Each f In fo.Files
        Dim vRes As Variant
        vRes = Application.VLookup(f.Name, sh.Range("A:B"), 2, 0)
        If IsError(vRes) Then
            MsgBox "Cannot rename " & f.Name & " - " & CStr(vRes)
        Else
            new_name = vRes
            f.Name = new_name
        End If
    Next

    MsgBox "Done"

End Sub

This code will also give you a hint what went wrong as it gives you the cell error value. For an extended discussion on vlookup I recommend to have a look at this article

Collectives™ on Stack Overflow

Rename multiple files using Excel VBA

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related