Mrrrr's Forum (VIEW ONLY)
Un forum care ofera solutii pentru unele probleme legate in general de PC. Pe langa solutii, aici puteti gasi si alte lucruri interesante // A forum that offers solutions to some PC related issues. Besides these, here you can find more interesting stuff.
Mrrrr
AdMiN Inregistrat: acum 17 ani
Postari: 2186
First of all, please check the youtube link at the bottom of this post for a video tutorial on how to do the whole renaming part - my explanation might not be as simple for someone who hasn't done this before.
I modified a bit the steps taken in the video tutorial and created my own Excel template for renaming multiple files. My excel file contains the following additional / different things compared to the source video: - I've done everything in an Excel table (Insert - Table or just use CTRL+T) - I've used formulas instead of simple text, like: ="ren" instead of just ren and dragging that down - I'm having a table with formulas so if more rows are added the formulas will automatically fill down if I write ren as ="ren" - I added a vba code to clear the 2 columns with file names
1. Open Excel and save your file in a desired location. Choose either 1.a or 1.b when saving your Excel file: 1.a. Save your file as an Excel Workbook (.xlsx extension). At point 2, make sure you save the VBA code in a module in your PERSONAL.XLSB file. 1.b. Save your file as an Excel Macro Enabled Workbook (.xlsm extension). At point 2, save the VBA code in a module in your excel file.
2. Save the code below in your PERSONAL.XLSB file or in the Excel file you saved at point 1.b. Save PERSONAL.XLSB if this is the option you chose, then close the Visual Basic Editor. Otherwise, just close the Visual Basic Editor.
Sub Clear_Table_Columns() Application.EnableEvents = False Dim i As Integer Sheets(1).ListObjects("Table1").ListColumns(3).DataBodyRange.Clear Sheets(1).ListObjects("Table1").ListColumns(7).DataBodyRange.Clear Application.EnableEvents = True End Sub
3. In Sheet1 you will have to create a table with the following columns in Excel columns A, B, C, D, E, F, G, H, I:
Follow these steps to create the table:
3.1. Type the name of your columns in cells A1, B1 etc. (see below). - cell A1 text: Cmd - cell B1 text: Space - cell C1 text: Old names - cell D1 text: Ext - cell E1 text: Quot marks - cell F1 text: Old names with " " - cell G1 text: New names - cell H1 text: New names with ext and " " - cell I1 text: Final code to paste into CMD
Select range A1:I10, then either press CTRL+T or go to Insert - Table. Either way make sure "My table has headers" is checked, then click OK.
3.2. In the columns insert the following formulas in row 1 of the table (1st row after table headers) - or see picture from point 3.3. - cell A2: ="ren" - cell B2: =" " - cell C2: leave empty for now - cell D2: =IFERROR("."&RIGHT(C2;LEN(C2)-SEARCH(".";C2));"") - cell E2: ="""" - cell F2: =E2&C2&E2 - cell G2: leave empty for now - cell H2: =E2&G2&D2&E2 - cell I2: =A2&B2&F2&B2&H2
Note: in a table, if you manually select cells (ie click on them instead of typing), the cell references might change automatically to table references, like for example in cell D2: =IFERROR("."&RIGHT([@[Old names]];LEN([@[Old names]])-SEARCH(".";[@[Old names]]));"") etc.)
3.3. This is how your table should look like (on row 1 there are the table cells formulas, so my table started from row 2):
4.1. Next to your table insert a rectangle shape, type some text into it, like: "Clear names", then click outside the shape. 4.2. Right click the shape and select Assign Macro. In the list of Macros look for the Clear_Table_Columns macro you created at point 2, then click OK. Note 1: If you saved your file as .xlsx and the code in PERSONAL.XLSB file, then your macro in the list will be named PERSONAL.XLSB!Clear_Table_Columns, otherwise it will just be Clear_Table_Columns Note 2: The code at point 2 will delete the contents of columns 3 (Old names) and 7 (New names)
5. Open the folder containing your files to be renamed, select all of them, hold SHIFT key then right click the first file in the selection and then click "Copy as path".
6. In your Excel file, create a new sheet and paste the paths of the files in the first cell of column A. Remove the folder path by selecting it, copy, then CTRL+H to open the Find & Replace dialog, paste in the Find what: field and leave the Replace with: field empty. Then click replace all. Column should remain containing only your file names + extensions.
Eg. Find what: C:\My Folder\Files to rename\ Replace with:
7. Add the desired names for each of your files in column B, each corresponding to the file name in column A.
For example I downloaded some PDF files from a site but even though the links had names, the downloaded PDF files are named document_1, document_2 and so on. So I downloaded the files and made point 6.
Now I'm copying the proper names from the website all at once in excel and put them in column B, next to the current PDF files names.extensions obtained at point 6.
8. Copy the current PDF names (obtained at point 6) to column Old names in your table. Then copy the desired names to column New names in your table.
Column I of your table should get a result like this: ren "aaaa.pdf" "bbb.pdf"
9. Go to Windows Explorer (File Explorer) in the folder containing your files to be renamed, and in the address bar instead of the path type CMD then hit ENTER. A Command Prompt window will open at the location of the folder.
10. Make a backup of your files, just in case something is not right in your naming lists, otherwise you risk messing things up and having to manually rename all the files.
11. Copy the contents of the entire column I from your table and then paste it (CTRL+V) into CMD. Your files should be renamed pretty fast.
Source for VBA code from point 2:
Video source on how to rename multiple files using CMD and Excel:
