 Renaming Files (Using Excel Spreadsheet) |
|
|
|
|
  |
 Jul 13 2006, 03:41 AM
Post
#11
|
|
|
Way Out Of Control - You need a life :)  Group: Members Posts: 1,086 Joined: 21-June 05 From: New York Member No.: 6,440  |
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  Thanks. |
 |
 
|
|
Jul 13 2006, 07:43 AM
Post
#12
|
|
|
Advanced Member Group: Members Posts: 157 Joined: 16-May 06 Member No.: 13,476 |
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.
|
|
|
|
|
Jul 13 2006, 08:56 AM
Post
#13
|
|
|
Advanced Member Group: Members Posts: 157 Joined: 16-May 06 Member No.: 13,476 |
CODE 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 Next End 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! |
|
|
|
|
Jul 13 2006, 10:39 PM
Post
#14
|
|
|
Way Out Of Control - You need a life :) Group: Members Posts: 1,086 Joined: 21-June 05 From: New York Member No.: 6,440 |
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. |
|
|
|
|
Jul 14 2006, 03:13 PM
Post
#15
|
|
|
Advanced Member Group: Members Posts: 157 Joined: 16-May 06 Member No.: 13,476 |
Oops! Stupid me! Sorry for that supid mistake of mine!
CODE 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 Next End 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. 
|
|
|
|
|
Jul 14 2006, 10:01 PM
Post
#16
|
|
|
Way Out Of Control - You need a life :) Group: Members Posts: 1,086 Joined: 21-June 05 From: New York Member No.: 6,440 |
Thanks seec77. That worked perfectly
Just one last question. Can I undo the rename manually (ctrl+z) if I find out that there was a mistake? I just tried it and don't think it's working. I just posted that other question on VB6 over here. If you can help me out on that one also, that would be great  Thanks again for all the help. |
|
|
|
|
Jun 21 2007, 08:41 AM
Post
#17
|
|
|
Newbie [ Level 1 ] Group: Members Posts: 1 Joined: 21-June 07 Member No.: 22,806 |
hello guys,
i know this topic is very old, but it kind of describes perfect what i am looking for at the moment. i only have one important problem... the code doesnt seem to work for me... my VBE in excel is keep on saying me that custom type FSO is not defined... i think this script is exactly what im looking for but i can't try it, because the script doesnt work proper... it doesnt work at all... to describe the problem that i have: i want to rename *.JPG files with the fixed structure of something like '007_97_001.JPG'. the new names of the files are located in an excel sheet in one column... like i would have in a folder: 007_97_001.JPG 007_97_002.JPG 007_97_003.JPG and i have a column in excel: measure_control1 watermeter3 waterpump11 now i want to rename the old files in: measure_control1.JPG watermeter3.JPG waterpump11.JPG it would be really nice if anyone can answer greetings Stefan |
|
|
|
|
Jun 22 2007, 12:32 AM
Post
#18
|
|
|
Way Out Of Control - You need a life :) Group: Members Posts: 1,086 Joined: 21-June 05 From: New York Member No.: 6,440 |
Hi Stefan and welcome to Astahost.
The script created by seec77 was a lifesaver for me. I'm still using it very frequently to this day You need to look at two areas in the script: CODE Match = "Budget" Set FilesDir = FSO.GetFolder("C:\yotam\temp\renamefiles\test\") For the Match part, in your case enter something like 007. That should be more than enough since I'm assuming ALL your files in THAT folder starts with 007 right? Keep in mind that if you have any other files that are different like 008, it will NOT rename that file. So make sure they are following some pattern. After that part, you need to tell it what folder you have it in. So if you have all those JPGs in c:\images, enter that into the double quotes for the Set FilesDir line. Once that's done, close the visual basic editor window. Hit Alt+F8 to bring up the Macros window. Double click on the macro to run it. |
|
|
|
|
Dec 12 2007, 08:39 PM
Post
#19
|
|
|
Newbie [ Level 1 ] Group: Members Posts: 0 Joined: 1-November 07 Member No.: 25,869 |
Thaaaaaankyou
thank you thank you thank you thank you thank you thank you thank you thank you thank you thank you seec77! -jimmycrackcorn |
|
|
|
|
Feb 19 2008, 06:05 AM
Post
#20
|
|
|
Newbie [ Level 1 ] Group: Members Posts: 0 Joined: 1-November 07 Member No.: 25,869 |
Copy and Rename files in folder using Excel Macro
Renaming Files (Using Excel Spreadsheet) Hi, I have a folder say see:\test which has many files. Using Excel I want to copy all files starting with A*.* to B*.* (Note I want to copty and not rename as I would like to retain all the files which start with A*.*) Can anyone please help. -Summer |
|
|
|
|
Similar Topics
| Topics | Topics | |
|---|---|---|
|
|
|
|
Lo-Fi Version | Time is now: 11th October 2008 - 06:49 AM |