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
Other features
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 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.

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 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:
="C:\Examples\Reporting\"&C8&"\"&C9&"\"
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:
=C18&D18

Note:

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.

Replace files

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.

Delete files

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

Archive files

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.

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 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.

Other features

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)

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: