rename files based on excel spreadsheet

[harleyberger]

I have 500 .avi files that I need to rename. The files are titled, for example, 2345678.avi. In an Excel spreadsheet I have 2 columns of data. In column A is the current filename, 2345678.avi. In the corresponding cell in column B I have the actual filename that I want to rename the file to, for example, COKE_AD1.avi.

Does anyone know of a file renaming program that will allow me to do something like this?

 

[Glaswegian]

Hi

Have a look at this thread - a similar situation.

http://www.mrexcel.com/board2/viewtopic.php?t=138484&highlight=

Regards

 

[harleyberger]

Sorry, but I'm a novice when it comes to macros. Any help you can give to my particular situation would be appreciated.

 

[Glaswegian]

Hi

Are all the files in just one directory? What is the path name - i.e. something like C:\Media\My Files or whatever? And you want to keep them in the same directory?

Regards

 

[harleyberger]

Yes, all of the files are in one directory on my C: drive. They can stay in the same directory I just need the file names changed.

 

[Glaswegian]

OK try this. You'll need to change the path - I just used D:\Test as a test. Also change the sheet name to match your own. Just in case you're not sure where to put the code follow these steps
1. Press Alt+F11 from Excel to open the VB Editor.
2. Click on the Project(Workbook) name in the left pane.
3. Click on ‘Insert’ on the Menu Bar
4. Select ‘Module’ from the list. The new Module will open.
5. Paste the code into the right pane of the Module.
Now press Alt+F8 to open the macro dialogue box and then select the macro name.

I would suggest copying some files to a test folder and then run the code on that folder first, just to check all is OK. Copy the same file names to another sheet in your workbook, so that the file names match the copied files. If it works OK then you can run it on your main list.

Sub RenameFiles2()
Dim oldName As String
Dim myfile As String
Dim newName As String
Dim myPath As String
Dim x As Integer
Dim boFlag

boFlag = True
myPath = "D:\Test"
x = 1
Do
    oldName = Sheets("Sheet2").Cells(x, 1)
    newName = Sheets("Sheet2").Cells(x, 2)
        If oldName = "" Then
            boFlag = False
            Exit Do
        End If
        myfile = Dir(myPath & "\" & oldName)
        Do Until myfile = ""
            Name myPath & "\" & oldName As myPath & "\" & newName
            myfile = Dir
        Loop
    x = x + 1
Loop
End Sub

I tested on some .wav files and it worked OK.

HTH

Regards

 

[harleyberger]

Thanks for your help, but I followed your instructions and ran the macro and the filenames are still the same as they were.

Here's specifically what I have....

1. Sheet name is creativeids.
2. In cell A1 of creativeids worksheet I have the filename exactly as I want it to appear, TOB_CDC_WORKERS.avi
3. In cell B1 of creativeids worksheet I have the filename as it appears now on my hard drive, 1023683.avi
4. I have a list of about 500 of these that I need to change that follow the same format as above.

When I run this, I want 1023683.avi to be changed to TOB_CDC_WORKERS.avi

 

[Glaswegian]

Hi

Your original post said the existing file name was in column A and the new name was in column B. Try this amended version - remember to change the path to the folder on your hard drive as required.

Sub RenameFiles2()
Dim oldName As String
Dim myfile As String
Dim newName As String
Dim myPath As String
Dim x As Integer
Dim boFlag

boFlag = True
myPath = "C:\"
x = 1
Do
oldName = Sheets("creativeids").Cells(x, 2)
newName = Sheets("creativeids").Cells(x, 1)
If oldName = "" Then
boFlag = False
Exit Do
End If
myfile = Dir(myPath & "\" & oldName)
Do Until myfile = ""
Name myPath & "\" & oldName As myPath & "\" & newName
myfile = Dir
Loop
x = x + 1
Loop
End Sub

HTH

Regards

 

[harleyberger]

Still not working. In your code you have quite a few "\". I'm not sure what those are used for. As I said, my column A has the filenames exactly as I want them to appear. Column B has the filenames exactly as they appear now.

 

[Glaswegian]

The "\" is the path separator. It separates files and folders. For example if you had a file called "Test.xls" in a folder called "Mine" on your C drive, the path would look like

C:\Mine\Test.xls

If your files are on the C drive but not in a folder then change these lines

myfile = Dir(myPath & "\" & oldName) 
Do Until myfile = "" 
Name myPath & "\" & oldName As myPath & "\" & newName

to these

myfile = Dir(myPath & oldName)
Do Until myfile = ""
Name myPath & oldName As myPath & newName

Regards