+ Reply to Thread
Results 1 to 9 of 9

Rename Files in A folder

  1. #1
    Registered User
    Join Date
    09-11-2012
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    11

    Rename Files in A folder

    Hi

    I need help to make a macro using Excel 2010 to rename All files (30) in a folder which is updated daily with files having current Date and Time its name, I have to rename these files to the required format as shown below:

    Current File Name ...... Required File Name
    ABC(09112012 0719).xls ...... ABC.xls
    EFG(09112012 0720).xls ...... EFG.xls
    HIJ(09112012 0720).xls ...... HIJ.xls

    Each file name can be delimited using "(" , which may help making macro.

    Thanks

  2. #2
    Forum Expert
    Join Date
    11-29-2010
    Location
    Ukraine
    MS-Off Ver
    Excel 2019
    Posts
    4,168

    Re: Rename Files in A folder

    hi pradeepjassal, try this:

    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    09-11-2012
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Rename Files in A folder

    I tried Using the code given by you but it is giving "Rename has been done though some files have not been renamed due file exists with the same name:", but there is no such conflict in the picked folder.

  4. #4
    Forum Expert
    Join Date
    11-29-2010
    Location
    Ukraine
    MS-Off Ver
    Excel 2019
    Posts
    4,168

    Re: Rename Files in A folder

    if there is no error there will be message for confirmation on successful renaming. Actually error message means not only that file with the same name exists but also different file name format and as a result incorrect file name. So the file exists message error was not fully accurate. I've added error description to the error message for each file.

    Please Login or Register  to view this content.
    Last edited by watersev; 09-12-2012 at 03:16 AM.

  5. #5
    Registered User
    Join Date
    09-11-2012
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Rename Files in A folder

    Thanks for your support....
    Following the procedure of the macro provided I am giving the path of the Folder in which the files are to be renamed but In addition to the previous error message "Rename has been done though some files have not been renamed due file exists with the same name:", additional info as "Subscript Out of Range" is coming twice.
    But the none of the files has been renamed.

  6. #6
    Forum Expert
    Join Date
    11-29-2010
    Location
    Ukraine
    MS-Off Ver
    Excel 2019
    Posts
    4,168

    Re: Rename Files in A folder

    what are the current file names to be renamed in real life that the code gives error on?

  7. #7
    Forum Expert
    Join Date
    11-29-2010
    Location
    Ukraine
    MS-Off Ver
    Excel 2019
    Posts
    4,168

    Re: Rename Files in A folder

    the code was written basing on the fact that "(" and ")" are both present in the file name. I assume that's not the case though your post #1 sets that very format.
    If I'm right try this option that uses "." to get file extension instead of closing bracket ")":

    Please Login or Register  to view this content.
    Last edited by watersev; 09-12-2012 at 04:06 AM.

  8. #8
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Rename Files in A folder

    Quote Originally Posted by pradeepjassal View Post
    Hi

    I need help to make a macro using Excel 2010 to rename All files (30) in a folder which is updated daily with files having current Date and Time its name, I have to rename these files to the required format as shown below:

    Current File Name ...... Required File Name
    ABC(09112012 0719).xls ...... ABC.xls
    EFG(09112012 0720).xls ...... EFG.xls
    HIJ(09112012 0720).xls ...... HIJ.xls

    Each file name can be delimited using "(" , which may help making macro.

    Thanks
    Try this
    It only rename the file that has
    (8 digits number space 4 digits number)
    just in front of extension.
    So such like "ABC(09112012 0719)D.xls" will not be renamed.
    Please Login or Register  to view this content.

  9. #9
    Registered User
    Join Date
    09-11-2012
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Rename Files in A folder

    Quote Originally Posted by watersev View Post
    the code was written basing on the fact that "(" and ")" are both present in the file name. I assume that's not the case though your post #1 sets that very format.
    If I'm right try this option that uses "." to get file extension instead of closing bracket ")":

    Please Login or Register  to view this content.
    Hi Watersev

    Thanks once again
    The code provided by you is working after getting the list of the files in the excel , and the Name of the files in the folder is Updated to the required format.
    Just the extra thing I did was writing a code to get the list current name of the files present in the folder.

    Sub HyperlinksToDirectory()
    ' puts hyperlinks to each of the files in a directory of your choice
    ' into the active sheet starting at the active cell
    Dim stDir As String
    Dim stFile As String
    Dim R As Range
    Set R = ActiveCell
    stDir = InputBox("Directory?", , Default:=CurDir())
    stFile = Dir(stDir & "\*.*")
    Do Until stFile = ""
    R.Hyperlinks.Add R, stDir & "\" & stFile, , , stFile
    Set R = R.Offset(1)
    stFile = Dir()
    Loop
    R.CurrentRegion.Sort key1:=R, order1:=xlAscending, Header:=xlNo
    End Sub

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1