How To Keep Control Of Contents Templates
If you're working on a project or a monthly reporting process, document command tin can be the most time-consuming activity. Often in that location are then many documents that it is hard to keep up with all the inputs and revisions flight almost all over the place. Which documents have we got? Which documents are missing? Take nosotros 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 Command Template for Excel, information technology enables me to go along rails of documents for all of import processes. If you lot recollect I'yard just talking nigh using a spreadsheet like a listing, then yous'll exist pleasantly surprised. It's so much more than that.
By using the Document Control Template, which I am about to share with yous, information technology is possible to
- "Cheque in" documents – moving and re-naming documents into specific folders with specific file names
- See at a glance the documents which accept been received or are missing
- Go on track of the "current" version, and retain copies of all previous versions of a file
- Delete former files without needing to find it in the folder construction
- Open of files in their default awarding with a single click
- "Curl-over" the Document Command Template for the next catamenia/version
- Work with all file types, non just Excel workbooks
The biggest benefit of using a Document Control Template is knowing the specific file path of each document. Every bit it and 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 verbal file path of each document is at present known.
Much of the code for the Certificate Control Template tin be found in the VBA Code Snippets library. To notice out more than information on whatever 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 ostend existence of files
- Create, delete, rename and ostend being of folders
Introducing the Certificate Control Template for Excel
Download the Certificate Control Template
You lot do not need to download the file to create your own Document Command Template, all the instructions and VBA code are in the sections below. But, information technology will exist much easier to follow along if you accept the Template downloaded. If y'all but want to get stuck right in, you can download the file and ignore the 2d part of this article.
Download the file: Document Command Template
Disclaimer:
Whilst I try to create rubber and reliable templates and add together-ins, I can (and often practise) brand mistakes. Please backup copies of your files before opening this template or running any VBA code. If you do notice any bugs or errors, delight let me know using my contact folio.
By using any templates, add-ins, downloads or information from the site, you agree that I will non be held liable for any type of amercement, and use is entirely at your own risk.
Using the Certificate Control Template?
Below is a screenshot from the Document Control Template, each feature is described below.
Check In Push button:
To 'Check In' a file, select a jail cell with a valid file path (eastward.k. Cells E15-E19) then click the Check In push. A file option window will open, navigate to the location where the file is currently saved, click Open up. For this button to piece of 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 jail cell.
Open Push:
Select a prison 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 inside its default awarding.
Update Button:
The Update push button functions in a similar way to the Bank check In button. The key divergence beingness the file must already be in the selected location. The existing file is renamed to include the day and time information technology was replaced, and then the new file is renamed to the file proper noun 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 exist 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, and so does the file path. This makes the template useful for any regular reporting cycles, just modify the variables and subsequent files are saved in a new location.
Existence:
Cells B15-B19 displays Truthful or Simulated to indicate if a file already exists in the location shown in Cells E15-E19.
File Path & File Proper name:
In my template, the File Path (Cells C15-C9) is based on the value in Cell B9. However, the file path could exist unique for each file, and could comprise other variables.
The file names (Cells D15-D19) are the names which the files will be renamed to.
Alarm messages
Using invalid file paths volition 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 lot to piece of work with it and see what yous can achieve.
Create your ain Document Control Template
If you're interested in making your ain 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 lawmaking below into a Module inside the Visual Basic Editor. I won't go through the code line past line, in that location are comments within the code provide some guidance. Or check out the articles in the VBA Lawmaking Snippets Library for farther information.
Option Explicit Function doesFileExist(filePath) As Boolean 'Brand the calculation volatile, forcing recalculation when used equally 'a worksheet function Application.Volatile doesFileExist = Dir(filePath) <> "" End Function
Function doesFolderExist(folderPath) Equally Boolean 'If blank cell selected volition cause error: render false 'If not bare, and then check for binder existence If folderPath = "" And so doesFolderExist = False Else doesFolderExist = Dir(folderPath, vbDirectory) <> "" End If End Office
Office IsFileOpen(fileName As Cord) Dim fileNum Every bit Integer Dim errNum As Integer 'Permit all errors to happen On Error Resume Next fileNum = FreeFile() 'Effort to open and close the file for input. 'If Error it means the file is already open Open fileName For Input Lock Read Every bit #fileNum Close fileNum 'Get the error number errNum = Err 'Exercise not allow errors to happen anymore On Error GoTo 0 'Cheque the Error Number Select Case errNum 'errNum = 0 means no errors, therefore file closed Case 0 IsFileOpen = False 'errNum = lxx means the file is already open up Case lxx IsFileOpen = True 'Something else went wrong Example Else IsFileOpen = errNum Terminate Select Cease Office
Sub DocControlCheckIn() 'Assign this Macro to the Check In push FileActions ("CheckIn") Finish Sub
Sub DocControlOpen() 'Assign this Macro to the Open button FileActions ("Open") Terminate 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") Cease Sub
Sub FileActions(action Equally String) Dim folderPath As Cord Dim errorCount As Integer Dim fileName As String Dim positionOfSlash As Integer Dim msgAns As Long 'Cheque if selection is blank If Selection.Value = "" Then errorCount = errorCount + ane 'Go the binder path from the selected cell, by finding concluding backslash positionOfSlash = InStrRev(Option.Value, "\") If positionOfSlash >= ane Then folderPath = Left(Selection.Value, positionOfSlash) Else folderPath = Selection.Value errorCount = errorCount + 1 Stop If 'Cheque if the folder path exists If doesFolderExist(folderPath) = Fake So errorCount = errorCount + 1 'Brandish error bulletin 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 Command Template" Exit Sub Cease If 'Check if file is already open If IsFileOpen(Selection.Value) = True So MsgBox Pick.Value & " is already open past your or another user.", _ vbExclamation, "Certificate Control Template" Exit Sub End If Select Instance action 'Delete file if it exists, includs confirmation to delete Case "Delete" If doesFileExist(Choice.Value) = True And so msgAns = MsgBox("Are you sure you wish to delete " & _ Selection.Value & "?", vbYesNo, "Document Control Template") If msgAns = vbYes Then Impale Pick.Value Stop If Else MsgBox Selection.Value & " cannot be deleted every bit it does not be.", _ vbExclamation, "Certificate Control Template" Exit Sub End If 'Check In the file if the file does not already exist Instance "CheckIn" If doesFileExist(Selection.Value) = True Then MsgBox "Unable to Check In " & Selection.Value & _ " equally the file already exists", vbExclamation, _ "Document Command Template" Exit Sub Else Call saveFileInLocation(Pick.Value) Cease If 'Open the file if information technology exists Case "Open up" If doesFileExist(Selection.Value) = True Then CreateObject("Shell.Application").Open up (Selection.Value) Else MsgBox "Unable to open " & Option.Value & _ " as the file does not exist.", vbExclamation, _ "Certificate Control Template" Exit Sub End If 'Update the file if information technology exists Case "Update" If doesFileExist(Option.Value) = True Then fileName = folderPath & Mid(Left(Selection.Value, _ InStrRev(Selection.Value, ".") - 1), Len(folderPath) + 1) & "_" & _ Format(Now(), "yymmddhhmmss") & _ Mid(Pick.Value, InStrRev(Choice.Value, ".")) Name Choice.Value As fileName Call saveFileInLocation(Choice.Value) Else MsgBox "Unable to update " & Choice.Value & _ " as the file does not already be.", vbExclamation, _ "Document Control Template" Leave Sub End If End Select 'Recalculate sheet. This should strength the doesFileExist function in 'Cells B15-B19 to recalculate to show correct TRUE/FALSE value ActiveSheet.Calculate End Sub
Sub saveFileInLocation(savePath Every bit String) Dim dialogBox As FileDialog Dim selectedFile Every bit String Set dialogBox = Awarding.FileDialog(msoFileDialogOpen) 'Practise non allow multiple files to be selected dialogBox.AllowMultiSelect = Imitation 'Fix the championship of the DialogBox dialogBox.Championship = "Select a file" 'Show the dialog box and assign full file path and file name to variable If dialogBox.Show = -1 And then selectedFile = dialogBox.SelectedItems(1) Cease If 'Check if the selectedFile is already open If IsFileOpen(selectedFile) = Truthful And so MsgBox selectedFile & " is already open up past your or another user.", _ vbExclamation, "Document Control Template" Exit Sub Stop 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 Terminate Sub
Creating the buttons on the worksheet
At that place are 4 main deportment inside the VBA code to a higher place; Check In, Open, Update, Delete. Create a button for each activeness. From the Developer Ribbon click Insert -> Class Controls -> Push button (Form Controls). Click and describe a rectangle on the worksheet. This creates a new button. Classify 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.
File paths
The last affair required to create the template are cells with folder and file paths. How you lot create these paths is up to you, in my template I accept 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 be in that location, for Cheque In, the folder must exist, but the file does not be.
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 non exist.
Check for existence
The VBA lawmaking includes a User Divers Function, which checks for a file'south being.
=doesFileExist(E15)
In the User Defined Part higher up the file path is contained in Cell E15. The function will return TRUE if the file exists and Faux if it does not.
Save the Template
The final footstep is to save the file you have created as a macro enabled workbook, an .xlsm file type.
Determination
There was a lot of code in at that place, but go on referring dorsum 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.
Get our Free VBA eBook of the 30 about useful Excel VBA macros.
Automate Excel so that yous tin save time and cease doing the jobs a trained monkey could do.
Claim your complimentary eBook
Don't forget:
If you lot've constitute this post useful, or if you have a better arroyo, and so please leave a comment beneath.
Do you need help adapting this to your needs?
I'm guessing the examples in this post didn't exactly meet your state of affairs. We all use Excel differently, so information technology's incommunicable to write a post that will meet everybody's needs. By taking the time to empathise the techniques and principles in this post (and elsewhere on this site) yous should be able to adapt it to your needs.
Simply, if you're still struggling you should:
- Read other blogs, or scout YouTube videos on the same topic. Y'all volition benefit much more by discovering your own solutions.
- Ask the 'Excel Ninja' in your function. Information technology'southward amazing what things other people know.
- Enquire a question in a forum like Mr Excel, or the Microsoft Answers Community. Recall, the people on these forums are by and large giving their time for free. So accept care to arts and crafts your question, make sure information technology's clear and concise. List all the things you've tried, and provide screenshots, code segments and example workbooks.
- Apply Excel Rescue, who are my consultancy partner. They aid past providing solutions to smaller Excel issues.
What next?
Don't go withal, there is enough more to learn on Excel Off The Grid. Check out the latest posts:
How To Keep Control Of Contents Templates,
Source: https://exceloffthegrid.com/document-control-template/
Posted by: reifmontering.blogspot.com
0 Response to "How To Keep Control Of Contents Templates"
Post a Comment