Get our FREE VBA eBook of the 30 most useful Excel VBA macros.

Automate Excel so that you can save time and stop doing the jobs a trained monkey could do.

Claim your free eBook


Document Control Template

Document control template

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.

Introducing the Document Control Template for Excel

Download the Document Control Template

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.

Download Icon
Download the file: Document Control Template

Disclaimer:
Whilst I try to create safe and reliable templates and add-ins, I can (and often do) make mistakes.  Please backup copies of your files before opening this template or running any VBA code.  If you do find any bugs or errors, please let me know using my contact page.

By using any templates, add-ins, downloads or information from the site, you agree that I will not be held liable for any type of damages, and use is entirely at your own risk.

Using the Document Control Template?

Below is a screenshot from the Document Control Template, each feature is described below.

Document Control Template - Screen Shot

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.

Open Button:
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.

Update Button:
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.

Delete Button:
Select a cell containing a valid file path, where the folder and file already exist, click Delete.  The file will be deleted.

Cell Variables:
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.

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

Warning messages

Using invalid file paths will trigger warning messages similar to the one below.

Document Control Template Example Error Message

The VBA code also checks to ensure the file is not already open by another user.

Document Control Template - File Open

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.

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

Create Button for Document Control Template

Right click on the Button to edit the text to your requirements.

File paths

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.

=doesFileExist(E15)

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.

Conclusion

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.


Headshot Round

About the author

Hey, I’m Mark, and I run Excel Off The Grid.

My parents tell me that at the age of 7 I declared I was going to become a qualified accountant. I was either psychic or had no imagination, as that is exactly what happened. However, it wasn't until I was 35 that my journey really began.

In 2015, I started a new job, for which I was regularly working after 10pm. As a result, I rarely saw my children during the week. So, I started searching for the secrets to automating Excel. I discovered that by building a small number of simple tools, I could combine them together in different ways to automate nearly all my regular tasks. This meant I could work less hours (and I got pay raises!). Today, I teach these techniques to other professionals in our training program so they too can spend less time at work (and more time with their children and doing the things they love).


Do you need help adapting this post to your needs?

I'm guessing the examples in this post don't exactly match your situation. We all use Excel differently, so it's impossible to write a post that will meet everybody's needs. By taking the time to understand the techniques and principles in this post (and elsewhere on this site), you should be able to adapt it to your needs.

But, if you're still struggling you should:

  1. Read other blogs, or watch YouTube videos on the same topic. You will benefit much more by discovering your own solutions.
  2. Ask the 'Excel Ninja' in your office. It's amazing what things other people know.
  3. Ask a question in a forum like Mr Excel, or the Microsoft Answers Community. Remember, the people on these forums are generally giving their time for free. So take care to craft your question, make sure it's clear and concise.  List all the things you've tried, and provide screenshots, code segments and example workbooks.
  4. Use Excel Rescue, who are my consultancy partner. They help by providing solutions to smaller Excel problems.

What next?
Don't go yet, there is plenty more to learn on Excel Off The Grid.  Check out the latest posts:

12 thoughts on “Document Control Template

  1. Mitchell Allen says:

    Hi Mark,

    It’s always fun to see what you get up to. I’ve been using classes to encapsulate much of the automation that I have to do regularly.

    I’m ready to chuck them all in favor of Power Query, though. You get all the file management and none of the VBA. Of course, combining PQ with your Document Template would be like greasing napalm on a stick of dynamite! LOL

    Thanks for sharing.

    Cheers,

    Mitch

    • Excel Off The Grid says:

      Hey Mitch – thanks for dropping by.

      You’re right, PowerQuery and my Document Control Template would work really well together. Once you can easily control file names the opportunities for automation with PowerQuery are massive.

      Document control has transformed how I work, so I’m glad that others can see the benefit too.

  2. Laura Michelle Gonzalez says:

    I’m unable to “Check In” with any of the cells in column E. I receive the message “the selected cell does not contain a valid file path”. Do you have any suggestions to help?

    • Excel Off The Grid says:

      Hi Laura – You need to click on the full file path in column E, plus the folder path must already exist.

      For example, if the full file path were C:\Users\marks\Documents\Test Folder\Test File.xlsx
      By clicking Check In, it will save the Test File.xlsx in the folder called C:\Users\marks\Documents\Test Folder.
      But the folder must exist, it does not create the folder automatically.

      Hope that helps.

  3. Nguyễn Thanh Phong says:

    Sorry can you explain more for me about function of “check in” button, Does it can use for both option (1.check file name was in folder or not , if file already exitst, it’s will rename the filename which in coloumn E to filename in Coloumn D, isn’t it. Thanks your alot, i very like your VBA, it’s so interesting.

    • Excel Off The Grid says:

      “Check in” is used to move the move a file to a specific location listed within column E. Column B will show whether a file with that name already exists. “Check in” should only work where a file does not exist in that location.

      If a file already exists in that location, you can use the “Update” button. This will create a backup copy of the original file and save the new file in the specified location.

  4. Nguyễn Thanh Phong says:

    I very like your idead , it’s help my work. I want to ask “can i built folderpath like value dynamic (Eg. coloumn E will have multi filepath and multi folderpath in coloumn D,and what should i do ?”) because i’m new learn VBA. Thanks for your share and help!

    • Excel Off The Grid says:

      The folder paths in Column C can be anything you want, provided is it a valid folder. You can use worksheet formulas to create the folder path in these cells, the values in B7, B8 and B9 are purely for illustration purposes.

  5. Ozgur says:

    Dear Mark,

    Thank you for sharing your document control template!

    I had a question about using the check in. I have made a folder and filled in the correct path. But in the description above you state that to check in we first select a path in Column E and then add our file. But then the file is renamed to the name in Column D.

    How is the function intended to be used? Do we first have to change the name of the of the file in Column D and only after that select the path in Column E?

    Many thanks in advance.

    Ozgur

    • Excel Off The Grid says:

      Hi Ozgur – Maybe I need to update the post to make things a bit clearer.

      Column C contains the folder path where the file should be saved.
      Column D is the file name you want to use.
      Column E is the combination of Column C and Column D (i.e. the full file path).

      Select the full file path in Column E and click Check In. Select the file to move, then macro will then move and rename the file to be the same as Column E.

      Does that help?

      • Ozgur says:

        Hi Mark. Thank you for your fast reply and clarification. In the post it wasn’t clear that we first must define the desired path in C and the desired name in D and only after that check in the file with column E. But is all clear now. Thank you very much and keep up the good work!

  6. Jo nMallek says:

    Hi:
    Getting the standard run-time error on the update, cannot figure out why.
    Name = folderPath & Mid(Left(Selection.Value, InStrRev(Selection.Value, “.”) – 1), Len(folderPath) + 1) & “_” & Format(Now(), “yymmddhhmmss”) & Mid(Selection.Value, InStrRev(Selection.Value, “.”))

Leave a Reply

Your email address will not be published. Required fields are marked *