Renaming Files (Using Excel Spreadsheet)

WeaponX

Dec 27 2005, 11:37 PM

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.doc
Charts102.xls
DecemberGraphs.xls
TestScrap.txt
etc...

And I have this in my Excel spreadsheet:

Report1.doc
Charts.xls
Graphs.xls
Scrap.txt
etc...

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

Jul 1 2006, 02:30 AM

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

Jul 1 2006, 11:31 AM

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

Jul 9 2006, 02:33 AM

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

I could actually get the files by some numerical order. Say something like the following in the folder:

Report 0701.doc
Report 0702.doc
Report 0707.doc
....

I want them to have these names (which are in the Excel file):

Report1.doc
Report2.doc
Report3.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.xls
Budget 0708.xld
...

into:

Budget1.xls
Budget2.xls
...

Maybe something like replace anything beginning with Budget to whatever is on the Excel spreadsheet?

seec77

Jul 9 2006, 10:33 AM

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

Jul 9 2006, 11:35 AM

Well, I'm done! Here it is:
Click to view attachment
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

Jul 11 2006, 12:47 AM

It sounds like you hit it right on the money seec77

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?

seec77

Jul 11 2006, 08:58 AM

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:

CODE

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

    Next
End 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!

WeaponX

Jul 11 2006, 11:20 PM

seec77, that worked perfectly. Thanks

I tried it with 10 letters and left the old code intact to see if that worked also. No problems

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

Question 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

Jul 12 2006, 09:01 AM

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

CODE

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.

Latest Entries

iGuest

Aug 5 2008, 09:56 AM

Macro to search the desired worksheet and load data to another worksheet
Renaming Files (Using Excel Spreadsheet)

Dear Experts,

I am very new to Macros. As such I wished to automate a Quality Check (QC) Report. There is a folder on our server where all the monthly QC files are saved. I want to run a macro to create a report of daily total volumes cleared vs internal errors identified vs external errors identified. The moment the run button is clicked it should ask "For which month" And whichever month I type, it should open the folder containing the QC files and search for the title of the QC files and open the required file. The first report in this report file is a daily report. The same report I want staffwise. The staff names are stored in the QC file in validation. So if there is a new staff getting added this new staff details should appear in the report through this macro only where in the macro searches the validation for the last staff name and if it matches with the existing staff list then ignores else creates the same report format as for the other staff.

I hope I will get good help from this site.
Please help as this is a part of a six sigma control phase where we want to create a robust accuracy reporting system with no person dependancy.

Thanks and Regards for all your valuable time and advice.

-question by Learner

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

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

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.

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

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