This article provides details of the features included in the Document Control Template
- Set up the template
- Using the template
- Check for file / folder existence
- Copy / move files
- Replace files
- Delete files
- Archive files
- Create folders
- Open files / folders
- Other features
- Known limitations
Most reporting, month-end, or forecasting/budgeting processes rely on managing many files, often submitted by multiple individuals across various departments.
The files used in these processes are usually saved in folder locations where the folder path contains information about the file. Then, each reporting cycle, the file path changes to incorporate the new reporting period. As an example, the June 2023 stock report may be saved in the Z:\Finance\Reporting\2023\June\ folder.
The Document Control Template is the tool for tracking these files and ensuring they are saved in the correct folders with the correct file names.
When we know the exact location of all the files we need, we can use them within automation processes.
Set up the template
To get the benefit from of template, it is important to set it up correctly. The setup process ensures any variable elements in the folder path are created and all files are listed.
Use the Variables section to list the dynamic parts of the file path.
In the screenshot above:
- Variable 1 has been renamed Year and given the value of 2023
- Variable 2 has been renamed Month and given the value June
- Variable 3 has been renamed Month Number and given the value 6
List required files
The Documents section lists all the required inputs.
- Document Name: The name you use to refer to the file (e.g. Stock Report)
- Folder Path: The folder path where the document should be saved.
The formula in cell C18 is:
This formula concatenates the values included in the Variables section to generate the correct file path
- File Name: The name to be given to the file.
The formula in cell D18 is:
="Stock Report P"&C10&".xlsx"
This formula concatenates the values included in the Variables section to give the file the correct name.
- Full File Path: This is the full file path of the file, using both the Folder Path and File Name columns.
The formula in cell E18 is:
- In the screenshot, cell C18 has turned green because the folder exists.
- Cell E18 has not turned green because the file does not yet exist in the file path.
To help the setup process, use the File Name, File Path and Full Path buttons. The buttons insert the information about a file/folder into the selected cell.
To use the buttons:
- Select the cell to populate
- Depending on requirements, click the File Name, File Path or Full Path button
- Navigate to the folder or file, then click Insert.
- The relevant information is inserted into the cell
Use the template
Copy / move files
When you receive or download a file, use the Document Control Template to ensure the file is saved in the correct location.
- Select the cell with the file path in the Full File Path column
- Click the Copy In or Move In buttons:
- Move In – moves the file from the original location
- Copy In – copies the file in the original location
- Navigate to the file and click Move/Copy
- The file is moved/copied into the file path shown in the selected cell. If necessary, any missing folders are created automatically.
Tip: When using the Move-In method, the original save location is irrelevant, as the file will be moved. Select somewhere easy to access, such as Downloads.
Check for file / folder existence
Any folders or files which exist are highlighted in green. If a file exists, but is not available for editing (i.e. it is locked by another individual), the cell color is blue.
Excel does not constantly track file and folder existence. If a file or folder is updated manually, click the Refresh button to update the color coding.
If a new version of a file is received, follow the same process as above to move or copy the file.
A message box appears to ask if the original file should be archived or deleted the file.
Archiving files renames the original file to include the date and time of archive. The new file occupies the file path.
To delete a file that already exists in the file path:
- Select the cell with the file path
- Click Delete
- A confirmation message appears.
- Click OK to delete the file
To archive a file that already exists in the file path:
- Select the cell with the file path
- Click Archive
Archiving files renames the original to include the current date and time. As the file is not deleted, no confirmation message appears. If necessary an “archived” file can be re-instated by using the Copy In / Move In buttons to place the file back into the file path.
If using Power Query to connect to a folder, filter out any files containing Archive_ to remove archived files.
To create folders without copying or moving any files:
- Select a cell with the required folder path
- Click Create Folders
- All the missing folders along the folder path are created
Open files / folders
To open a file or folder which exists:
- Select a cell with the required folder or file path
- Click Open
- The file or folder opens using the default application.
Roll forward for the next reporting cycle
When the next report, month-end, or forecast occurs, update the variables for the new folder paths. As the Documents section is created using formulas, the folder and file paths update.
File and folder existence are checked and color coding is updated accordingly. Any file or folder actions are now based on the newly calculated file path from the updated variables.
Pencil icon / renaming the template – Click the pencil icon in cell A1 to edit the heading in cell B1
Help button – The Help button provides a link to the Document Control Template help pages (https://exceloffthegrid.com/document-control-help/)
Excel Off The Grid website – Click the Excel Off The Grid logo to visit the website and discover the latest blog posts and information (https://exceloffthegrid.com)
Excel is a vast application. Therefore, sufficient testing must always be undertaken to ensure the Document Control Template works as expected.
OneDrive / SharePoint
The Document Control Template works well with files saved locally and on network drives. Files saved in locations that are synced with SharePoint or OneDrive may cause issues, as the Document Control Template may execute faster than file syncing to OneDrive or SharePoint.
Discover other articles: