One-by-One Renaming Spreadsheet for renaming files in Windows Explorer

Introduction 

The spreadsheet available for download using the link above assists with renaming files one at a time in Windows Explorer. This may be useful for people who frequently rename files and find it slow and clumsy to rename files in Windows Explorer when it is also necessary to view different parts of the document when deciding what the file name should be.

Of course, wherever possible it is ideal to bulk rename files using metadata (especially in the case of emails or photographs), or by using a table of data about the files that allows them to be bulk renamed (e.g. a report from accounting software that can be used to generate file name data for a collection of invoices).

However, it is often the case that a collection of files, particularly pdf files, need to be renamed one-by-one, perhaps based on visual inspection of the contents of the documents to confirm the date, and/or nature of the document. Renaming may be needed simply to have usefully named files for orderly document management, or may be needed to implement a file naming convention in a litigation document production library so that data can be bulk extracted when preparing a list of documents in litigation. In this context file renaming is essentially performing some degree of old-fashioned document “coding” by putting useful date and description information into the file names, and placing the files in sensibly named folders, all of which data can later be extracted when preparing a list of documents.

The One-by-One Renaming Spreadsheet partially automates the process of opening files one at a time, and aids file names being built up using a date picker and commonly used snippets of text which can be curated on lists stored in the spreadsheet. These features, and others built into the spreadsheet, can speed up file renaming.

In a nutshell, the spreadsheet works as follows:

1. Put the paths of the files to be renamed onto the clipboard.
2. Open the spreadsheet, and the file renaming form will be displayed automatically, and the first document to be renamed will be opened so you can view its contents.
3. Using the date picker, and snippets of text you have programmed into the available lists, and likely also some regular typing, define the proposed new file name.
4. Click the green “Rename” button, to shut down and rename the current file, and open the next file to be renamed.

While the above describes the core functionality of the spreadsheet, there are a number of settings and features designed to improve the renaming process, and so a somewhat lengthy set of instructions is included below.

 

The workflow the spreadsheet is designed to improve

To emphasize the purpose of the spreadsheet it is helpful to identify the workflow that it is designed to improve upon.

When renaming files in Windows File Explorer one may view file contents bv opening the file using its native application (e.g. open pdfs using Adobe), or by viewing the file in the preview pane in Windows File Explorer.  

A file name can be edited in Windows Explorer by clicking on it to select it, and then clicking it again to activate renaming mode. Or one can click on the file and press F2 to activate renaming mode.

Renaming mode can be activated while the file is still open, but the renaming will not be accepted unless the file is shut down before the renaming is applied. Problems can arise if one is in renaming mode but wants to scroll around in the document to find information (e.g. to find the relevant date of the document), and it is common to get the warning that the file cannot be renamed while open:

The above error can be addressed by shutting the file down and then clicking “Try Again” to implement the renaming, but that process is somewhat clumsy and slow.

Using the preview pane in Windows Explorer to gather the information needed to rename the file is an option, but when using the preview pane it is not possible to zoom in on part of the document e.g. to read a date written in small text.

A benefit of the One-by-One Renaming Spreadsheet, particularly when using it to rename pdf files, is that you are free to work with the document any way you wish while the renaming macro stands by. You can OCR the document, rotate pages, delete pages, perform keyword searches, or even apply redactions, and then proceed with the renaming process. This flexibility to work with documents in the midst of renaming streamlines the document review and renaming process.

Note that one thing you cannot do while the One-by-One Renaming Spreadsheet stands by is open or use another Excel workbook.

 

Requirements for, and limitations of, the One-by-One Renaming Spreadsheet  

The Windows desktop version of Excel is required to run the One-By-One Renaming Spreadsheet, and macros need to be enabled. The spreadsheet cannot be run on Mac, but only Windows.

Adobe Standard (or Adobe Pro) is not required, but is desirable so that the macro can automatically close the pdf files after renaming and then instantly open the next one. If Adobe Reader, or another pdf viewer (e.g. a web browser), is used to view the pdf files then they will usually need to be closed manually before renaming can proceed. 

Files other than pdfs will open in their native application that is used to open them by default, and in most cases these will need to be closed before renaming can proceed.  In some cases the files can be renamed while open and then bulk closed at the end of the batch.

The renaming spreadsheet cannot be used to rename Excel files.

The renaming spreadsheet works best with two screens (or one very large screen), so that the renaming form can be on one side and the documents to be renamed can open on the other side.

 

Macros and security issues

The One-By-One Renaming Spreadsheet was developed using macros (programmed in VBA) and in order for the spreadsheet to operate your security settings in Microsoft Excel must be set to allow macros to run. 

Macros can pose a security risk because they make your computer perform certain steps automatically, but are typically only dangerous when made to be that way by a computer programmer with a malicious intention – hence the warning when you change the security settings in Excel. However, the macros available in the One-by-One Renaming Spreadsheet are made with good intentions and should not harm your computer. 

To enable macros in Excel click:

• File (i.e. the menu on the top left)
• Options
• Trust Centre
• Trust Centre Settings
• Macro settings
• Enable all macros, and on that same page also check the box for “Trust access to the VBA project object model”. 

Modern versions of Excel are very protective in shielding users from files with macros, and you may need to save the spreadsheet in a “trusted location” in order for the macros to run. To designate the location the file is saved in as a trusted location, in Excel click:

• File (i.e. the menu on the top left)
• Options
• Trust Centre
• Trust Centre Settings
• Trusted Locations
• Add new location.
• Copy and paste the path of the folder containing the spreadsheet into the dialog box to specify that folder as a trusted location. 

 

Pin the file to the Excel icon on the taskbar

Because the point of using the One-By-One Renaming Spreadsheet is to save time, it is recommended to pin the spreadsheet to the Excel icon on the task bar in Windows Explorer. You can do this by opening the file, and the closing it down again, such that it then appears on the “Recent” list available when you right click the Excel icon on the taskbar. You can then click on the pin to the right of the name of the file on “Recent” list to pin it to the “Pinned” list:

Then, each time you wish to open the One-By-One Renaming Spreadsheet, you can right click on the Excel icon on the task bar and then click the file name in the Pinned list:

 

 

Running the spreadsheet

There are four ways to run the One-By-One Renaming Spreadsheet and bring up the file renaming form.

Method #1: Put file paths onto clipboard before opening the spreadsheet

Before opening the spreadsheet, put the paths of the files to be renamed onto the clipboard.

To put file paths on the clipboard, select the files in Windows File Explorer and then right click on them (or Shift Right Click if using Windows 10), and then select “Copy as Path” from the right click menu:

If file paths are on the clipboard, and the checkbox on the left end of cell B4 on the “Paths and File Names” tab of the spreadsheet is checked, then as the spreadsheet opens it will examine the paths on the clipboard and the put file paths in column C and the file names in column B:

If that checkbox in cell B4 was checked and valid file paths are successfully detected then the spreadsheet will automatically open the file renaming form and open the first document to be renamed.

Keeping the checkbox in cell B4 checked, putting file paths on the clipboard, and then opening the spreadsheet, is the most common way to run the spreadsheet, but additional methods are described below.

Method #2: Put a folder path onto clipboard before opening the spreadsheet

Before opening the spreadsheet, put the path of a folder containing the files to be renamed onto the clipboard.

To put a folder path on the clipboard, you can copy it from the address bar in Windows File Explorer:

Alternatively, you can also put a folder path onto the clipboard by right clicking on the folder name (or Shift Right Click if using Windows 10), and then select “Copy as Path” from the right click menu.

If a folder path is on the clipboard when the One-By-One Renaming Spreadsheet is opened the macro will automatically:

1. put the folder path into cell B15 on the “Paths and File Names” tab of the spreadsheet; and
2. put the file paths for all files in the folder tree at that folder path into column C, and the corresponding file names into column B. 

If the checkbox in cell B4 is checked and valid file paths are successfully detected then as it is opened the spreadsheet will automatically open the file renaming form and open the first document to be renamed

Method #3: Copy and paste file paths into column C

If you copy and paste file paths into column C of the “Paths and File Names” tab of the spreadsheet (see screenshot above), and then click the green “Run Renaming” button, the macro will put the corresponding file names into column B and open the file renaming form.

Method #4: Put folder path in cell B15

If you copy and paste a folder path into cell B15 of the “Paths and File Names” tab of the spreadsheet (see screenshot above) you can then click the blue “Extract paths from folder” button to extract into column C the file paths of all the files in the folder tree below that top level folder. The corresponding file names will be put into column B. You can then click the green “Run Renaming” button to open the file renaming form.

 

Overview of the file renaming form

The file renaming form will open on top of the One-By-One Renaming Spreadsheet window, and that form has four general areas which are each discussed in more detail below:

1. Listbox of files queued for renaming.
2. Date picker.
3. Curated lists of snippets that can be used when building new file names.
4. New file name definition area.

 

1 - Listbox of files queued for renaming

At the top left of the renaming form the pre-existing file name of the file currently being considered for renaming is displayed. In the list box below there will be a list of the files currently queued for renaming:

You can double click on any file in the list to activate it for renaming, and (if it is a pdf file and you are using Adobe Standard or Adobe Pro) the previous file will be closed, the double clicked one will be opened, and the current name of the double clicked one will be displayed in the new name input box in area 4 (discussed further below). After you have renamed a file the new name will be displayed in the listbox at the top left of the renaming form. If you later wish to further modify the name you can double click on the file name again to activate it for further renaming.

 

2 - Date picker

In the top middle of the renaming form there is a date picker that allows one to quickly define the date to be included at the start of the proposed file name:  

If you are naming old documents (e.g. before 2010) you can change the start year in the year listbox by entering a new value into the “Start Year” box shown at the top of the screenshot above, and then click the “Update” button to set the list of years to start with the year you just specified.

To quicky apply today’s date to the start of the proposed file name, click the “Today” button.

To prepend either “Undated” or “Various” to the start of the proposed new file name, click the applicable buttons shown in the screenshot above.

 

3 - Curated lists of snippets that can be used when building new file names.

At the top left of the renaming form is a listbox which you can populate with snippets that you will often want to include in file names. Related to this, there are three listboxes in this part of the renaming form:

1. “Categories” listbox = Categories under which similar types of snippets can be grouped. Each time a category name is clicked in the Category listbox the snippets in that category are displayed in the “Snippets” listbox to the right.
2. “Snippets” listbox = Contains a list of snippets that you will often want to include in file names.
3. “Operations” listbox = Allows you to perform various operations, such as adding a snippet to the new file name currently being composed.

Initially all categories will be named “Spare”, but you can rename a category by:

1. Selecting, in the Categories listbox, the Category to be renamed;
2. selecting “Edit category name” at the bottom of the Operations listbox; and
3. clicking the GO button.

To add, edit, or remove, snippets, first click on the relevant snippet in the Snippets listbox, select the desired operation from the middle zone of the Operations listbox, and then click the GO button.

To write a snippet to the new file name being composed, select the append location the snippet is to placed by selecting the relevant operation from the top three items of the Operations listbox, then select the relevant snippet in the Snippets listbox, and then click the GO button. However, you will not need to click the GO button in all cases: if one of the first three items is already selected in the Operations listbox then each time a new snippet is selected in the Snippets listbox that snippet will automatically be sent to the new file name.

If you wish to build out a collection of categories and snippets at once you can edit the list on the Settings sheet (discussed further below).

 

4 - New name definition area.

At the bottom left of the renaming form there is a field for definition of the proposed new file name, and a collection of controls:

By default, a date will be written in at the start of the proposed new file name. Although the related settings can be changed on the Settings page (discussed further below), the recommended format for the date to be included at the start of the file name is YYYY-MM-DD_ i.e. in accordance with ISO 8601, https://en.m.wikipedia.org/wiki/ISO_8601.

The dashes provide visual separation between the numerical components, and the underscore at the end of the date provides a distinct visual delimiter, and can also be used to separate text if file name data is later imported into Excel.

If the pre-existing file name has a recognizable date at the start (e.g. YYYYMMDD without the dashes) then that date will be placed at the start of the proposed file name, but reformatted with the dashes (or other formatting as specified on the Settings sheet, see below). If no date is recognized, the date used for the previous document will be placed at the start of the proposed file name. You can always use the date picker to redefine the date at the start of the proposed file name.

You can use the snippet listbox described above to add snippets to the proposed file name, and you can also just type regular text content in the proposed file name field to define the new file name.

When editing the file name you should maintain the file extension on the end of the file name.

The listbox below the proposed new file name field (see screenshot above) has various controls (simply click a control to run the operation, or to run it again ensure it is highlighted and click the GO button):

• Remove description portion = Click this option to instantly remove the “description” portion of the file name i.e. the portion between the date and the file extension.
• Remove underscore other than in position 11 = Some scanners / software use underscores instead of spaces in file names e.g. “Notice_of_Civil_Claim.pdf”, or files may have stray underscores which may be undesirable if you will later use underscores as a delimiter when splitting up file name text s in Excel. Click this option to instantly remove the underscores (other than those in position 11 which is at the end of the date portion of the file name). Note that this setting will also preserve underscores after “Undated”, or “Various”, at the start of file names.
• Remove double spaces = Click this option to replace all excess spaces in the file name. 
• $$$ - Add / replace $ amount in square brackets at end descrip. = Click this option to bring up an input box into which you can type a number with a period before the cents, that will then be formatted as [$XXX,XXX.00] and placed at the end of the proposed file name i.e. the macro will put in the required space, open square bracket, currency sign (specified on the Settings sheet), comma for thousands if needed, and close bracket. If you omit the period and cents then the macro will add .00 to the end of the currency amount. This tool is useful if you want to include amounts in file names for later ease of access, or for extraction into Excel. If you want to update a currency amount already in square brackets at the end of the file name, you can click this control again (or click the GO button again). It is not necessary to have the cursor in any particular location before clicking this option to add currency data to the end of the file name, but no matter where the cursor is on the renaming form the currency data will always be added to the end of the description portion of the file name.
• CLIPBOARD - Paste clipboard content at end of description = Click this option to quickly place the clipboard contents on the end of the description portion of the file name. This can be useful if you will want to repeatedly paste the same content on the end of the file names of a series of file being renamed.  
• LOWERCASE - Set description text to lower case = Click this button to get rid of all caps, or capital case, that may exist in the existing file name.
• RELOAD - Resets name, reopens file if set to open by default = Click this option if you regret edits to the file name and want to revert to the initial name, or if you closed the file and want to reopen it to inspect its contents again.
• DELETE - Replace file name with note earmarking file for deletion = If you wish to remove the file from the library, click this option to substitute “_Delete [Marked YYYY-MM-DD at hh h mm.ss]  - ” in place of the date at the start of the file name. When you are done renaming files then files with such notations will sort to the top in the list of files in Windows Explorer (when sorted by name) and you can easily delete those files.

 

“Default Settings” tab

The “Default Settings” tab in the new name definition area allows you to change default behaviours during file renaming: 

Additional information for these settings is as follows:

• Always open current file (when form opens, or when move to next file in list) = Use this switch to control whether files are opened by the macro as you move through the list of files queued for renaming. You may turn this off if you just want to perform reformatting of file names e.g. removing double spaces, or underscores, etc.
• Remove underscores not in pos. 11 by default = Check this box if you want to always eliminate underscores other than in position 11. Note that this setting will also preserve underscores after “Undated”, or “Various”, at the start of file names.
• Remove description portion by default = Check this box if you wish existing file name content to be entirely discarded as the file is loaded e.g. if file names are random text, or the file names are sequential numbers that are no longer relevant.
• Write following text into start of description by default = Use this control when you will always want the same text at the start of the description portion of the new file names e.g. if renaming a batch of invoices and you want each description to start with “Acme Ltd. Invoice # ”. Use a trailing space at the end of the default text as needed. Consider using this control in combination with the control above to remove the description portion by default and replace it with standardized text desired for all file names.
• Save and close at end of list = Check this box if you want the One-by-One Renaming Spreadsheet to automatically save and shut down when you get the end of the renaming list. This speeds things up if you often open this spreadsheet throughout the day to rename small batches of files.

 

“Use recent name” tab

The “Use recent name” tab in the new name definition area allows you to write any of the five most recently used names into the proposed new name filed. Just click on one of the last five names applied to write that previous name into the proposed new name field:

This can be useful when subsequent file names are to be very similar to recent previous ones.

 

Applying renaming

After the proposed new file name is fully defined, click the green “Rename” button to proceed with renaming, or click the orange “Skip to next file” button to skip renaming the current file, or the pink “Cancel/Quit” button to shut down the renaming form.

After editing the file name in the proposed new name field, a quick way to rename the file and proceed to the next file using the keyboard is to tap the Enter key twice i.e. tap Enter once to exit out of the proposed new name field and activate the Rename button, and then tap Enter again to depress the Rename button.

The red text above the green “Rename” button provides a warning if the date has not been updated since the file loaded i.e. when focussing on what descriptions should be it is sometimes easy to forget to update the date of the proposed new file name. You can also look back up the list of recently renamed files in the listbox at the top left of the renaming form to survey recent file name content (and assess whether perhaps you forgot to correctly define dates). To go back and edit the name of another file just double click the name for that file in that listbox at the top left of the renaming form.

 

Settings tab

The settings tab contains various settings and controls related to operation of the spreadsheet.

File types

Only files with file extensions listed in cell B7 will be processed by the One-by-One Renaming Spreadsheet, and all other file types will be ignored. Entry of file extensions in this field is necessary to ensure that system files will be skipped over. You can add additional file extensions to this cell to ensure the macro queues such file types for renaming, but do not include Excel file types (e.g. .xls, .xlsx, or .xlsm) as the macros cannot rename those.

Date format controls

This spreadsheet is premised on the assumption that most files will have dates in the format YYYY-MM-DD (or some slight variation of that) at the start of their file names. But that is not mandatory and the text “Undated”, or “Various”, can also easily be placed at the start of file names using the buttons in the date picker area on the renaming form. File names can also be edited manually to contain any content in any format at the start of their names. But as noted the spreadsheet is designed around the concept of having dates in the format YYYY-MM-DD (or similar) at the start of file names.

In cell C14 of the Settings tab you can specify the character to appear between the YYYY, MM, and DD portions of the date. A dash is recommended (compliant with ISO 8601, good visual separator, and most easily processed if file name data is later exported and processed in Excel). If all content is deleted from this cell then no characters or spaces will be placed between the portions of the date i.e. dates will be displayed as YYYYMMDD.

In cell C15 you can specify the character to appear after the date and before the description. An underscore is recommended (good visual separator, and it can be used as a delimiter if you later want to export the file name data into Excel).

In cell C16 you can specify the characters that you do not want to appear directly after the date. If the source files have any of these characters in position 11 then such characters will be deleted as the renaming form loads.

Settings for default behaviour

Rows 24 to 25 of the Settings sheet provide an alternative location to specify the default settings also available on the “Default Settings” tab in the new name definition area as discussed above. Entering TRUE or FALSE will result in the corresponding checkboxes on that form being either checked, or unchecked, respectively.

Currency symbol

In cell C29 you can specify the currency symbol to be used when the

“$$$ - Add / replace $ amount in square brackets at end descript” option is selected from the listbox below the proposed new name field. Even though that option in the listbox will always contain the $ symbol, the currency symbol written in for the proposed new file names will be whatever is entered in this cell e.g. €, £, ¥, etc.

Snippet lists

A key feature of the One-by-One Renaming Spreadsheet is to allow users to curate lists of snippets which can be quickly added to the proposed new file names to save typing them out. The snippets can be given category names in row 35 of the Settings sheet, and then up to 100 snippets can be defined on rows 36 to 135 of each category column.

 

Extra tips and cautions

Caution when clicking back up the list of files to edit a previously renamed file

If you are renaming files using the file renaming form and you have renamed a number of files, and you then double click on one of the already renamed files in the list in the top left listbox of the renaming form to go back and further edit it, if you have the boxes checked (on the Default Settings tab discussed above) for:

• “Remove description portion by default”, or (2)
• “Write following test into start of description by default”,

then when you click on that earlier file name its content will be modified by those default settings. To protect that previously defined file name you can uncheck those default setting checkboxes before double clicking on that previously defined file name. If you forget to uncheck those boxes and the file name is disrupted upon loading into the proposed new file name field, you can then restore the file name into the proposed new file name field by unchecking those default settings checkboxes and then clicking the RELOAD option below the proposed new file name field:

 

Locating the pdf window in a convenient place

You may find that the pdf window opens at an inconvenient location e.g. over top of the file renaming form. It may be that if you just move the Adobe window to a new location it will remain there as subsequent files are opened without any further disruptions. If not, a trick that sometimes works to get the Adobe window to persistently open in a particular location is to open a pdf file, then perform just a single operation of moving the Adobe window to a better location, and then shutting down the Adobe window. Subsequently opened Adobe windows should then open in that same location.  If it will take multiple steps to move the Adobe window to the better location e.g. first minimize, and then move sideways, you should perform those sequential steps one at a time, shutting down the Adobe window each time between the sequential steps. This “teach it how to open step by step” method seems strange, but does work in some cases.

 

Conclusion

The One-by-One Renaming Spreadsheet can speed up file renaming by saving the many clicks normally needed to open and close files that you need to review the contents of to determine appropriate file names, especially for pdf files where Adobe Standard (or Pro) is being used. It can also save typing by allowing quick definition of dates using the date picker, and by using pre-defined snippets to build out file names. While renaming files using metadata is always preferred, where a collection of files does need renaming one-by-one, this spreadsheet may speed up the process.