File Renaming Tool - Working in Subfolders

[Amberama]

Hi all, and thank you in advance for your time.

I have created a file renaming tool using Excel and VBA. Basically it will take an unlimited amount of files in a folder and rename them according to a pre-defined data-set. It won't just add or swap a piece of the name like most rename tools, but allow us to use data that we have to rename 1k+ files in one go. The Excel sheet has an "Old Name" column, a "New Name" Column, a user defined field for the folder/filepath and a macro run button.

As an example let's say I need to rename files from 1, 2, 3 and 4 to A, B, C, and D and all the files are in the same folder. The code (below) I've cobbled together will do it.

However if files 1 and 2 are in the specified folder, but file 3 is in a subfolder and file 4 in a subfolder of that, how can I get the code to look in those folders for files to rename?

Thanks again for any suggestions or tips!

Working code (but only in specified folder and not subfolders yet):

Sub Rename()
 On Error Resume Next
 strPath = Range("C2") 'this is the user defined filepath taken from the sheet
 Dim r As Long
 Dim n As Long
 n = Cells(Rows.Count, 1).End(xlUp).Row
 For r = 2 To n
 Name strPath & Cells(r, 1) As strPath & Cells(r, 2)
 Next r
 On Error GoTo 0
End Sub

[patel]

mayby this will be useful

Sub ShowSubFolderList1()
   Dim objFSO, destRow As Long
   Dim mainFolder, mySubFolder
Set objFSO = CreateObject("Scripting.FileSystemObject")
   CARTEL = "D:\DATI\prova\"
   Set mainFolder = objFSO.GetFolder(CARTEL)
   destRow = 7
   For Each mySubFolder In mainFolder.subfolders
      destRow = destRow + 1
      Cells(destRow, 1) = mySubFolder
   Next
End Sub

[Kenneth Hobs]

Welcome to the forum!

Try posting a short example workbook or explain a bit more. You want to move the first found source file to a new location and rename the base name part? When looking for the source file from the parent folder, you can have 3 cases: (1) file is in parent folder, (2) file is in a subfolder, and (3) no file with that name exists.

DOS methods can be used to quickly find files with a base name in the parent and subfolders. SNB will probably jump in here and show you how before I get time to show you. e.g. Dir t:\a\t.txt /s/b

[snb]

 
Sub Rename_snb() 
sn=thisworkbook.sheets(1).cells(1).currentregion
sp=split(createobject("wscript.shell").exec("cmd /c dir " & sn(2,3) & "/b /s").stdout.readall,vbcrlf) 
for j=1 to ubound(sn)
st=filter(sp,sn(j,1))
if ubound(st)>-1 then name st(0) as replace(st(0),"\" & sn(j,1),"\" & sn(j,2))
next
End Sub

[Kenneth Hobs]

Whatever you do, before trying solutions, be sure to backup your data in case it does something that you didn't want.

[Amberama]

Thanks for the welcome Kenneth. And thank you all for replying.

I have attached the test spreadsheet, not the prettiest thing in the world yet, but functioning! To use it you would need a little test sample of files too (in my test environment they are .jpgs but could be anything). Here is a bit more info.

In my example, we have actual files which are uniquely named 1.jpg, 2.jpg, 3.jpg etc....

We have a database which lists all of our files, and holds other metadata fields. One of those fields is an alternative identifier/name, and we now need to change the file name of the jpgs from 1.jpg to the alternative identifier, which in my example is A.jpg.

As the database holds both sets of data, current and new number, I thought we could use that data in a macro to rename all files in a click of a button. Previously it was expected we manually renamed each file. Very time consuming!

So the user would identify the folder where the files to be renamed live, and enter the path into Cell C2 (this field is variable to allow future use wherever we need it, and to make it easy for users.

The user would extract the data from the database and insert into the rename sheet - column A for the current file name, and column B for the new /alternative filename.

The macro would then look in the user specified folder/filepath for the files listed in column A, when it finds one it would rename that file according to the entry in column B. Then it will move on until it can find no more files to rename.

This process seems to work flawlessly when all the files are neatly in one folder, however many thousands of the files are in multiple layers of subfolders. Therefore what I would love to do is have the same renaming functionality offered by the current code, but have it look to see if 1.jpg exists in any subfolders of the specified path as well, and rename the file A.jpg.

Basically I need:

• A sheet with a user specified filepath and user populated new and old filename.
• A code that finds the files listed in column A within the specified filepath OR any subfolders of that filepath and;
• When it finds that file, renames it according to the corresponding entry in column B.
• Repeat until complete!

Sorry for not making it clearer, and thank you for your time!

[snb]

So what were the results when you were testing my code suggestion ?

[shrivallabha]

I guess this thread is similar to your requirements:
http://www.vbaexpress.com/forum/showthread.php?t=36352

[Amberama]

So what were the results when you were testing my code suggestion ?

I was going to test your code this morning SNB. I have to say I'm not totally sure how it locates my files/limits it to a specific folder. Just a bit nervous of testing a renaming code unless I understand it!

Thanks so much for your reply.

[shrivallabha]

I was going to test your code this morning SNB. I have to say I'm not totally sure how it locates my files/limits it to a specific folder. Just a bit nervous of testing a renaming code unless I understand it!

Thanks so much for your reply.

Make a backup copy. And then do the testing, you will be less nervous.

[Amberama]

Make a backup copy. And then do the testing, you will be less nervous.

This may sound stupid (ok, it DOES sound stupid), but because I don't understand where it will look for the data, I don't know what to back up. Does part of SNBs code point to a location or filepath? Whilst I know that I have file 1.jpg etc, there may be many more elsewhere on the network that I'm not in control of and would not want to alter. I only need the renaming to work on a limited folder (and subfolders). So that I understand, which part of the code has that function? It's frustrating being dumb at this, because where I understand the code I've put together, I don't understand this, and I'd like to!

[Kenneth Hobs]

If you step through the code with F8, it should be obvious what is doing.

Did you get what the first line does?

SNB likes to use DOS-like methods.

1. WScript.Shell enables a shell to the DOS environment. You can do this manually by clicking the Start button. Type "cmd" less qoutes and press Enter key. We are now at the DOS prompt.
2. Now type "help cmd" and press the Enter key. Now you can see what the /c command line switch does.
3. Type "help dir" to see the command line switch help for /b and /s.
4. stdout gets the string output of that shell environment.
5. sn(2,3) is like cells(). It contains the (rows,columns). So Range("C2") would be sn(2,3). That is your parent path. Change the value to your testing backup folder.
6. Split() is splitting the string value from the dir command into a one dimensional array separated by the crlf characters.
7. The other commands are just standard VBA commands. With the cursor in or next to a command word in the VBE, press F1 to get help for it.

[Amberama]

So what were the results when you were testing my code suggestion ?

I have had a go at testing this code this morning. It basically flashes the command line and closes it down. Stepping through, it seems to loop the correct amount of times according to the amount of populated rows in column A but doesn't change the name of the files to the corresponding entry in column B (in the pathed folder or subfolder).

I have double checked the file path (cell C2) and tried dropping the trailing backslash to no avail.

No errors are reported. Any suggestions?

Thanks again SNB, Kenneth and shrivallabha.

[snb]

You can build your own magnifyng glasses:

 
Sub Rename_snb() 
  sn=thisworkbook.sheets(1).cells(1).currentregion 
msgbox thisworkbook.sheets(1).cells(1).currentregion.address
sp=split(createobject("wscript.shell").exec("cmd /c dir " & sn(2,3) & "/b /s").stdout.readall,vbcrlf) 
msgbox "numer of items: " & ubound(sq) +1
for j=1 To UBound(sn) 
    st=filter(sp,sn(j,1)) 
    msgbox "the file " & st(0) & " will be replaced by " &  replace(st(0),"\" & sn(j,1),"\" & sn(j,2)) 
If UBound(st)>-1 Then name st(0) As replace(st(0),"\" & sn(j,1),"\" & sn(j,2)) 
  Next 
End Sub

[Amberama]

You can build your own magnifyng glasses:

 
Sub Rename_snb() 
  sn=thisworkbook.sheets(1).cells(1).currentregion 
msgbox thisworkbook.sheets(1).cells(1).currentregion.address
sp=split(createobject("wscript.shell").exec("cmd /c dir " & sn(2,3) & "/b /s").stdout.readall,vbcrlf) 
msgbox "numer of items: " & ubound(sq) +1
for j=1 To UBound(sn) 
    st=filter(sp,sn(j,1)) 
    msgbox "the file " & st(0) & " will be replaced by " &  replace(st(0),"\" & sn(j,1),"\" & sn(j,2)) 
 
If UBound(st)>-1 Then name st(0) As replace(st(0),"\" & sn(j,1),"\" & sn(j,2)) 
  Next 
End Sub

Morning (or evening!) all,

At the line beginning "sp = Split..." a message box containing the range of populated cells appears ($A$1:$H$13) and then at the line "MsgBox "numer of items: " & UBound(sq) + 1" it states a runtime error (13 - type mismatch) and runs the debugger.

Anything more I can test here?

[snb]

Please do not Quote !

2 typos:
msgbox "number of items: " & UBound(sp) +1

[Amberama]

Sorry, lesson learned!

I made the two changes and it moves on to report the range ($A$1:$H$13 etc.) then the number of items (which it reports as 0) and then at the line *msgbox "the file " & st(0) & " will be replaced by " & replace(st(0),"\" & sn(j,1),"\" & sn(j,2))* it stops and runs the debugger stating Run time error 9, subscript out of range. No files are renamed.

Thank you snb, much appreciated.

[snb]

Please dive into the fundamentals of VBA first.

[Amberama]

If only I had the time to gain enough understanding right now. I'll keep trying. Thanks very much for your help any way.