MS Excel版本控制的最佳方式 [英] Best way to do Version Control for MS Excel

问题描述

您使用什么版本控制系统与MS Excel（2003/2007）？你会推荐什么和为什么？您的最高评级版本控制系统有什么限制？

为了将其视为透视图，以下是几个用例：

1. VBA模块的版本控制


2. 多个人正在使用Excel电子表格，他们可能会对相同的工作表进行更改，他们想要合并和整合。该工作表可能有公式，数据，图表等。


3. 用户不太技术性，使用的版本控制系统越少越好


4. 空间约束是一个考虑。理想情况下，只保存增量更改，而不是整个Excel电子表格。

解决方案

我刚刚设置了一个使用Bazaar的电子表格，手动检入/ TortiseBZR。鉴于该主题帮助我保存部分，我想在这里发布我的解决方案。

我的解决方案是创建一个导出所有模块的电子表格保存，并在打开的时候删除并重新导入模块。是的，这可能是转换现有电子表格的潜在危险。

这允许我通过 Emacs 来编辑模块中的宏（是，emacs）或本机，并在主要更改后提交我的BZR存储库。因为所有的模块都是文本文件，BZR中的标准差异样式命令对于我的源文件来说除了Excel文件本身。

我已经为我的BZR设置了一个目录存储库，X：\Data\MySheet。在repo中是我的每个模块的MySheet.xls和一个.vba文件（即：Module1Macros）。在我的电子表格中，我添加了一个免除导出/导入循环名为VersionControl的模块。要导出和重新导入的每个模块必须以宏结尾。

VersionControl模块的内容

  Sub SaveCodeModules（）
 
'此代码导出所有VBA模块
 Dim i％，sName $ 
 
 With ThisWorkbook.VBProject 
对于i％= 1 To .VBComponents.Count 
如果.VBComponents（i％）。CodeModule.CountOfLines> 0然后
 sName $ = .VBComponents（i％）。CodeModule.Name 
 .VBComponents（i％）。导出X：\Tools\MyExcelMacros\ sName $& .vba
 End If 
 Next i 
 End with 
 
 End Sub 
 
 Sub ImportCodeModules（）
 
 With ThisWorkbook.VBProject 
对于i％= 1 To .VBComponents.Count 
 
 ModuleName = .VBComponents（i％）。CodeModule.Name 
 
如果ModuleName <> VersionControl然后
如果右（ModuleName，6）=Macros然后
 .VBComponents.Remove .VBComponents（ModuleName）
 .VBComponents.ImportX：\Data\MySheet \& ModuleName& .vba
 End If 
 End If 
 Next if 
 Next i 
 End with 
 
 End Sub 
  / pre> 
 
 
接下来，我们必须设置用于打开/保存的事件钩子来运行这些宏。在代码查看器中，右键单击ThisWorkbook并选择查看代码。您可能需要将代码窗口顶部的选择框从（常规）视图更改为工作簿视图。
 
 
 
 内容的工作簿视图： 
  Private Sub Workbook_Open（）
 
 ImportCodeModules 
 
 End Sub 
 
 Private Sub Workbook_BeforeSave（ByVal SaveAsUI As Boolean，Cancel As Boolean）
 
 SaveCodeModules 
 
 End Sub 
  
我将在接下来的几周内解决这个工作流程，而我如果我有任何问题，将发布。
 
 
 
感谢您共享VBComponent代码！
 
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.   

解决方案
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 > 0 Then
            sName$ = .VBComponents(i%).CodeModule.Name
            .VBComponents(i%).Export "X:\Tools\MyExcelMacros\" & sName$ & ".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 <> "VersionControl" Then
            If Right(ModuleName, 6) = "Macros" Then
                .VBComponents.Remove .VBComponents(ModuleName)
                .VBComponents.Import "X:\Data\MySheet\" & ModuleName & ".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!

                        
这篇关于MS Excel版本控制的最佳方式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！