Document Control Template Feature Overview

This article provides details of the features included in the Document Control Template

Purpose
Set up the template
- Create variables
- List required files
Using the template
- Roll forward next reporting cycle
Copying and duplicating the template
Known limitations

Purpose

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. For each reporting cycle, the file path changes to incorporate the new reporting period. As an example, the April 2025 stock report may be saved in the Z:\Finance\Reporting\2025\April\ 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.

Once we know the exact location of all the files we need, we can easily use them within Power Query an other automation processes.

Set up the template

To get the benefit from the template, it is important to set it up correctly.

Create variables

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 2025
Variable 2 has been renamed Month and given the value April

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 Listing)
Folder Path: The folder path where the document should be saved.
The formula in cell D18 is:
="C:\Example\Month End Reporting\"&D9&"\"&D10&"\Stock\"
This formula concatenates the values included in the Variables section to generate the correct file path of:
C:\Example\Month End Reporting\2025\April\Stock\
File Name: The name to be given to the file.
The formula in cell E18 is:
="Stock Listing - "&D10&" "&D9&".xlsx"
This formula concatenates the values included in the Variables section to give the file the correct name of:
Stock Listing - April 2025.xlsx
Full File Path: This is the full file path of the file, using both the Folder Path and File Name columns. This is calculated automatically based on the Folder Path and the File Name.

Note:

The cells highlighted in green indicate that a file or folder exists within that location. Existence is checked whenever a new cell is selected.

To help the setup process, use the File Name, File Path and Full Path buttons. These 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 or Copy
The file is moved/copied into the file path shown in the selected cell. If necessary, any missing folders are created automatically, and color will change to indicate the file now exists in that location.

Check for file / folder existence

Any folders or files that exist are highlighted in green.

If a file exists, but is locked 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, clicking any cell in the sheet will automatically update the color coding.

Replace files

If a new version of a file is received, follow the same process as above to move or copy the file into the correct location.

A message box appears to ask if the original file should be archived or deleted.

Archiving files renames the existing file to include the date and time of the archive. The new file is located at the specified file path.

Delete files

To delete a file that already exists in the file path:

Select the cells with the file paths.
Click Delete.
A confirmation message appears.
Click OK to delete the file.

Archive files

To archive a file that already exists in the file path:

Select the cells with the file paths.
Click Archive.

Archiving files renames the original to include the word Archive and 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.

By default, archived files are saved in the same folder as the original file. To save all archived filed to a specific folder use the Archive Folder section at the top of the template.

Create Folders

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 that exists, click on the Folder or File icon next to the file or folder path.

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.

Copying between workbooks

The Document Control Template is encapsulated within a single worksheet. Therefore, it is possible to add copies into other workbooks.

To copy the template into another workbook:

Right-click on the Document Control Template tab
Click Copy or Move… from the menu
Select the desired location from the drop down list
Check the Create Copy box
Click OK

A fully functioning version of the Document Control Template now exists in the selected workbook.

As the Document Control Template uses VBA code, it will be necessary to save the workbook with an .xlsm file type, and the file will be subject to the standard Excel VBA security requirements.

Limitations

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: