Renaming Files (Using Excel Spreadsheet)

[WeaponX 0]

Hi, I want to use filenames on my Excel Spreadsheet for the files I have in my folder.For example, let's say I have these in a folder:Reportdummy.docCharts102.xlsDecemberGraphs.xlsTestScrap.txtetc...And I have this in my Excel spreadsheet:Report1.docCharts.xlsGraphs.xlsScrap.txtetc...Is there an easy way for me to copy the cell's value to the file in that folder? I want them to be copied exactly in that order. So far I have to do this manually (copy from Excel, then rename and paste for each file).Thanks.

[doudou 0]

I guess I don’t quite understand your question., maybe you can clarify. When you said you have files in your excel spreadsheet. Do you mean individual worksheet? I don’t understand how you can have and doc file inside a spreadsheet.

[seec77 0]

I think I understand what you want to do, and I think it can easily be done with VBA (Visual Basic for Applications) and FSO (file system object). Do you have experience with these technologies? If not I can try to dig up my VB skills and conceive some code for you. I just don't understand by what order you're looking at the files in the directory. I mean, how would the code know to call Reportdummy.doc (the "first" file in the folder) by the spreadsheet value of Report1.doc. Why not call Charts102.xls Report1.doc (after all, C comes before R). I hope I understood your initial request correctly.

[WeaponX 0]

I have specific filenames in my Excel spreadsheet that I want to use, but the files themselves are not the same. I have to change them to the spreadsheet one by one now. Hard to explain...just need the change My experience with VBA and FSO are very limited at best. If you can help write up a VBS or VBA script for this that would be great :DI could actually get the files by some numerical order. Say something like the following in the folder:Report 0701.docReport 0702.docReport 0707.doc....I want them to have these names (which are in the Excel file):Report1.docReport2.docReport3.doc...Quick question. Is it absolutely necessary to have these files in their own folder each time I run the script? Can I enter a "pattern" for it to search and replace? For example, if I wanted to do the same thing for:Budget 0703.xlsBudget 0708.xld...into:Budget1.xlsBudget2.xls...Maybe something like replace anything beginning with Budget to whatever is on the Excel spreadsheet?

[seec77 0]

I think I understood what you're meaning. I'm working on it as I'm writing this and I hope that in half an hour max. you'll have your working VBA code!

[seec77 0]

Well, I'm done! Here it is:

 

It turned out quite a few lines of code less than I had imagined. I hope you understand what's being done here. Open up the VBA editor through Alt + F11. The code is in the file for the first worksheet. If you want me to move it over to the whole workspace, and have it work on the current worksheet, I have to move some stuff around, but you can easily move it to your own worksheets as is. Just don't forget to look at the code and change the three variables there (column, starting row, and directory name).

 

The code looks only for files beginning with "budget" (case-insensitive), and then renames them to the values in your worksheet, sorted by the original filesnames (FSO sorts automatically! ). The little testing I've done proved it quite useful, but it if you're having trouble with it write back.

 

Anyways, you can call this macro by going to Tools -> Macro -> Macro Commands (or whatever it's called in English, as I have a Hebrew version ). Personally, I would advise you to give the macro a button on your toolbar by doing this:

Go to Tools -> Customize...

Open the Toolbars tab, and click on New...

Name your new toolbar "Rename" or something like that.

Go to the Commands tab, and scroll the right listbox down to Macro Commands.

Drag a Customizable Button to your newly created toolbar that should be open.

Right click the new button on your toolbar and choose the lowest option ("Associate Macro", or something to that effect) and choose the RenameFiles macro.

Right click again and change the name and other properties of the button.

Drag the toolbar to wherever you want it at.

Enjoy!

[WeaponX 0]

It sounds like you hit it right on the money seec77 :unsure:I opened up the Excel file and then the Visual Basic editor. I left the row and columns alone for testing purposes. I changed the directory to my D: drive and then set the keyword to look for as leatherman. I have a few files beginning with Leatherman...1, 2, 3, etc. some JPGs and one TXT file. I left the first column alone and tried to test it. It didn't seem to work.Does it matter if I change the name of the VBA macro? I had to change it because it was half in Hebrew and it won't allow me to run the macro like that (it doesn't recognize the characters).What am I doing wrong here?

Edited July 11, 2006 by WeaponX (see edit history)

[seec77 0]

Oh great, I have to write this post again because I accidently closed my computer before sending it.

First of all: No, changing the name of the sub should not be a problem. What caused the problem for you was changing "budget" to "leatherman". This new code should solve such further problems:

Sub RenameFiles()	'Edit this variable to start the name search from a different row:	StartRow = 1	'Edit this variable to specify in which column the names are:	StartColumn = 1	'Edit this variable to look for a different string at the beginning of filenames	Match = "Budget"		'Define an FSO object	Dim FSO As FileSystemObject	Set FSO = New FileSystemObject		'Create a variable to store the folder holding the files	Dim FilesDir As Folder	'Change the string here to look in a different folder (highly recommended :P)	Set FilesDir = FSO.GetFolder("C:\yotam\temp\renamefiles\test\")		'Define a counter variable and set it zero	Dim i As Integer	i = 0		'Loop through all of the files in the folder	Dim CurFile As File	For Each CurFile In FilesDir.Files			'If the file begins with the word "budget" then rename it		If LCase(Left(CurFile.Name, Len(Match))) = LCase(Match) Then						'Rename the file to the value in the specified cell			CurFile.Move FilesDir + "\" + Me.Cells(StartRow + i, StartColumn).Text			'Increment the counter so next time we will use a cell from the next row for naming			i = i + 1					End If			NextEnd Sub

It was causing problems for you because I hard coded the routine to look in the first 6 letters of the filename for the word "budget", and obviously the code never worked because the program couldn't find the word "leatherman" in the first 6 characters of the filename. Anyways, I now put the word to match in the Match variable, and it automatically makes it lower-case and measures its length, so you should have no problem messing around with it now.

 

I'm trying to make a variant of the routine that can work on the cells you select as filenames. Are you interested? And tell me if it works!

Edited July 11, 2006 by seec77 (see edit history)

[WeaponX 0]

seec77, that worked perfectly. Thanks :DI tried it with 10 letters and left the old code intact to see if that worked also. No problems :unsure:For that other variant, you mean instead of having it "freely" selecting from the columns, you will have the option to choose the cells to copy the name? Definitely interested in that one. I actually wanted to have that originally, but this code makes it much easier already. If you have the time, please code it so that it will rename according to the selected cells instead. I have huge tables in Excel and would have to change the values each time I do this...I will test it out and report back on the progress of it :DQuestion on the renaming. Does it rename in the order the files were sorted in the folder or alphabetically? Also, how does it deal with files with different extensions? For the test, I had 4 JPG files and 1 TXT file. For some reason, it renamed the TXT file first and then the 4 JPG file.

[seec77 0]

As for the most immediate solution to choosing different rows/columns each time, change the first few lines of the subroutine to:

StartRow = CInt(InputBox("Row to start from?", "Row", "1"))StartColumn = CInt(InputBox("Column to start from?", "Column", "1"))

This will make the macro pop up two question boxes each time you run it to ask you for a row and a column.

About the ordering. I really don't know, because FSO (the file system object which I use to access the files) apparently sorts the files on its own, but I don't know what rules it works with. I can easily create an ordering function (I think ) so tell me what you want. And maybe the program as of now regards the extension as a part of the filename, and takes the whole file as a string instead of taking the extension seperately. That could explain why the TXT file came before.

I've been messing around with the selection thing. This is really fun, but I have no results to show you right now, but I'll post back as soon as possible.

[WeaponX 0]

It looks like you are right on that file name sorting. I checked the order and it does seem to go according by name. That's ok...I was just playing around with it and noticed that part. I don't think there will be any filenames that will be the same.For the VBA file, what I do I need to in order to use it on another Excel spreadsheet that I created already? I tried copying and pasting the code directly into my other spreadsheet and it gives my a FileSystemObject error.Yeah, if you can make it so it doesn't even ask, but just checks to see which cells are selected that would be even better :unsure:Thanks.

[seec77 0]

This is the messy way, but I can't remember right now how to do it otherwise, and I'm kind of busy this second, so I'll just give you a little hack to make it work on other Excel files. In the Microsoft Visual Basic editor go to Tools -> Reference, and check Microsoft Scripting Runtime, and it should work. You have to do this for every workbook you want to use the code in.

[seec77 0]

Sub RenameFiles()	'Edit this variable to look for a different string at the beginning of filenames	Match = "Budget"	'Edit this to false if you do not want confirmation	Confirm = True		'Define an FSO object	Set FSO = CreateObject("Scripting.FileSystemObject")		'Create a variable to store the folder holding the files	Dim FilesDir As Folder	'Change the string here to look in a different folder (highly recommended :P)	Set FilesDir = FSO.GetFolder("C:\yotam\temp\renamefiles\test\")		ReDim Files(0) As File		'Loop through all of the files in the folder	Dim CurFile As File	i = 0	For Each CurFile In FilesDir.Files			'If the file begins with the word "budget" then rename it		If LCase(Left(CurFile.Name, Len(Match))) = LCase(Match) Then						'Store all files in an array			Set Files(i) = CurFile			ReDim Preserve Files(UBound(Files) + 1)			i = i + 1					End If			Next		'Loop through all of the selected cells	i = 0	For Each Item In Selection				'If we're out of files to rename, we're done		If (i = UBound(Files)) Then					Exit Sub					End If						If Confirm Then						'Confirm the renaming operation with the user and check the answer			result = MsgBox("Rename """ + Files(i).Name + """ to """ + Item.Value + """?", vbYesNoCancel, "Rename")						If result = vbYes Then							'Do the actual renaming in case the user confirmed				Files(i).Move FilesDir + "\" + Item.Value							ElseIf result = vbCancel Then								'If the user click cancel, we're done				Exit Sub							End If					Else						'Do the actual renaming if confirmation is disabled anyways			Files(i).Move FilesDir + "\" + Item.Value				End If				i = i + 1			NextEnd Sub

Here's the updated version. The new things are that firstly, it will now work on any Excel workbook without any need to modfy anything. Secondly, the routine now retrieves the new filenames from the selected range. And thirdly, it now features a confirmation feature, that can be turned off quite easily by changing the Confirm variable to False. If confirmation is enabled, the user gets asked to confirm every file rename. I just added this feature because I imagined that you might be afraid that you're renaming the wrong files, or selecting the wrong cells, or whatever.

 

Anyways, I was really happy to make this, so if you have any improvements you want me to do on it, or any other VBA (not neccessarily Excel) application you want I'd be really glad to help!

[WeaponX 0]

How do I use this on my own Excel spreadsheet? I just created a blank macro and tried copying the code in but it gives me an error:

 

Compile error:

 

User-defined type not defined

 

I was comparing and see what's the difference between the file you created and mine. The one I have has the code inside a Module. The one you attached earlier doesn't have this but something else.

 

I do have a separate question. You might be able to help me out on this one. It's VB6 related. I will open a new topic for this if you can help me on this one. I want to create a VB program that will allow users to preview BB codes (like the ones we see here at InvisionBoard). So basically it will recognize the BB tags and display them in formatted text.

[seec77 0]

Oops! Stupid me! Sorry for that supid mistake of mine!

Sub RenameFiles()	'Edit this variable to look for a different string at the beginning of filenames	Match = "Budget"	'Edit this to false if you do not want confirmation	Confirm = True		'Define an FSO object	Set FSO = CreateObject("Scripting.FileSystemObject")		'Change the string here to look in a different folder (highly recommended :P)	Set FilesDir = FSO.GetFolder("C:\yotam\temp\renamefiles\test\")		ReDim Files(0)		'Loop through all of the files in the folder	Dim CurFile	i = 0	For Each CurFile In FilesDir.Files			'If the file begins with the word "budget" then rename it		If LCase(Left(CurFile.Name, Len(Match))) = LCase(Match) Then						'Store all files in an array			Set Files(i) = CurFile			ReDim Preserve Files(UBound(Files) + 1)			i = i + 1					End If			Next		'Loop through all of the selected cells	i = 0	For Each Item In Selection				'If we're out of files to rename, we're done		If (i = UBound(Files)) Then					Exit Sub					End If						If Confirm Then						'Confirm the renaming operation with the user and check the answer			result = MsgBox("Rename """ + Files(i).Name + """ to """ + Item.Value + """?", vbYesNoCancel, "Rename")						If result = vbYes Then							'Do the actual renaming in case the user confirmed				Files(i).Move FilesDir + "\" + Item.Value							ElseIf result = vbCancel Then								'If the user click cancel, we're done				Exit Sub							End If					Else						'Do the actual renaming if confirmation is disabled anyways			Files(i).Move FilesDir + "\" + Item.Value				End If				i = i + 1			NextEnd Sub

This is hardly a change over the previous code, but it should work now (I tested it in an enviroment like your's should be, and it worked).
About the VB, I guess I can help (even though I haven't done VB6 in quite a long time and don't have it installed). You should open a new thread in the appropriate place. Just be aware that VB is really a bad language for something like what you're trying to do!

Sorry again! I hope this is the last time I have to post this code.