PAGE-2: Renaming Files (Using Excel Spreadsheet) related to excel, file, rename, folder

WeaponX

Jul 13 2006, 03:41 AM

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.

Reply

seec77

Jul 13 2006, 07:43 AM

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.

Reply

seec77

Jul 13 2006, 08:56 AM

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!

Reply

WeaponX

Jul 13 2006, 10:39 PM

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.

Reply

seec77

Jul 14 2006, 03:13 PM

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.

Reply

WeaponX

Jul 14 2006, 10:01 PM

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.

Reply

cypro

Jun 21 2007, 08:41 AM

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

Reply

WeaponX

Jun 22 2007, 12:32 AM

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.

Reply

iGuest

Dec 12 2007, 08:39 PM

Thaaaaaankyou

thank you
thank you
thank you
thank you
thank you
thank you
thank you
thank you
thank you
thank you seec77!

-jimmycrackcorn

Reply

iGuest

Feb 19 2008, 06:05 AM

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

Reply

ADD REPLY / Got an Opinion!	a humble request :-)	RAPID SEARCH!	Free Hosting	[X]
Express your Opinions, Thoughts or Contribute your information that might help someone here. Ask your Doubts & Queries to get answers.. "Together, We enlight each other!"	Register FREE for AD-FREE forum, Create your own topics, Ask Questions, track topics, setup subscriptions & notifications and Get a Free Website w/ Email and FTP.		500MB Space No Ads, CPanel, FTP, PHP, MySQL, EMails - 100% FREE