Best way to do Version Control for MS Excel

Question

What version control systems have you used with MS Excel  2003 2007   What would you recommend and Why  What limitations have you found with your top rated version control system   To put this in perspective  here are a couple of use cases    version control for VBA modules  more than one person is working on a Excel spreadsheet and they may be making changes to the same worksheet  which they want to merge and integrate  This worksheet may have formulae  data  charts etc the users are not too technical and the fewer version control systems used the better Space constraint is a consideration  Ideally only incremental changes are saved rather than the entire Excel spreadsheet

User · Answer

Use any of the standard version control tools like SVN or CVS  Limitations would depend on whats the objective  Apart from a small increase in size of the repository  i did nt face any issues

User · Answer

in response to mattlant s reply - sharepoint will work well as a version control only if the version control feature is turned on in the document library  in addition be aware that any code that calls other files by relative paths wont work  and finally any links to external files will break when a file is saved in sharepoint

User · Answer

If you are looking at an office setting with regular office non technical users than Sharepoint is a viable alternative  You can setup document folders with version control enabled and checkins and checkouts  Makes it freindlier for regular office users

User · Answer

Actually there only a handful of solutions to track and compare changes in macro code - most of those were named here already  I have been browsing the web and came across this new tool worth mentioning    XLTools Version Control for VBA macros   version control for Excel sheets and VBA modules preview and diff changes before committing a version great for collaborative work of several users on the same file  track who changed what when comments  compare versions and highlight changes in code line-by-line suitable for users who are not tech-savvy  or Excel-savvy for that matter version history is stored in Git-repository on your own PC - any version can be easily recovered    VBA code versions side by side  changes are visualized

User · Answer

I would like to recommend a great open-source tool called Rubberduck that has version control of VBA code built in  Try it

User · Answer

There is also a program called Beyond Compare that has a quite nice Excel file compare  I found a screenshot in chinese that briefly shows this    Original image source  There is a 30 day trial on their page

User · Answer

It depends whether you are talking about data  or the code contained within a spreadsheet  While I have a strong dislike of Microsoft s Visual Sourcesafe and normally would not recommended it  it does integrate easily with both Access and Excel  and provides source control of modules    In fact the integration with Access  includes queries  reports and modules as individual objects that can be versioned   The MSDN link is here

User · Answer

I found a very simple solution to this question which meets my needs  I add one line to the bottom of all of my macros which exports a   txt file with the entire macro code each time it is run  The code   ActiveWorkbook VBProject VBComponents  moduleName   Export C  Path To Spreadsheet moduleName txt     Found on Tom s Tutorials  which also covers some setup you may need to get this working    Since I ll always run the macro whenever I m working on the code  I m guaranteed that git will pick up the changes  The only annoying part is that if I need to checkout an earlier version  I have to manually copy paste from the   txt into the spreadsheet

User · Answer

I ve just setup a spreadsheet that uses Bazaar  with manual checkin out via TortiseBZR  Given that the topic helped me with the save portion  I wanted to post my solution here   The solution for me was to create a spreadsheet that exports all modules on save  and removes and re-imports the modules on open  Yes  this could be potentially dangerous for converting existing spreadsheets   This allows me to edit the macros in the modules via Emacs  yes  emacs  or natively in Excel  and commit my BZR repository after major changes  Because all the modules are text files  the standard diff-style commands in BZR work for my sources except the Excel file itself   I ve setup a directory for my BZR repository  X  Data MySheet  In the repo are MySheet xls and one  vba file for each of my modules  ie  Module1Macros   In my spreadsheet I ve added one module that is exempt from the export import cycle called  VersionControl   Each module to be exported and re-imported must end in  Macros    Contents of the  VersionControl  module   Sub SaveCodeModules     This code Exports all VBA modules Dim i   sName   With ThisWorkbook VBProject     For i    1 To  VBComponents Count         If  VBComponents i   CodeModule CountOfLines  gt  0 Then             sName     VBComponents i   CodeModule Name              VBComponents i   Export  X  Tools MyExcelMacros    amp  sName   amp    vba          End If     Next i End With  End Sub  Sub ImportCodeModules    With ThisWorkbook VBProject     For i    1 To  VBComponents Count          ModuleName    VBComponents i   CodeModule Name          If ModuleName  lt  gt   VersionControl  Then             If Right ModuleName  6     Macros  Then                  VBComponents Remove  VBComponents ModuleName                   VBComponents Import  X  Data MySheet    amp  ModuleName  amp    vba             End If         End If     Next i End With  End Sub   Next  we have to setup event hooks for open   save to run these macros  In the code viewer  right click on  ThisWorkbook  and select  View Code   You may have to pull down the select box at the top of the code window to change from   General   view to  Workbook  view   Contents of  Workbook  view   Private Sub Workbook Open    ImportCodeModules  End Sub  Private Sub Workbook BeforeSave ByVal SaveAsUI As Boolean  Cancel As Boolean   SaveCodeModules  End Sub   I ll be settling into this workflow over the next few weeks  and I ll post if I have any problems   Thanks for sharing the VBComponent code

User · Answer

I have been looking into this too   It apears that the latest Team Foundation Server 2010 may have an Excel Add-In   Here is a clue   http   team-foundation-server blogspot com 2009 07 tf84037-there-was-problem-initializing html

User · Answer

Taking  Demosthenex  s answer a step further  if you d like to also keep track of the code in your Microsoft Excel Objects and UserForms you have to get a little bit tricky   First I altered my SaveCodeModules   function to account for the different types of code I plan to export     Sub SaveCodeModules dir As String    This code Exports all VBA modules Dim moduleName As String Dim vbaType As Integer  With ThisWorkbook VBProject     For i   1 To  VBComponents count         If  VBComponents i  CodeModule CountOfLines  gt  0 Then             moduleName    VBComponents i  CodeModule Name             vbaType    VBComponents i  Type              If vbaType   1 Then                  VBComponents i  Export dir  amp  moduleName  amp    vba              ElseIf vbaType   3 Then                  VBComponents i  Export dir  amp  moduleName  amp    frm              ElseIf vbaType   100 Then                  VBComponents i  Export dir  amp  moduleName  amp    cls              End If          End If     Next i End With  End Sub   The UserForms can be exported and imported just like VBA code  The only difference is that two files will be created when a form is exported  you ll get a  frm and a  frx file for each UserForm   One of these holds the software you ve written and the other is a binary file which  I m pretty sure  defines the layout of the form   Microsoft Excel Objects  MEOs   meaning Sheet1  Sheet2  ThisWorkbook etc  can be exported as a  cls file  However  when you want to get this code back into your workbook  if you attempt to import it the same way you would a VBA module  you ll get an error if that sheet already exists in the workbook    To get around this issue  I decided not to try to import the  cls file into Excel  but to read the  cls file into excel as a string instead  then paste this string into the empty MEO  Here is my ImportCodeModules   Sub ImportCodeModules dir As String   Dim modList 0 To 0  As String Dim vbaType As Integer    delete all forms  modules  and code in MEOs With ThisWorkbook VBProject     For Each comp In  VBComponents          moduleName   comp CodeModule Name          vbaType    VBComponents moduleName  Type          If moduleName  lt  gt   DevTools  Then             If vbaType   1 Or                   vbaType   3 Then                   VBComponents Remove  VBComponents moduleName               ElseIf vbaType   100 Then                    we can t simply delete these objects  so instead we empty them                  VBComponents moduleName  CodeModule DeleteLines 1   VBComponents moduleName  CodeModule CountOfLines              End If         End If     Next comp End With    make a list of files in the target directory Set FSO   CreateObject  Scripting FileSystemObject   Set dirContents   FSO getfolder dir    figure out what is in the directory we re importing    import modules  forms  and MEO code back into workbook With ThisWorkbook VBProject     For Each moduleName In dirContents Files            I don t want to import the module this script is in         If moduleName Name  lt  gt   DevTools vba  Then                if the current code is a module or form             If Right moduleName Name  4      vba  Or                   Right moduleName Name  4      frm  Then                    just import it normally                  VBComponents Import dir  amp  moduleName Name                if the current code is a microsoft excel object             ElseIf Right moduleName Name  4      cls  Then                 Dim count As Integer                 Dim fullmoduleString As String                 Open moduleName Path For Input As  1                  count   0                count which line we re on                 fullmoduleString         build the string we want to put into the MEO                 Do Until EOF 1           loop through all the lines in the file                      Line Input  1  moduleString    the current line is moduleString                     If count  gt  8 Then              skip the junk at the top of the file                            append the current line  to the string we ll insert into the MEO                         fullmoduleString   fullmoduleString  amp  moduleString  amp  vbNewLine                      End If                     count   count   1                 Loop                    insert the lines into the MEO                  VBComponents Replace moduleName Name    cls        CodeModule InsertLines  VBComponents Replace moduleName Name    cls        CodeModule CountOfLines   1  fullmoduleString                  Close  1              End If         End If      Next moduleName End With  End Sub   In case you re confused by the dir input to both of these functions  that is just your code repository  So  you d call these functions like   SaveCodeModules  C      YourDirectory Project source   ImportCodeModules  C      YourDirectory Project source

User · Answer

I wrote a revision controlled spreadsheet using VBA  It is geared more for engineering reports where you have multiple people working on a Bill Of Material or Schedule and then at some point in time you want to create a snapshot revision that shows adds  del and updates from the previous rev   Note  it is a macro enabled workbook that you need to sign in to download from my site  you can use OpenID   All the code is unlocked     Rev Controlled Spreadsheet

User · Answer

It depends on what level of integration you want  I ve used Subversion TortoiseSVN which seems fine for simple usage  I have also added in keywords but there seems to be a risk of file corruption  There s an option in Subversion to make the keyword substitutions fixed length and as far as I understand it will work if the fixed length is even but not odd  In any case you don t get any useful sort of diff functionality  I think there are commercial products that will do  diff   I did find something that did diff based on converting stuff to plain text and comparing that  but it wasn t very nice

User · Answer

Let me summarise what you would like to version control and why    What    Code  VBA  Spreadsheets  Formulae  Spreadsheets  Values  Charts      Why     Audit log Collaboration Version comparison   diffing   Merging    As others have posted here  there are a couple of solutions on top of existing version control systems such as    Git Mercurial Subversion Bazaar   If your only concern is the VBA code in your workbooks  then the approach Demosthenex above proposes or VbaGit  https   github com brucemcpherson VbaGit  work very well working and are relatively simple to implement  The advantages are that you can rely on well proven version control systems and chose one according to your needs  have a look at https   help github com articles what-are-the-differences-between-svn-and-git  for a brief comparison between Git and Subversion    If you not only worry about code but also about the data in your sheets   hardcoded  values and formula results   you can use a similar strategy for that  Serialise the contents of your sheets into some text format  via Range Value  and use an existing version control system  Here s a very good blog post about this  https   wiki ucl ac uk display  ucftpw2 2013 10 18 Using git for version control of spreadsheet models - part 1 of 3  However  spreadsheet comparison is a non-trivial algorithmic problem  There are a few tools around  such as Microsoft s Spreadsheet Compare  https   support office com en-us article Overview-of-Spreadsheet-Compare-13fafa61-62aa-451b-8674-242ce5f2c986   Exceldiff  http   exceldiff arstdesign com   and DiffEngineX  https   www florencesoft com compare-excel-workbooks-differences html   But it s another challenge to integrate these comparison with a version control system like Git   Finally  you have to settle on a workflow that suits your needs  For a simple  tailored Git for Excel workflow  have a look at https   www xltrail com blog git-workflow-for-excel

User · Answer

One thing you could do is to have the following snippet in your Workbook   Sub SaveCodeModules     This code Exports all VBA modules Dim i   sName       With ThisWorkbook VBProject         For i    1 To  VBComponents Count             If  VBComponents i   CodeModule CountOfLines  gt  0 Then                 sName     VBComponents i   CodeModule Name                  VBComponents i   Export  C  Code    amp  sName   amp    vba              End If         Next i     End With End Sub   I found this snippet on the Internet   Afterwards  you could use Subversion to maintain version control  For example by using the command line interface of Subversion with the  shell  command within VBA  That would do it  I m even thinking of doing this myself

User · Answer

I use git  and today I ported this  git-xlsx-textconv  to Python  since my project is based on Python code  and it interacts with Excel files  This works for at least  xlsx files  but I think it will work for  xls too  Here s the github link  I wrote two versions  one with each row on its own line  and another where each cell is on its own line  the latter was written because git diff doesn t like to wrap long lines by default  at least here on Windows    This is my  gitconfig file  this allows the differ script to reside in my project s repo     diff  xlsx       binary   true     textconv   python  git rev-parse --show-toplevel  src util git-xlsx-textconv py   if you want the script to be available for many different repos  then use something like this    diff  xlsx       binary   true     textconv   python C  Python27 Scripts git-xlsx-textconv py   my  gitattributes file     xlsx diff xlsx

User · Answer

You might have tried using Microsoft s Excel XML in zip container   xlsx and  xslm   for version control and found the vba was stored in vbaProject bin  which is useless for version control    The solution is simple     Open the excel file with LibreOffice Calc  In LibreOffice Calc    File  Save as  Save as type  ODF Spreadsheet   ods      Close LibreOffice Calc  rename the new file s file extension from  ods to  zip  create a folder for the spreadsheet in a GIT maintained area  extract the zip into it s GIT folder  commit to GIT    When you repeat this with the next version of the spreadsheet you ll have to make sure you make the folder s files exactly match those in the zip container  and don t leave any deleted files behind

User · Answer

I m not aware of a tool that does this well but I ve seen a variety of homegrown solutions  The common thread of these is to minimise the binary data under version control and maximise textual data to leverage the power of conventional scc systems  To do this    Treat the workbook like any other application  Seperate logic  config and data  Separate code from the workbook  Build the UI programmatically   Write a build script to reconstruct the workbook

User · Answer

After searching for ages and trying out many different tools  I ve found my answer to the vba version control problem here  https   stackoverflow com a 25984759 2780179  It s a simple excel addin for which the code can be found here  There are no duplicate modules after importing  It exports your code automatically  as soon as you save your workbook  without modifying any existing workbooks  It comes together with a vba code formatter

User · Answer

TortoiseSVN is an astonishingly good Windows client for the Subversion version control system  One feature which I just discovered that it has is that when you click to get a diff between versions of an Excel file  it will open both versions in Excel and highlight  in red  the cells that were changed  This is done through the magic of a vbs script  described here   You may find this useful even if NOT using TortoiseSVN

User · Answer

Working upon  Demosthenex work   Tmdean and  Jon Crowell invaluable comments    1 them   I save module files in git  dir beside workbook location  Change that to your liking   This will NOT track changes to Workbook code  So it s up to you to synchronize them     Sub SaveCodeModules     This code Exports all VBA modules Dim i As Integer  name As String  With ThisWorkbook VBProject     For i    VBComponents count To 1 Step -1         If  VBComponents i  Type  lt  gt  vbext ct Document Then             If  VBComponents i  CodeModule CountOfLines  gt  0 Then                 name    VBComponents i  CodeModule name                  VBComponents i  Export Application ThisWorkbook Path  amp                                                  git    amp  name  amp    vba              End If         End If     Next i End With  End Sub  Sub ImportCodeModules   Dim i As Integer Dim ModuleName As String  With ThisWorkbook VBProject     For i    VBComponents count To 1 Step -1          ModuleName    VBComponents i  CodeModule name          If ModuleName  lt  gt   VersionControl  Then             If  VBComponents i  Type  lt  gt  vbext ct Document Then                  VBComponents Remove  VBComponents ModuleName                   VBComponents Import Application ThisWorkbook Path  amp                                               git    amp  ModuleName  amp    vba              End If         End If     Next i End With  End Sub   And then in Workbook module   Private Sub Workbook Open        ImportCodeModules  End Sub  Private Sub Workbook BeforeSave ByVal SaveAsUI As Boolean  Cancel As Boolean       SaveCodeModules  End Sub

User · Answer

It should work with most VCS  depending on other criteria you might choose SVN  CVS  Darcs  TFS  etc   however it will actually the complete file  because it is a binary format   meaning that the  what changed  question is not so easy to answer   You can still rely on log messages if people complete them  but you might also try the new XML based formats from Office 2007 to gain some more visibility  although it would still be hard to weed through the tons of XML  plus AFAIK the XML file is zipped on the disk  so you would need a pre-commit hook to unzip it for text diff to work correctly

[excel] Best way to do Version Control for MS Excel

Examples related to excel

Examples related to version-control