Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: File Renaming
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Jul 2007
    Location
    Australia, NSW
    Posts
    829
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default File Renaming

    I am trying to macro a painful process of renaming around 30 files from something like filename.csv to filename pay 21 07/08.csv help please this is painfully slow to do manually

  2. #2
    Board Regular Blade Hunter's Avatar
    Join Date
    Mar 2008
    Location
    Sydney, Australia
    Posts
    3,147
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: File Renaming

    What is the logic for the new names of the files?
    .select, overused and inefficient.

  3. #3
    Board Regular
    Join Date
    Jul 2007
    Location
    Australia, NSW
    Posts
    829
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: File Renaming

    I was going to add another section in the middle for each pay.. pays are fortnightly based on the financial year, so we are currently on pay 21, financial year won't need to be updated regularly

  4. #4
    Board Regular Blade Hunter's Avatar
    Join Date
    Mar 2008
    Location
    Sydney, Australia
    Posts
    3,147
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: File Renaming

    You can either use some VBA code to do the rename or if it is a one off, what I usually do is go to a dos prompt and type
    Code:
    dir /b *.csv >go.bat
    Once that is done open the resulting bat file in notepad.

    Copy everything and dump it in excel, use simple text string manipulation formulas to write a rename command per line.

    Paste the results back into the file in notepad, close and save then double click.

    Its quick and it works.

    If you are doing it through VBA then you will need to use the
    Code:
    Name "original file" as "new file"
    command.

    Here is some example code I have done recently to scan the contents of a DIR into an array (I was doing WAV files, you can change it to suit your needs)
    Code:
    WAVCounter = 1
    WAVFile = Dir$(DirName & "\*.WAV")
    Do While WAVFile <> ""
        ReDim Preserve WAVListArray(WAVCounter)
        WAVListArray(WAVCounter) = WAVFile
        WAVFile = Dir$
        WAVCounter = WAVCounter + 1
    Loop
    I would then have something like:
    Code:
        For X = 1 To UBound(WAVListArray)
            Name WavListArray as NewFileName
        Next
    Hope that helps.

    Dan
    .select, overused and inefficient.

  5. #5
    Board Regular
    Join Date
    Jul 2007
    Location
    Australia, NSW
    Posts
    829
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: File Renaming

    Hi Dan a little bit new to bat files can you show me an example

  6. #6
    Board Regular Blade Hunter's Avatar
    Join Date
    Mar 2008
    Location
    Sydney, Australia
    Posts
    3,147
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: File Renaming

    Quote Originally Posted by Ed in Aus View Post
    Hi Dan a little bit new to bat files can you show me an example
    If you are new to them I would reccomend you go with the VBA code instead. A bat file is a sequential list of dos commands for the system to process one after the other.

    The construct of batch file would be something like:
    Code:
    rename "file1.csv" "newfilename.csv"
    rename "file2.csv" "another new name.csv"
    rename "third file.csv" "yet another new name.csv"
    So if we have the current names in column A
    example:
    Code:
    adde_20061123_014500.csv
    adde_20061130_014500.csv
    adde_20061207_014501.csv
    adde_20061214_014501.csv
    adde_20061221_014500.csv
    adde_20061228_014501.csv
    And we have the new names in Column B
    example:
    Code:
    MyNewFileName.csv
    SomethingElse.csv
    blah blah blah.csv
    MyNewFileName2.csv
    MyNewFileName3.csv
    blah blah blah2.csv
    The formula we enter to build our dos rename call is:
    Code:
    ="rename """ & A1 & """ """ & B1 & """"
    This gives us a result of
    Code:
    rename "adde_20061123_014500.csv" "MyNewFileName.csv"
    rename "adde_20061130_014500.csv" "SomethingElse.csv"
    rename "adde_20061207_014501.csv" "blah blah blah.csv"
    rename "adde_20061214_014501.csv" "MyNewFileName2.csv"
    rename "adde_20061221_014500.csv" "MyNewFileName3.csv"
    rename "adde_20061228_014501.csv" "blah blah blah2.csv"
    All ready to paste into a .BAT file and double click.

    But like I said, if you have not played around with these before it is probably best to stick with the VBA code.
    .select, overused and inefficient.

  7. #7
    Board Regular
    Join Date
    Jul 2007
    Location
    Australia, NSW
    Posts
    829
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: File Renaming

    Thanks Dan

  8. #8
    Board Regular
    Join Date
    Jun 2005
    Location
    London
    Posts
    7,761
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    1 Thread(s)

    Default Re: File Renaming

    Here's a file which contains a macro to rename files. You can do them individually or several at a time, providing they're in the same folder.
    http://www.glynnconsulting.co.uk/RenameFiles.zip
    Neil

  9. #9
    Board Regular
    Join Date
    Jul 2007
    Location
    Australia, NSW
    Posts
    829
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: File Renaming

    Thanks the file looks perfect now just have to get around the macro security at work

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

    Default Re: File Renaming

    Hi Dan,

    I am trying to have a list of PDF files renamed, there are thousands and renaming them manually is a pain.

    So i tried to use the concatenate command but it still did not work.

    Is there a way in Excel that we can do this if i have a list of my old file names and the new ones i want to rename it to ?

    Could you please give me clear and simple instructions.

    Thanks
    Suzie

Some videos you may like

User Tag List

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
  •