batch rename files based on contents of one of its cells.

[vimes]

Greets'

I've got a good one .. i think

Okay, i've got a project, take the 3000 files given to me, and rename each file based on the contents of one of its cells, C4.

So, my vba needs to scan the folder assigned, check out C4, and then rename the wb.

Help!





ps: i've been reading a lot about people wanting to get lists of files from their computers folders. The easiest way to do this is open a command prompt, go to the folder and type

DIR >FILE.TXT

this will create a file called file.txt with a list of everything in the folder.

If you do dir /? you will get a list of commands you can use.. Personally i like the;

dir /b >mp3s.txt

this will just list file names, no size, no nothing extra.

dir /s >allfiles.txt
this will give you all files in a folder and its sub-folders.

anyway, food for thought.

 

[Dan Aragon]

Re: batch rename files based on contents of one of its cells

Well, it's been a long while since I've been to the forum. I saw your post went unanswered since yesterday so allow a rusty old guy to take a crack at it.

First, I'm unaware of any easy way to extract data from a workbook without opening it. I've seen a workaround that requires you to add code to the targeted workbook, but that isn't helpful here. I'm assuming you have a list of the names of the workbooks in a main workbook in cells A1:A3000. We'll call it main.xls. Second, I assume the 3000 workbooks are in a single directory like c:\temp. You can write a small piece of code to open each workbook then do a "Save As". Something like this:
=========================
Sub Rename()
Dim oldName As String
Dim newName As String

Application.ScreenUpdating = False
Workbooks("main.xls").Activate
For i = 1 To 3000
'Get name of file
oldName = "c:\temp\" & Cells(i, 1).Value & ".xls"
Workbooks.Open (oldName)
'Set new name of file
newName = "c:\temp\" & Range("c4").Value & ".xls"
ActiveWorkbook.SaveAs Filename:=newName
ActiveWorkbook.Close
Workbooks("main.xls").Activate
Next i

Application.ScreenUpdating = True
End Sub
===========================

Alternatively, if you really are looking to just rename the workbook, rather than saving a copy under the new name, you can use the Name function instead of the SaveAs function. Like:

============================
Sub Rename()
Dim oldName As String
Dim newName As String

Application.ScreenUpdating = False
Workbooks("main.xls").Activate
For i = 1 to 3000
'Get name of file
oldName = "c:\temp\" & Cells(i, 1).Value & ".xls"
Workbooks.Open (oldName)
'Set new name of file
newName = "c:\temp\" & Range("c4").Value & ".xls"
ActiveWorkbook.Close
Name oldName as newName
Workbooks("main.xls").Activate
Next i

Application.ScreenUpdating = True
End Sub

=====================

I haven't tested these and didn't put in any error checks, but this should get you started.

-Dan