Version Control Keeping track of different versions of a file has always been a pain for me. I am grateful that I have access to Microsoft SharePoint at my company and am able to easily save a new version without even thinking. However I received a question from a reader who goes by the name Kauket, asking if it was possible to automate saving a new version for a file. This was a great question and really caught my attention. Let's walk through how I personally would tackle this problem and see if we can come up with an efficient solution. The Logic Let's first think through what we will need to code. Below I will list a few of the major capabilities our VBA code will need to possess. • Determine if a previous version already exists • Determine where the file is saved (or if it is saved at all) • Create a new version with an incremental version extension (for example adding '_v2' to the end of the file name) I decided to write VBA code for Excel as well as Miscrosoft Word and PowerPoint in case you would like to use the same process with your documents and presentations. Luckily there aren't too many differences between the three macros sets. Function To Determine If File Exists: In order for the main VBA code to work, you will need to use a small function that determines if a specific file (via file path) exists on your computer. This is very important in order to determine the proper version number to use. Vba Auto Increment File Name Is Too Long. (vba) How to create an. Since there are several ways in which you can structure a macro to delete blank rows or rows. Excel Formulas, Data, Tables, & VBA Ways to use Excel formulas and data, including worksheet functions, range names, cell references, databases, lookup methods. Join them; it only takes a minute. Since there are several ways in which you can structure a macro to delete blank rows or rows with empty cells, at the end of this tutorial I. I found a great article on Ron de Bruin's website detailing various ways to check if something exists with VBA code. Be sure to check out his full article, as it is a great read. The below function code is what I was able to glean from Ron's article. ![]() MAKE SURE that you include this function along with any of the application macro code versions shown below. You can just paste the below code below either the Excel, PowerPoint, or Word subroutines. VBA Code of Version Control For Excel Let's start with the Excel version of the VBA code, as I assume most of you reading are interested in using the version control capabilities within Excel (since you make up the bulk of my readers). The code has only one input that you may want to change and that is the code variable VersionExt. ![]() I made the extension default to '_v' (ie myReport_v2.xlsx) but you can just as easily change it to ' v' (ie myReport v2.xlsx) or any other extension you may want/need. Be sure to leave a comment below if you have any questions about making this tweak to the VBA code. Please note that you will need the FileExist Function code (located above this section) pasted into your code module as well. If you need a walk-through on how to insert a macro into Microsoft Excel to use across any of your Excel Workbooks, you can check out an earlier post I wrote entitled. Sub SaveNewVersion_Excel() 'PURPOSE: Save file, if already exists add a new version indicator to filename 'SOURCE: www.TheSpreadsheetGuru.com/The-Code-Vault Dim FolderPath As String Dim myPath As String Dim SaveName As String Dim SaveExt As String Dim VersionExt As String Dim Saved As Boolean Dim x As Long TestStr = ' Saved = False x = 2 'Version Indicator (change to liking) VersionExt = '_v' 'Pull info about file On Error GoTo NotSavedYet myPath = ActiveWorkbook.FullName myFileName = Mid(myPath, InStrRev(myPath, ' ') + 1, InStrRev(myPath, '.' ) - InStrRev(myPath, ' ') - 1) FolderPath = Left(myPath, InStrRev(myPath, ' ')) SaveExt = '.' & Right(myPath, Len(myPath) - InStrRev(myPath, '.' File Name Too Long To DeleteSub SaveNewVersion_Word() 'PURPOSE: Save file, if already exists add a new version indicator to filename 'SOURCE: www.TheSpreadsheetGuru.com/The-Code-Vault Dim FolderPath As String Dim myPath As String Dim SaveName As String Dim SaveExt As String Dim VersionExt As String Dim Saved As Boolean Dim x As Long TestStr = ' Saved = False x = 2 'Version Indicator (change to liking) VersionExt = '_v' 'Pull info about file On Error GoTo NotSavedYet myPath = ActiveDocument.FullName myFileName = Mid(myPath, InStrRev(myPath, ' ') + 1, InStrRev(myPath, '.' ) - InStrRev(myPath, ' ') - 1) FolderPath = Left(myPath, InStrRev(myPath, ' ')) SaveExt = '.' Name Too Long To Delete& Right(myPath, Len(myPath) - InStrRev(myPath, '.' VBA Code of Version Control For PowerPoint Similar to the Microsoft Word version, the only thing you need to change from the first Excel VBA code is change ActiveWorkbook to ActivePresentation. Everything else is the same! Please note that you will need the FileExist Function code (located near the beginning of this article) pasted into your code module as well. Fundamentals of ecology by odum pdf converter. • You can only upload photos smaller than 5 MB. • You can only upload files of type PNG, JPG, or JPEG. • You can only upload files of type 3GP, 3GPP, MP4, MOV, AVI, MPG, MPEG, or RM. Knights of the zodiac english dub download.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |