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!
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Rename multiple files in a folder macro
- Thread starter csmithee
- Start date
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.
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:
Upvote
0
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!
Upvote
0
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.
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.
Upvote
0
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.
Upvote
0
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...
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...
Upvote
0
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.
Upvote
0
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.
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.
Upvote
0
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.
Upvote
0
