从多个 Excel 文档导出 VBA 代码以放入版本控制
声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow
原文地址: http://stackoverflow.com/questions/608872/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Exporting VBA code from Multiple Excel documents to put into version control
提问by Craig T
Does anyone know a way to export the VBA code from a number of Excel documents, so that the code can be added into a subversion repository? Without having to manually open each document and export the code.
有谁知道从多个 Excel 文档中导出 VBA 代码的方法,以便可以将代码添加到 subversion 存储库中?无需手动打开每个文档并导出代码。
采纳答案by Adam Davis
You'll find a tool for this here:
你会在这里找到一个工具:
http://www.pretentiousname.com/excel_extractvba/index.html
http://www.pretentiousname.com/excel_extractvba/index.html
It's a VBS script that automates excel. You can modify it according to your needs - note that it isn't perfect (read the webpage for caveats).
这是一个自动化excel的VBS脚本。您可以根据自己的需要修改它 - 请注意它并不完美(请阅读网页以了解注意事项)。
option explicit
Const vbext_ct_ClassModule = 2
Const vbext_ct_Document = 100
Const vbext_ct_MSForm = 3
Const vbext_ct_StdModule = 1
Main
Sub Main
Dim xl
Dim fs
Dim WBook
Dim VBComp
Dim Sfx
Dim ExportFolder
If Wscript.Arguments.Count <> 1 Then
MsgBox "As the only argument, give the FULL path to an XLS file to extract all the VBA from it."
Else
Set xl = CreateObject("Excel.Application")
Set fs = CreateObject("Scripting.FileSystemObject")
xl.Visible = true
Set WBook = xl.Workbooks.Open(Trim(wScript.Arguments(0)))
ExportFolder = WBook.Path & "\" & fs.GetBaseName(WBook.Name)
fs.CreateFolder(ExportFolder)
For Each VBComp In WBook.VBProject.VBComponents
Select Case VBComp.Type
Case vbext_ct_ClassModule, vbext_ct_Document
Sfx = ".cls"
Case vbext_ct_MSForm
Sfx = ".frm"
Case vbext_ct_StdModule
Sfx = ".bas"
Case Else
Sfx = ""
End Select
If Sfx <> "" Then
On Error Resume Next
Err.Clear
VBComp.Export ExportFolder & "\" & VBComp.Name & Sfx
If Err.Number <> 0 Then
MsgBox "Failed to export " & ExportFolder & "\" & VBComp.Name & Sfx
End If
On Error Goto 0
End If
Next
xl.Quit
End If
End Sub
-Adam
-亚当
回答by Vijay
I have used this successfully for the past few years to export my code and save it. I can confirm it works in Office 2003, 2007. I assume it works in 2000 as well.
在过去的几年里,我成功地使用它来导出我的代码并保存它。我可以确认它适用于 Office 2003、2007。我认为它也适用于 2000。
回答by RedBlueThing
When I was doing a lot of Excel VBA development I got into the habit of exporting to the text format for each file (module, etc) each time I made a change (from the context menu). I kept those files in source control alongside the XLA binary. This worked pretty well for me and didn't require any external tools.
当我进行大量 Excel VBA 开发时,我养成了每次进行更改(从上下文菜单)时将每个文件(模块等)导出为文本格式的习惯。我将这些文件与 XLA 二进制文件一起保存在源代码管理中。这对我来说效果很好,不需要任何外部工具。
回答by Chris Spicer
SourceTools is good once you're up and running, but if you're needing to export from a large number of Excel workbooks, opening up each one and exporting it could be a bit tedious.
SourceTools 一旦启动并运行就很好,但如果您需要从大量 Excel 工作簿中导出,打开每个工作簿并导出它可能有点乏味。
VbaDiff (disclaimer: my product) has an API that can read through multiple Excel files and extract the VBA code. There's an example of here- it could easily be adapted to export the code to a file, ready to be checked in. If you're good with SharpSvn, you could probably add the code to the repository as you go!
VbaDiff(免责声明:我的产品)有一个 API,可以读取多个 Excel 文件并提取 VBA 代码。有一个例子在这里-它可以很容易地适应代码导出到文件,准备进行检查,如果你有好SharpSvn,你很可能代码,当您去添加到库中。!