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

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
11,739
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,099,248
Messages
5,467,517
Members
406,544
Latest member
semoredhawk

This Week's Hot Topics

Top