最佳的MS Excel版本控制方法是什么？

Question

最佳的MS Excel版本控制方法是什么？

excelversion-control

180

你使用过哪些版本控制系统来管理MS Excel（2003/2007）？你会推荐哪些系统？为什么？你认为你所使用的最佳版本控制系统有哪些局限性？

以下是几种应用场景：

对VBA模块进行版本控制
多人共同编辑Excel表格，可能同时更改同一工作表，需要合并和整合这些修改。该表格可能包含公式、数据、图表等元素。
用户不太懂技术，尽量少用版本控制系统。
空间限制要考虑。理想情况下，只保存增量更改，而不是整个Excel电子表格。

- TheObserver

17

谷歌应用/文档没有微软Excel的全部功能，如果您需要进行更高级的建模等工作，则需要Excel。 - TheObserver

26

如果这是一个实际可行的选择（即使用matlab/python），那么所有金融公司现在都会进行转换。要求分析金融模型但不是程序员的人成为程序员总体上是存在危险且相对不切实际的。@Richie Cotton - Anonymous Type

1

请参见此处的类似问题：https://dev59.com/C3RB5IYBdhLWcg3weXSX。但它并没有涉及到工作表本身，只是VBA代码。 - Vijay

8

责怪Excel建模是导致信贷危机的人很可能是故意将垃圾商品当作AAA出售的骗子。你不需要电子表格就能知道一个投资品是垃圾。作为金融从业者，我可以告诉你，完全依赖任何模型都是一种输钱的绝对途径。此外，任何模型的优劣取决于构建它们的人。如果你雇用普通人来完成像爱因斯坦那样的工作，你会遭遇糟糕的时光。 - Eric J

如果您主要只对VBA宏进行版本控制感兴趣，请参阅我的答案：https://dev59.com/Y3A75IYBdhLWcg3w_ef1#38297505 - Chel

如果您只对VBA VCS部分感兴趣，那么Chel链接的帖子是相关的，另外这个关于通过Git管理VBA的帖子也很有用：https://dev59.com/71oV5IYBdhLWcg3wnf3r#53160775 - Colm Bhandal

22个回答

45

TortoiseSVN是一款用于Subversion版本控制系统的惊人好用的Windows客户端。我刚刚发现它有一个功能，当您单击获取Excel文件版本之间的差异时，它将在Excel中打开两个版本，并突出显示（以红色）更改的单元格。这是通过vbs脚本的魔法来完成的，可以在这里了解详情。

即使不使用TortoiseSVN，您可能会发现此功能很有用。

- mcherm

3

很惊奇地发现TortoiseSVN可以作为内置功能进行比较。 - Nam G VU

3

这和Word文件是一样的东西吗？ - Nam G VU

4

我刚刚测试过了 - 这也适用于Word文件。很酷 ^^ - Nam G VU

1

它似乎不适用于VB代码。有什么解决办法吗？ - manpreet

12

让我总结一下您想进行版本控制的内容及原因：

什么内容：
- 代码（VBA）
- 电子表格（公式）
- 电子表格（数值）
- 图表
- ...
为什么：
- 审计日志
- 协作
- 版本比较（"diffing"）
- 合并

正如其他人在这里发布的，现有版本控制系统上还有几个解决方案，例如：

Git
Mercurial
Subversion
Bazaar

如果您唯一关心的是工作簿中的VBA代码，则Demosthenex提出的方法或VbaGit (https://github.com/brucemcpherson/VbaGit) 非常适用且相对简单易实施。优势是您可以依赖经过充分验证的版本控制系统，并根据您的需求选择一个（请参阅https://help.github.com/articles/what-are-the-differences-between-svn-and-git/进行Git和Subversion之间的简要比较）。

如果你不仅担心代码，还关注工作表中的数据（硬编码值和公式结果），那么可以使用类似的策略：将工作表的内容序列化为某种文本格式（通过Range.Value），并使用现有的版本控制系统。这里有一篇非常好的博客文章：https://wiki.ucl.ac.uk/display/~ucftpw2/2013/10/18/Using+git+for+version+control+of+spreadsheet+models+-+part+1+of+3。

然而，电子表格比较是一个非平凡的算法问题。有一些工具可用，例如Microsoft的电子表格比较 (https://support.office.com/en-us/article/Overview-of-Spreadsheet-Compare-13fafa61-62aa-451b-8674-242ce5f2c986)、Exceldiff (http://exceldiff.arstdesign.com/) 和 DiffEngineX (https://www.florencesoft.com/compare-excel-workbooks-differences.html)。但要将这些比较与Git等版本控制系统集成起来，是另一个挑战。

最后，你必须确定适合你需求的工作流程。对于简单而专业的Excel Git工作流程，请查看https://www.xltrail.com/blog/git-workflow-for-excel。

- Bjoern Stiel

1

2020年更新：感谢您提供https://www.xltrail.com/git-xl的建议，这是一个开源的git扩展程序，可以在提交之间生成vba源代码差异。虽然有限，但它可以避免出错的导入和导出。 - chingNotCHing

9

这取决于你是在谈论数据还是电子表格中包含的代码。虽然我非常不喜欢微软的Visual Sourcesafe，通常不建议使用它，但它可以轻松集成Access和Excel，并提供模块的源代码控制。

[实际上，与Access的集成包括查询、报告和模块作为可版本化的单独对象]

MSDN链接在这里。

- Mitch Wheat

4

更好的保密之一——我不知道VSS可以做到那个。+1 - ConcernedOfTunbridgeWells

我也不知道。但无论如何，VSS是一堆垃圾，我会远离它。 - GUI Junkie

我非常兴奋，花了一个小时在网上搜寻，但似乎微软在Excel 2003中停止支持它。如果你正在使用Access VBA，你可能会有好运，但我没有去看。 - harvest316

1

你可能能够使用Office开发者版插件吗？：http://brandon.fuller.name/archives/2003/11/07/10.26.30/ - Mitch Wheat

7

我不知道是否有一个很好地完成这个任务的工具，但我看到过各种自制解决方案。这些方案的共同特点是尽量减少版本控制下的二进制数据，最大化文本数据以利用传统 scc 系统的优势。为此，您需要：

将工作簿视为任何其他应用程序。分离逻辑、配置和数据。
将代码与工作簿分开。
通过编程方式构建 UI。
编写构建脚本以重建工作簿。

- Hobbo

为什么要经历所有这些麻烦，当你只需要一个处理二进制对象的源代码控制呢？SVN可以做到。 - Unknown Coder

18

因为你无法合并二进制对象。 - igelineau

6

我使用 git，今天我将这个（git-xlsx-textconv）移植到 Python，因为我的项目基于 Python 代码，并且与 Excel 文件交互。这适用于至少.xlsx文件，但我认为它也适用于.xls文件。这里是 GitHub 链接。我编写了两个版本，一个每行一个单元格，另一个每个单元格都在一行上（后者是因为 git diff 默认不喜欢换行，至少在 Windows 上是这样的）。

这是我的.gitconfig文件（这允许差异脚本驻留在我的项目存储库中）：

[diff "xlsx"]
    binary = true
    textconv = python `git rev-parse --show-toplevel`/src/util/git-xlsx-textconv.py

如果您希望脚本可在许多不同的仓库中使用，请使用以下代码：

[diff "xlsx"]
    binary = true
    textconv = python C:/Python27/Scripts/git-xlsx-textconv.py

我的.gitattributes文件：

*.xlsx diff=xlsx

- nmz787

6

在@Demosthenex的工作基础上，受到@Tmdean和@Jon Crowell宝贵的评论！（点赞）

我将模块文件保存在工作簿位置旁边的git\目录中。请根据您的喜好进行更改。

这不会跟踪工作簿代码的更改。因此，由您自行进行同步。

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 <> vbext_ct_Document Then
            If .VBComponents(i).CodeModule.CountOfLines > 0 Then
                name = .VBComponents(i).CodeModule.name
                .VBComponents(i).Export Application.ThisWorkbook.Path & _
                                            "\git\" & name & ".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 <> "VersionControl" Then
            If .VBComponents(i).Type <> vbext_ct_Document Then
                .VBComponents.Remove .VBComponents(ModuleName)
                .VBComponents.Import Application.ThisWorkbook.Path & _
                                         "\git\" & ModuleName & ".vba"
            End If
        End If
    Next i
End With

End Sub

然后在工作簿模块中：

Private Sub Workbook_Open()

    ImportCodeModules

End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

    SaveCodeModules

End Sub

- przemo_li

6

接着@Demosthenex的回答，如果您还想跟踪Microsoft Excel对象和用户窗体中的代码，您需要进行一些小技巧。

首先，我修改了我的SaveCodeModules()函数，以便考虑到我计划导出的不同类型的代码：

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 > 0 Then
            moduleName = .VBComponents(i).CodeModule.Name
            vbaType = .VBComponents(i).Type

            If vbaType = 1 Then
                .VBComponents(i).Export dir & moduleName & ".vba"
            ElseIf vbaType = 3 Then
                .VBComponents(i).Export dir & moduleName & ".frm"
            ElseIf vbaType = 100 Then
                .VBComponents(i).Export dir & moduleName & ".cls"
            End If

        End If
    Next i
End With

End Sub

用户表单可以像VBA代码一样进行导入和导出。唯一的区别是，当一个表单被导出时，将会创建两个文件（每个用户表单都会得到一个.frm和一个.frx文件）。其中一个文件保存了你所编写的软件，另一个是一个二进制文件，它（我非常确定）定义了表单的布局。

Microsoft Excel对象（MEOs）（例如Sheet1、Sheet2、ThisWorkbook等）可以导出为.cls文件。然而，如果你想要将这段代码重新导入工作簿中，如果该工作簿中已经存在相同的工作表，那么尝试像导入VBA模块一样导入它时，会出现错误。

为了解决这个问题，我决定不尝试将.cls文件导入Excel，而是将.cls文件读取为字符串，然后将此字符串粘贴到空白的MEO中。这就是我的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 <> "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 <> "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 & 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 > 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 & moduleString & 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

如果你对这两个函数中dir输入参数感到困惑，它只是你的代码存储库！因此，你可以这样调用这些函数：

SaveCodeModules "C:\...\YourDirectory\Project\source\"
ImportCodeModules "C:\...\YourDirectory\Project\source\"

- dslosky

一个快速的提示：由于二进制文件，我在UserForms上没有进行真正的版本控制。如果您在git repo中创建多个分支，则可能无法合并它们，如果您正在使用UserForms。 - dslosky

3

我想推荐一个名为Rubberduck的优秀开源工具，它内置了VBA代码版本控制。试试吧！

- Yuxiang Wang

3

你可以在工作簿中加入以下代码片段来实现这个功能：

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 "C:\Code\" & sName$ & ".vba"
            End If
        Next i
    End With
End Sub

我在互联网上找到了这段代码。

之后，你可以使用Subversion来维护版本控制。例如，通过在VBA中使用“shell”命令与Subversion的命令行接口。这样就可以做到了。我甚至考虑自己也这样做 :)

- GUI Junkie

我认为这是一个很好的方法。只有通过分解才能对Excel代码进行源代码控制。这将暗示一种构建方法。例如，Code Cleaner可以做到这一点（我相信是免费软件）http://www.appspro.com/Utilities/CodeCleaner.htm，但同样你的代码也是等效的。 - polyglot

我尝试修改这个程序，使其能够在Microsoft® Excel® for Mac 2011 Version 14.4.1上运行。调用Export方法的那一行代码却毫无反应（无论我使用多么规范的OS X目录）。 - D A Vincent

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Demosthenex · Accepted Answer

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

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

这使我能够通过Emacs（是的，emacs）或Excel本身进行宏模块的编辑，并在进行重大更改后提交我的BZR仓库。由于所有模块都是文本文件，因此BZR中的标准差异样式命令适用于我的源代码，除了Excel文件本身。

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

"VersionControl"模块的内容：

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

接下来，我们需要为打开/保存设置事件钩子来运行这些宏。在代码查看器中，右键单击"ThisWorkbook"并选择"查看代码"。您可能需要拉下代码窗口顶部的选择框，从"(通用)"视图更改为"工作簿"视图。

"工作簿"视图的内容：

Private Sub Workbook_Open()

ImportCodeModules

End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

SaveCodeModules

End Sub

在接下来的几周里，我将逐渐适应这个工作流程，如果遇到任何问题，我会发帖寻求帮助。

谢谢分享 VBComponent 代码！