excel导入string时间字段_流水账3 自动更新excel文档中的VBA模块

1、 之前写了一些公用模块（比如查询，配置，导表）。

图为一个对index+match函数的简单封装

2、 然后我肯定需要在多个工作簿中使用它们。为此我建立了一个公用的宏表模板（xltm）并且插入了我希望在每个新的工作簿中出现的模块。这样每次我在新建设计表的时候就可以让它们自带写好的模块。

3、 后来我对公用模块进行了多次功能迭代，比如增加了新的功能，改进了旧功能的容错性。问题出现了，我有太多的地方用到了他们，一一手动进行修改属实愚蠢！模板也解决不了问题。

手动导入文件和导出文件是微软默认的模块管理方式

4、 由此引出今天的问题。我希望建立一个公用模块库，以SVN进行版本控制。打开含有宏的工作簿时，自动查询SVN目录的revision版本号，以及本工作簿的版本号，并进行比较。如果本工作簿的版本号过低则对附属模块进行更新，否则不用进行任何操作。

5、 现在我们需要找到一个地方存储本工作簿的版本号。右键查看我们的工作簿，详细信息里面有很多字段是可资利用的，我的方案中选择了“修订号”。

下图中的作者也变成了一串数字是因为我在这个文档上进行了实验，验证excel在保存时会对哪些字段进行修改。

excel文档的详细信息

6、 使用VBA修改文档信息的方式如下。

thisVersion = ThisWorkbook.BuiltinDocumentProperties("Revision Number").Value

微软官方文档​docs.microsoft.com

这里列举一下其他字段的名称。

• Title
• Subject
• Author
• Keywords
• Comments
• Template
• Last Author
• Revision Number
• Application Name
• Last Print Date
• Creation Date
• Last Save Time
• Total Editing Time
• Number of Pages
• Number of Words
• Number of Characters
• Security
• Category
• Format
• Manager
• Company
• Number of Bytes
• Number of Lines
• Number of Paragraphs
• Number of Slides
• Number of Notes
• Number of Hidden Slides
• Number of Multimedia Clips
• Hyperlink Base
• Number of Characters (with spaces)

以上字段都是可读可写的，如果想要修改某一项可以这样

ThisWorkbook.BuiltinDocumentProperties("Revision Number").Value = 53659

7、 然后是设法从SVN目录下获取当前版本号。为此我们需要动用TortoiseSVN的COM接口，这一步需要安装好TortoiseSVN。

tortoisesvn的文档​tortoisesvn.net

然后是代码

    Dim oSvn As Object
    Set oSvn = CreateObject("SubWCRev.Object")
    oSvn.GetWCInfo VBApath, 1, 1
    rev = Str(oSvn.Revision)

第一行定义oSvn为一个对象。第二行以后期引用创建了一个SubWCRev对象，并把它用oSvn指代。第三行使用oSvn的GetWCInfo方法初始化了这个对象。这里VBApath是你存放VBA模块的路径，可能长这样：

E:Projecttrunk项目公用VBA模块库

怎么拿到它我们以后再说。第四行引用Revision成员，获取当前版本号，并且强制转换成string（其实我并不确定这一步是否必要，这里是懒得进一步验证了）并存放到rev变量中。

8、 下一步是，如果发现工作簿的版本号落后，设法更新模块。思路是首先清空所有代码模块，然后遍历模块库，依次进行导入。这里我们使用面向Stack Overflow编程，直接找到现成的做法。

https://stackoverflow.com/questions/704759/source-control-of-excel-vba-code-modules​stackoverflow.com

With ThisWorkbook.VBProject
    NumModules = ThisWorkbook.VBProject.VBComponents.Count
    y = 1
    While y <= NumModules
        If .VBComponents.Item(y).Type = 1 Then
            CompName = .VBComponents.Item(y).Name
            If VBA.Strings.InStr(CompName, "模块") > 0 Then
                OldModules = ThisWorkbook.VBProject.VBComponents.Count
                .VBComponents.Remove .VBComponents(CompName)
                NumModules = ThisWorkbook.VBProject.VBComponents.Count
                If OldModules - NumModules = 1 Then
                    y = 1
                Else
                    MsgBox ("Failed to remove " & CompName & " module from VBA project")
                End If
            End If
        End If
        y = y + 1
    Wend
End With

以上代码的思路是遍历 ThisWorkbook.VBProject.VBComponents 列表。需要注意的是在其中每个窗体、工作簿、模块、类模块都会被遍历到，因此要对.VBComponents.Item(y).Type进行判断。后面还有一层对模块名字的判断，进一步减少误伤可能。

只有最后三个模块会被移除

然后遍历模块文件夹，导入带有模块字样的bas文件

Dim fso As Object
Set fso = CreateObject("Scripting.FileSystemObject")
Set fdr = fso.getfolder(VBApath)
For Each file In fdr.Files
    If VBA.Strings.InStr(file.Name, "模块") > 0 And VBA.Strings.InStr(file.Name, "bas") Then
        ThisWorkbook.VBProject.VBComponents.Import file.Path
    End If
Next file
ThisWorkbook.BuiltinDocumentProperties("Revision Number").Value = Int(macroVersion)

具体可以参考这里

Import a module from a file using VBA in Microsoft Excel​www.exceltip.com

在上面的代码里面我们先是创建了一个Scripting.FileSystemObject，然后用它初始化了一个路径对象，遍历里面的文件判读是不是VBA模块，把符合条件的模块导入，最后将文档的修订号更新为模块库的SVN修订号。

上面所有的工作都不是太难，难点在于

1. 意识到问题存在
2. 判断问题可以被技术解决
3. 拥有使用技术解决问题的意愿和决心
4. 以适当的关键词检索有用的信息
5. 在遇到意料外的障碍时遇山开路遇水搭桥
6. 持续推进进度直到解决问题

技术能力和知识储备本身，反而没那么重要。