Results 1 to 2 of 2

Thread: Renaming the multiple workbooks on the bases of their respective cell value
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Mar 2019
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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

  2. #2
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    4,028
    Post Thanks / Like
    Mentioned
    38 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Renaming the multiple workbooks on the bases of their respective cell value

    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 = "C:\trabajo\books\"
        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("C2").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
    Regards Dante Amor

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •