Saving Excel files, when working with VBA is unfortunately a bit of pain the ass. You always need to make a new version every now and then, because it does not matter how seasoned VBA developer you are, still there is a good chance that every now and then you will lose some 1-2 hours of work because of an Excel bug. You know it, I know it, the VBA team in Microsoft (if the team still exists) knows it and we all live with it. In this article I will provide a simple solution, that actually may save your day a bit, as far as you are a poor VBA developer, sentences to live without source control for the rest of your lives, working with saving the versions and naming them like abc.1.2.3.xlsb, abc.1.2.4 and making a source control out of it. Yet, you may still dump the VBA code to git (VBA – Source Control with Git – Video) and use some kind of partial source control, but this is not it and you probably realize it, if you have ever worked a bit with Python, C#, JS or any other non-VBA “fancy” language.
Her name is Pucci and she is always happy to see me 🙂
So, what the code does? Pretty much, if you run SaveThisM(), it will increment the last number of the file with 1 and it will save it as a new version. The old version will be moved here – Destination:=ThisWorkbook.path & "\Arch\Auto\" & oldName. If you run the SaveThis() option, it will do the same, without moving the old version to that folder. The idea is to use the immediate window and to use this command every 30 minutes or so.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 |
Public Sub SaveThis() 'saves foo.4.5.6.xlsb to foo.4.5.7.xlsb Dim mySplitter As Variant mySplitter = Split(ThisWorkbook.FullName, ".") Dim oldVersion As String oldVersion = mySplitter(UBound(mySplitter) - 1) Dim newVersion As String newVersion = oldVersion + 1 mySplitter(UBound(mySplitter) - 1) = newVersion Dim newName As String newName = Join(mySplitter, ".") ThisWorkbook.SaveAs newName Debug.Print "Saved as:" & vbCrLf & newName End Sub Public Sub SaveThisM() 'saves foo.4.5.6.xlsb to foo.4.5.7.xlsb 'and moves the old one to root\Arch\Auto Dim oldName As String oldName = ThisWorkbook.Name SaveThis Dim fso As New FileSystemObject fso.MoveFile Source:=ThisWorkbook.path & "\" & oldName, Destination:=ThisWorkbook.path & "\Arch\Auto\" & oldName Debug.Print "Moved to:" & vbCrLf & ThisWorkbook.path & "\Arch\Auto\" & oldName End Sub |
One day, in probably in 2030 someone will find a proper way to integrate Git, VBA and Excel. Or not…