Renaming the multiple workbooks on the bases of their respective cell value

Cloud67

New Member
Joined
Mar 13, 2019
Messages
15
Hi,
I receive around 300 files through a software that i use in my office. The problem is that the software saves the files on my computer with the weird name which is meaning less. I would like to save those files with the account number which is located in each file in specific cell (different account number for different file ). Is there any way i could achieve that through macro? I would greatly appreciate the help.
Thanks
 

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
11,272
Office Version
2007
Platform
Windows
Use the following macro.
The macro will take the name of the first page of the book, from cell C2.
Change the data in red for your current data


Code:
Sub rename_books()
    Dim books As Variant
    Dim wPath As String, wName As String, wExt As String
    Dim wb2 As Workbook
    
    Application.ScreenUpdating = False
    wPath = "[COLOR=#ff0000]C:\trabajo\books\[/COLOR]"
    If Right(wPath, 1) <> "\" Then wPath = wPath & "\"
    
    books = Dir(wPath & "*.xls*")
    Do While books <> ""
        Set wb2 = Workbooks.Open(wPath & books)
        wName = wb2.Sheets(1).Range("[COLOR=#ff0000]C2[/COLOR]").Value
        If wName <> "" Then
            wName = Replace(wName, "/", "-")
            wExt = Mid(books, InStrRev(books, ".") + 1)
            wb2.Close False
            Name wPath & books As wPath & wName & "." & wExt
        End If
        books = Dir()
    Loop
    MsgBox "End"
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,096,444
Messages
5,450,482
Members
405,613
Latest member
Arpit

This Week's Hot Topics

Top