Rename multiple files in a folder macro

csmithee

New Member
Joined
Jun 10, 2009
Messages
18
I have 400ish departmental budget files in a folder that I need to upload to a Sharepoint doc library. The files in the folder will have to exactly match the files we already have set up in sharepoint in order to upload them to the sharepoint site. As it stands now there is only one consistency between the sharepoint files and the budget files in the folder, which is the department number (which every file in both begins with). I'm thinking some sort of macro will let me insert a sheet into each of the files in the budget folder, which would have a formula that pulls the department number from a certain sheet in the file, and produce the file name I want into a certain cell. Then I would like a macro that goes into each file and renames it based on that cell. I'd like to know if this is possible and if there may be a better way to do it. Thanks in advance for all your help!
 

jbeaucaire

Well-known Member
Joined
May 8, 2002
Messages
6,012
Anything you can imagine can be coded. Just need the specifics.

1) Are you talking about opening ALL the files in one folder and renaming them?

2) Can the new name you want each file to have be found IN the document itself in the same place, a specific cell perhaps? If so, what cell on what sheet?

3) Does that cell represent the WHOLE name or are you adding that info to some other stock text string? Like if the cell is D1 and the value is 6/6/09 and you want the filename to be: "Budget 06/06/09.xls" then you need to specify that.

Otherwise we would just name it "6/6/09.xls" or "06/06/09.xls" ... make sure you full describe the filename results you want.

Also, if the filename goal is a SUBSET of the existing book name and not a value in a cell, clarify that fully because it's doable, too.

4) There should be no need to add a sheet to the files themselves unless that's something you must have, too. We just need to know where the cell is that has the information in it for naming the file.
 
Last edited:

csmithee

New Member
Joined
Jun 10, 2009
Messages
18
Anything you can imagine can be coded. Just need the specifics.

1) Are you talking about opening ALL the files in one folder and renaming them?

2) Can the new name you want each file to have be found IN the document itself in the same place, a specific cell perhaps? If so, what cell on what sheet?

3) Does that cell represent the WHOLE name or are you adding that info to some other stock text string? Like if the cell is D1 and the value is 6/6/09 and you want the filename to be: "Budget 06/06/09.xls" then you need to specify that.

Otherwise we would just name it "6/6/09.xls" or "06/06/09.xls" ... make sure you full describe the filename results you want.

Also, if the filename goal is a SUBSET of the existing book name and not a value in a cell, clarify that fully because it's doable, too.

4) There should be no need to add a sheet to the files themselves unless that's something you must have, too. We just need to know where the cell is that has the information in it for naming the file.
First, thank you so much for the reply!

I am wanting to open all the files in a folder and rename them. Only the department number can be found inside the file itself.

I would want to add the extra sheet (hidden and protected with password "2010" preferably) to all existing budget files, linking to the dept number in another sheet to produce the final file name in cell D4, by saying if the dept # is this, then the file name will be this. For instance, in the insert sheet, column A would be the dept # and in column B would be the new filename. In column C, there would be a formula that says if the dept # in "Sheet 1", cell C4 is X, then find X on the new sheet, and produce its filename in cell D4 of the new sheet.

The new filename should be linked to cell D4 in the newly inserted sheet called "filename".

Again, thank you for your help. It is much appreciated!
 

jbeaucaire

Well-known Member
Joined
May 8, 2002
Messages
6,012
I've read that 4 times and must admit your description of the desired process stumbles me. I still have no glimmer of the point of this hidden sheet.

You're saying that in some "other sheet" that you're running this macro from is a list of departments? And next to those departments is another column with the desired budget book names? Is that what you meant?

Also, in the books being opened, the department name is found in the existing data somewhere already? Well, where? Exactly?

If we have a table of departments and booknames to refer to, and when we open a book we can spot the department in a specific cell...we have no need of a hidden sheet, we can determine already what the bookname should be and immediately save the book, move on to the next.
 

csmithee

New Member
Joined
Jun 10, 2009
Messages
18
I've read that 4 times and must admit your description of the desired process stumbles me. I still have no glimmer of the point of this hidden sheet.

You're saying that in some "other sheet" that you're running this macro from is a list of departments? And next to those departments is another column with the desired budget book names? Is that what you meant?

Also, in the books being opened, the department name is found in the existing data somewhere already? Well, where? Exactly?

If we have a table of departments and booknames to refer to, and when we open a book we can spot the department in a specific cell...we have no need of a hidden sheet, we can determine already what the bookname should be and immediately save the book, move on to the next.
I'm sorry for the confusion. We do, in another file all together, have a table of departments and their corresponding booknames to refer to, which I originally wanted to insert into all the budget files. Does that not have to be in the file itself? If not, then you are correct, there is no need for an inserted sheet into each file. The dept # is in cell C4 of "Sheet 1". The dept # would trigger the full filename in this unrelated budget file. Hope that helps.
 

jbeaucaire

Well-known Member
Joined
May 8, 2002
Messages
6,012
Will you be running this macro FROM the sheet that has the department/booknames? If so, where in this book is this table of values.

If not, what is the full path and name of the book, too?

I suppose with the full name, the file could be kept in the same folder of files you are renaming...
 

csmithee

New Member
Joined
Jun 10, 2009
Messages
18
Will you be running this macro FROM the sheet that has the department/booknames? If so, where in this book is this table of values.

If not, what is the full path and name of the book, too?

I suppose with the full name, the file could be kept in the same folder of files you are renaming...
Yes, I will run the macro from the sheet that has the departments and corresponding filenames, which I can put in the same folder of the files I am renaming.
 

csmithee

New Member
Joined
Jun 10, 2009
Messages
18
I will have the original file name in Column A, then I will have the new filename in Column C. So I want it to search a folder and when it finds the name of a file in Column A to rename it with the corresponding name in Column C.
 

jbeaucaire

Well-known Member
Joined
May 8, 2002
Messages
6,012
That's a little different than you've said before. I thought we were going to:

1) Open a file with no concern for its original name
2) Look at Sheet1!C4 for a department
3) Match the department in Column A (?) of the main sheet
4) Save the opened file with the matching value from Column C (?) of the main file (corresponding value from the department match in step #3)

Yes?

Also, you indicated 200+ files, so that means there are 200+ departments? After all, we can't save two sheets with the same name.
 

csmithee

New Member
Joined
Jun 10, 2009
Messages
18
That's a little different than you've said before. I thought we were going to:

1) Open a file with no concern for its original name
2) Look at Sheet1!C4 for a department
3) Match the department in Column A (?) of the main sheet
4) Save the opened file with the matching value from Column C (?) of the main file (corresponding value from the department match in step #3)

Yes?
Well, I got to thinking about it today and figured out a way get a list of all the filenames in the folder in column A. Then from that list create their new corresponging names in column C. So, can I use a macro to search a folder for the original name, and replace it with the new name in Column C. We'll say the folder filepath is N:\Accounting\Financial Analysis\2010 Budgets. The translate sheet, with the old and new names will be called "Translate Sheet" in the same folder.
 

Forum statistics

Threads
1,082,269
Messages
5,364,148
Members
400,783
Latest member
sambills

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top