Create Incremental Backups when using Power Query - Excelerator BI

Create Incremental Backups when using Power Query

Edit 16 May 2015: I now know the exact cause and cure for this problem.  See this post here This backup tip is still useful though.
This is a follow up to this post where I talked about Power Query and warned to take frequent backups when editing your Power Query Workbooks.  The issue is that Power Query is a bit flaky, and hence it is well advised to create incremental workbook backups with different file names in case bad things happen.

Anthony Newell kindly posted an idea (and some code) that he used to automate the process of taking incremental backups.  I took his code as inspiration, and then made quite some changes to meet my needs.  I am sharing this new code here for those that want to use it.  It is equally as useful for any workbook that you have invested time in, and you can’t afford to lose.  It doesn’t have to be a Power Query workbook.

What the Code Does

  • First it checks if there is a “version sheet” in the active workbook.  If not, it creates this version sheet.  The version sheet contains the current version number and the location of your backups.  You can manually change the default backup location in this sheet (but it always saves this as a sub folder of the original file location).
  • It then saves the active workbook with its current name in the current active folder (no version number added yet).  I have done it this way because I don’t want my active workbook file name changing all the time.  What I want is a backup that I can recover later if needed.
  • It then saves a copy of the active workbook in the archive folder with the current version number appended on the end.
  • It then closes the version with the version number added and reopens the original file so that you are working on the original file without the version number.
  • Finally it increments the version number on the version page.

backup tool

How to Use this Code

Simply copy the code from below into your personal.xlsb workbook.  That way it will always be available as a backup tool for what ever workbook you happen to be working on.  It is then easy to add it to your Quick Access Toolbar so it is always 1 click away (as you can see in the animated gif above).

  
 Sub SaveIncrementalFileVersion()
    Dim FileName As String
    Dim FileStub As String
    Dim FullArchiveFileName As String
    Dim Folder As String
    Dim Archive As String
    Dim FullFilename As String
    Dim VersionNo As Long
    Dim myFound As Boolean
    Dim Sheet As Object
    
    On Error GoTo ErrorHandler
    
    ' Check to see if the version sheet exists already
    For Each Sheet In Sheets
        If Sheet.Name = "Version" Then myFound = True
    Next Sheet
    
    If myFound = False Then  ' if the version sheet does not exist, then create it
        Sheets.Add
        ActiveSheet.Name = "Version"
        Range("A1") = "Version Number"
        Range("A2") = "Archive Sub Folder Name"
        Range("b1") = 1
        Range("b2") = "Archive"
        Range("C2") = "<= Manually Change Sub Folder as Needed"
        Columns("A:C").EntireColumn.AutoFit
    End If
    
    ActiveWorkbook.Save 'save current workbook
    
    'prepare variables
    VersionNo = Sheets("Version").Range("B1").Value
    FileName = ActiveWorkbook.Name
    FileStub = Left(FileName, Len(FileName) - 5)
    Folder = ActiveWorkbook.Path
    Archive = Sheets("version").Range("b2").Value
    FullFilename = Folder & "" & FileName
    FullArchiveFileName = Folder & "" & Archive & "" & FileStub & " " & VersionNo
    
    'check if archive folder exists and create it if it doesn't exist
    If Dir(Folder & "" & Archive, vbDirectory) = "" Then
        MkDir ActiveWorkbook.Path & "" & Archive
    End If
    
    ActiveWorkbook.SaveAs FullArchiveFileName 'save as an incremental version number
    ActiveWorkbook.Close savechanges:=False
     
    'reopen the orginal
    Workbooks.Open FileName:=FullFilename
    
    ' increment the version number
    VersionNo = Sheets("Version").Range("B1").Value + 1
    Sheets("Version").Range("B1").Value = VersionNo

    
ErrorExit:
    Exit Sub
    
ErrorHandler:
    Resume ErrorExit
End Sub

Edit 16 May 2015: I now know the exact cause and cure for this problem.  See this post here

 

If you want a comprehensive lesson on how to use Power Query, checkout my training course here https://exceleratorbi.com.au/power-query-online-training/

6 thoughts on “Create Incremental Backups when using Power Query”

  1. Matt, Anthony,

    Great Post, again. This will save me time! Just a quick “contribution” from my side. To make it work as I expected, I had to add a backslash in the places where you ampersanded a blank.

    For example “FullFilename = Folder & “” & FileName” in your code became “FullFilename = Folder & “\” & FileName”

    Don’t know if that is what you expected lesser Gods like me to do or if you had intended the code to work otherwise.

    So the full code that works for me is

    Dim FileName As String
    Dim FileStub As String
    Dim FullArchiveFileName As String
    Dim Folder As String
    Dim Archive As String
    Dim FullFilename As String
    Dim VersionNo As Long
    Dim myFound As Boolean
    Dim Sheet As Object

    On Error GoTo ErrorHandler

    ‘ Check to see if the version sheet exists already
    For Each Sheet In Sheets
    If Sheet.Name = “Version” Then myFound = True
    Next Sheet

    If myFound = False Then ‘ if the version sheet does not exist, then create it
    Sheets.Add
    ActiveSheet.Name = “Version”
    Range(“A1”) = “Version Number”
    Range(“A2”) = “Archive Sub Folder Name”
    Range(“b1”) = 1
    Range(“b2”) = “Archive”
    Range(“C2”) = “<= Manually Change Sub Folder as Needed"
    Columns("A:C").EntireColumn.AutoFit
    End If

    ActiveWorkbook.Save 'save current workbook

    'prepare variables
    VersionNo = Sheets("Version").Range("B1").Value
    FileName = ActiveWorkbook.Name
    FileStub = Left(FileName, Len(FileName) – 5)
    Folder = ActiveWorkbook.Path
    Archive = Sheets("version").Range("b2").Value
    FullFilename = Folder & "\" & FileName
    FullArchiveFileName = Folder & "\" & Archive & "\" & FileStub & " " & VersionNo

    'check if archive folder exists and create it if it doesn't exist
    If Dir(Folder & "\" & Archive, vbDirectory) = "" Then
    MkDir ActiveWorkbook.Path & "\" & Archive
    End If

    ActiveWorkbook.SaveAs FullArchiveFileName 'save as an incremental version number
    ActiveWorkbook.Close savechanges:=False

    'reopen the orginal
    Workbooks.Open FileName:=FullFilename

    ' increment the version number
    VersionNo = Sheets("Version").Range("B1").Value + 1
    Sheets("Version").Range("B1").Value = VersionNo

    ErrorExit:
    Exit Sub

    ErrorHandler:
    Resume ErrorExit
    End Sub

  2. Khushnood Viccaji

    Hello Matt, Anthony, just a quick check on whether either of you have been able to complete the final version of the code.
    I’d be interested in a copy for my use 🙂

  3. I’ve been tinkering with it today and will mail you across a final version of code when I’ve finished

    It will maintain a history of version numbers, date stamps and ‘milestone descriptions’ if they have been provided by user

    I want to complete it as I’ve finally got round to producing an Excel shell tool that contains everything I need before I start a development, Central error handler etc etc. This version control feature will also become a central element

    Back to the Scotch, all the best!

  4. This is a great evolution and I’m going take a copy!
    You could also finesse this further by prompting user to input brief message describing what change has been made to the last version and store this value on you version worksheet. Restrict to 30 characters say
    That way, as well as actual version number history you have a brief record of what has changed
    That will also help you when you do housekeeping and decide to delete prior versions you don’t need anymore

    1. I swear I woke up in e middle of the night with exactly the same idea. Also could add a date time stamp column, and possibly an option to keep x versions befor deleting the oldest. I will probably improve it over time and may post an improved version sometime.

Leave a Comment

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

Scroll to Top