从 Excel 电子表格中提取 VBA
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/846565/
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
Extracting VBA from a Excel spreadsheet
提问by nzpcmad
Is there a clean way to extract the VBA from a spreadsheet and store it in a repository.
是否有一种干净的方法可以从电子表格中提取 VBA 并将其存储在存储库中。
The spreadsheets hardly ever change but the VBA does. Storing the whole spreadsheet in the repository makes it difficult to do diffs across the different VBA revisions to see what code has changed and who changed it.
电子表格几乎不会改变，但 VBA 会改变。将整个电子表格存储在存储库中使得很难在不同的 VBA 修订版之间进行差异，以查看哪些代码已更改以及是谁更改了它。
We use VBA 6.5 / Excel 2003.
我们使用 VBA 6.5 / Excel 2003。
回答by Mitch Wheat
Previous versions of Excel and Access (prior to 2003) supported VBA Source Code Version Control via an add-in. I have used this very effectively in Office 2000.
早期版本的 Excel 和 Access（2003 年之前）通过加载项支持 VBA 源代码版本控制。我在 Office 2000 中非常有效地使用了它。
Visual SourceSafe (VSS) support for VBA was dropped with the release of Office 2003, but the add-in that was shipped with Office XP Developer apparently works with Office 2003.
随着 Office 2003 的发布，Visual SourceSafe (VSS) 不再支持 VBA ，但 Office XP Developer 附带的加载项显然适用于 Office 2003。
Microsoft Knowledge Base articles:
Microsoft 知识库文章：
Failing that you can use this code to extract the VBA code (from herebut it was missing the final object cleanups). Read the webpage for caveats:
如果您不能使用此代码来提取 VBA 代码（从这里但它缺少最终的对象清理）。阅读网页的注意事项：
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
Set fs = Nothing
Set xl = Nothing
End If
End Sub
回答by Vijay
Answered the same thing in another topic. Another alternative for you to try.
在另一个主题中回答了同样的问题。您可以尝试的另一种选择。
Exporting VBA code from Multiple Excel documents to put into version control
(is there a way to cross-link these topics and/or answers?)
（有没有办法交叉链接这些主题和/或答案？）
回答by RedBlueThing
回答by Eric
The other answers to this question address it pretty well, but if you're just starting to write the code, look into VSTO. It provides managed code for Office, and best of all, you can even do it in C# or whatever .NET language strikes your fancy. It also works with Excel 2003, which is a bonus, in your case.
这个问题的其他答案很好地解决了这个问题，但是如果您刚刚开始编写代码，请查看VSTO。它为 Office 提供托管代码，最重要的是，您甚至可以使用 C# 或任何您喜欢的 .NET 语言来实现。它也适用于 Excel 2003，在您的情况下，这是一个奖励。
回答by JonnyBoats
You already have a great answer, but allow me to add an aside.
你已经有一个很好的答案，但请允许我补充一点。
It is quite possible to keep all of your VBA code in a spreadsheet seperate from the spreadsheet(s) that contains the data. If you do this it might make your comparisons easier.
很可能将所有 VBA 代码保存在与包含数据的电子表格分开的电子表格中。如果你这样做，它可能会让你的比较更容易。
