If you’re working on a project or a monthly reporting process, document control can be the most time-consuming activity. Often there are so many documents that it is difficult to keep up with all the inputs and revisions flying about all over the place. Which documents have we got? Which documents are missing? Have we got the latest version of this or that? When was a document revised, or superseded by a new version? Aaaahhhhhh! Too many questions. This is why I created a Document Control Template for Excel, it enables me to keep track of documents for all important processes. If you think I’m just talking about using a spreadsheet like a list, then you’ll be pleasantly surprised. It’s so much more than that.
By using the Document Control Template, which I am about to share with you, it is possible to
- “Check in” documents – moving and re-naming documents into specific folders with specific file names
- See at a glance the documents which have been received or are missing
- Keep track of the “current” version, and retain copies of all previous versions of a file
- Delete old files without needing to find it in the folder structure
- Open of files in their default application with a single click
- “Roll-over” the Document Control Template for the next period/version
- Work with all file types, not just Excel workbooks
The biggest benefit of using a Document Control Template is knowing the specific file path of each document. As it then becomes possible to use other automation macros, such as consolidating workbooks, merging PDFs, or printing specific schedules of each workbook. This automation is much harder or impossible if = the exact file path of each document is now known.
Much of the code for the Document Control Template can be found in the VBA Code Snippets library. To find out more information on any of the VBA code below, follow these links.
- Selecting a file using the File Dialog Box
- Find out if a file is already open by you or another user
- Copy, move, rename, delete and confirm existence of files
- Create, delete, rename and confirm existence of folders
Introducing the Document Control Template for Excel
Download the Document Control Template
The Document Control Template is available for newsletter subscribers to download for free.
You do not need to download the file to create your own Document Control Template, all the instructions and VBA code are in the sections below. But, it will be much easier to follow along if you have the Template downloaded. If you just want to get stuck right in, you can download the file and ignore the second part of this article.
Existing newsletter subscribers can enter their name and e-mail address into the form below for instant access to the download. Or, if you are not a subscriber yet, can enter your name and e-mail address to become a one.
Using the Document Control Template?
Below is a screenshot from the Document Control Template, each feature is described below.
Check In Button:
To ‘Check In’ a file, select a cell with a valid file path (e.g. Cells E15-E19) then click the Check In button. A file selection window will open, navigate to the location where the file is currently saved, click Open. For this button to work a “valid file path” is where the folder exists, but the file name does not. The selected file is automatically moved to the file path selected in the cell.
Select a cell containing a valid file path (e.g. Cells E15-E19), then click the Open button. For this button to work a “valid file path” is where both the folder and file exist. The file will open within its default application.
The Update button functions in a similar way to the Check In button. The key difference being the file must already exist in the selected location. The existing file is renamed to include the day and time it was replaced, then the new file is renamed to the file name in the selected cell.
Select a cell containing a valid file path, where the folder and file already exist, click Delete. The file will be deleted.
Cells B7 and B8 are variables. These are used to construct the file path in Cell B9. If the year or period changes, so does the file path. This makes the template useful for any regular reporting cycles, just change the variables and subsequent files are saved in a new location.
Cells B15-B19 displays TRUE or FALSE to indicate if a file already exists in the location shown in Cells E15-E19.
File Path & File Name:
In my template, the File Path (Cells C15-C9) is based on the value in Cell B9. However, the file path could be unique for each file, and could incorporate other variables.
The file names (Cells D15-D19) are the names which the files will be renamed to.
Using invalid file paths will trigger warning messages similar to the one below.
The VBA code also checks to ensure the file is not already open by another user.
There are many ways this file could be used, it is up to you to work with it and see what you can achieve.
Create your own Document Control Template
If you’re interested in making your own Document Control Template from scratch, or if you’re a VBA fan, then all the necessary steps and code are contained below.
VBA Code for the Document Control Template
Copy the code below into a Module within the Visual Basic Editor. I won’t go through the code line by line, there are comments within the code provide some guidance. Or check out the articles in the VBA Code Snippets Library for further information.
Function doesFileExist(filePath) As Boolean 'Make the calculation volatile, forcing recalculation when used as 'a worksheet function Application.Volatile doesFileExist = Dir(filePath) <> "" End Function
Function doesFolderExist(folderPath) As Boolean 'If blank cell selected will cause error: return false 'If not blank, then check for folder existence If folderPath = "" Then doesFolderExist = False Else doesFolderExist = Dir(folderPath, vbDirectory) <> "" End If End Function
Function IsFileOpen(fileName As String) Dim fileNum As Integer Dim errNum As Integer 'Allow all errors to happen On Error Resume Next fileNum = FreeFile() 'Try to open and close the file for input. 'If Error it means the file is already open Open fileName For Input Lock Read As #fileNum Close fileNum 'Get the error number errNum = Err 'Do not allow errors to happen anymore On Error GoTo 0 'Check the Error Number Select Case errNum 'errNum = 0 means no errors, therefore file closed Case 0 IsFileOpen = False 'errNum = 70 means the file is already open Case 70 IsFileOpen = True 'Something else went wrong Case Else IsFileOpen = errNum End Select End Function
Sub DocControlCheckIn() 'Assign this Macro to the Check In button FileActions ("CheckIn") End Sub
Sub DocControlOpen() 'Assign this Macro to the Open button FileActions ("Open") End Sub
Sub DocControlDelete() 'Assign this Macro to the Delete button FileActions ("Delete") End Sub
Sub DocControlUpdate() 'Assign this Macro to the Update button FileActions ("Update") End Sub
Sub FileActions(action As String) Dim folderPath As String Dim errorCount As Integer Dim fileName As String Dim positionOfSlash As Integer Dim msgAns As Long 'Check if selection is blank If Selection.Value = "" Then errorCount = errorCount + 1 'Get the folder path from the selected cell, by finding final backslash positionOfSlash = InStrRev(Selection.Value, "\") If positionOfSlash >= 1 Then folderPath = Left(Selection.Value, positionOfSlash) Else folderPath = Selection.Value errorCount = errorCount + 1 End If 'Check if the folder path exists If doesFolderExist(folderPath) = False Then errorCount = errorCount + 1 'Display error message for selecting a cell with an invalid file path If errorCount >= 1 Then MsgBox "The selected cell does not contain a valid file path.", _ vbExclamation, "Document Control Template" Exit Sub End If 'Check if file is already open If IsFileOpen(Selection.Value) = True Then MsgBox Selection.Value & " is already open by your or another user.", _ vbExclamation, "Document Control Template" Exit Sub End If Select Case action 'Delete file if it exists, includs confirmation to delete Case "Delete" If doesFileExist(Selection.Value) = True Then msgAns = MsgBox("Are you sure you wish to delete " & _ Selection.Value & "?", vbYesNo, "Document Control Template") If msgAns = vbYes Then Kill Selection.Value End If Else MsgBox Selection.Value & " cannot be deleted as it does not exist.", _ vbExclamation, "Document Control Template" Exit Sub End If 'Check In the file if the file does not already exist Case "CheckIn" If doesFileExist(Selection.Value) = True Then MsgBox "Unable to Check In " & Selection.Value & _ " as the file already exists", vbExclamation, _ "Document Control Template" Exit Sub Else Call saveFileInLocation(Selection.Value) End If 'Open the file if it exists Case "Open" If doesFileExist(Selection.Value) = True Then CreateObject("Shell.Application").Open (Selection.Value) Else MsgBox "Unable to open " & Selection.Value & _ " as the file does not exist.", vbExclamation, _ "Document Control Template" Exit Sub End If 'Update the file if it exists Case "Update" If doesFileExist(Selection.Value) = True Then fileName = folderPath & Mid(Left(Selection.Value, _ InStrRev(Selection.Value, ".") - 1), Len(folderPath) + 1) & "_" & _ Format(Now(), "yymmddhhmmss") & _ Mid(Selection.Value, InStrRev(Selection.Value, ".")) Name Selection.Value As fileName Call saveFileInLocation(Selection.Value) Else MsgBox "Unable to update " & Selection.Value & _ " as the file does not already exist.", vbExclamation, _ "Document Control Template" Exit Sub End If End Select 'Recalculate sheet. This should force the doesFileExist function in 'Cells B15-B19 to recalculate to show correct TRUE/FALSE value ActiveSheet.Calculate End Sub
Sub saveFileInLocation(savePath As String) Dim dialogBox As FileDialog Dim selectedFile As String Set dialogBox = Application.FileDialog(msoFileDialogOpen) 'Do not allow multiple files to be selected dialogBox.AllowMultiSelect = False 'Set the title of the DialogBox dialogBox.Title = "Select a file" 'Show the dialog box and assign full file path and file name to variable If dialogBox.Show = -1 Then selectedFile = dialogBox.SelectedItems(1) End If 'Check if the selectedFile is already open If IsFileOpen(selectedFile) = True Then MsgBox selectedFile & " is already open by your or another user.", _ vbExclamation, "Document Control Template" Exit Sub End If 'Catch errors when moving file to final location On Error Resume Next 'Rename the file Name selectedFile As savePath If Err.Number <> 0 Then MsgBox "Unable to Check In the file" End If On Error GoTo 0 End Sub
Creating the buttons on the worksheet
There are 4 main actions within the VBA code above; Check In, Open, Update, Delete. Create a button for each action. From the Developer Ribbon click Insert -> Form Controls -> Button (Form Controls). Click and draw a rectangle on the worksheet. This creates a new button. Allocate each button created to the 4 macros
- Check In – Macro: DocControlCheckIn
- Open – Macro: DocControlOpen
- Update – Macro: DocControlUpdate
- Delete – Macros: DocControlDelete
Screen shot below shows how to create a button and allocate a Macro.
Right click on the Button to edit the text to your requirements.
The last thing required to create the template are cells with folder and file paths. How you create these paths is up to you, in my template I have chosen to use Year and Month as variables to construct the file path. This enables the document to be used for a monthly process. File paths must be valid. For Open, Delete and Update the specified file must already exist in that location, for Check In, the folder must exist, but the file does not exist.
File paths must be valid. For Open, Delete and Update the specified file must already exist in that location, for Check In, the folder must exist, but the file most not exist.
Check for existence
The VBA code includes a User Defined Function, which checks for a file’s existence.
In the User Defined Function above the file path is contained in Cell E15. The function will return TRUE if the file exists and FALSE if it does not.
Save the Template
The final step is to save the file you have created as a macro enabled workbook, an .xlsm file type.
There was a lot of code in there, but keep referring back to the original document, and it should all makes sense. Having a document control template has saved me hours of time every month, I hope it will give you similar benefits too.