从Excel电子表格中提取VBA[已关闭]

Excel和Access的早期版本(2003年之前)通过外接程序支持VBA源代码版本控制。我在Office 2000中非常有效地使用了这一功能。

已取消对VBA的Visual SourceSafe(VSS)支持 dropped with the release of Office 2003 ,但Office XP Developer附带的外接程序 works with Office 2003 .

Microsoft知识库文章:

• Using Visual SourceSafe with Documents and VBA Code

• Using the Visual SourceSafe Add-In with the Visual Basic Environment

here 但它缺少最终的对象清理)。请阅读网页了解注意事项:

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

VSTO . 它为Office提供托管代码,最重要的是,你甚至可以用C#或任何你喜欢的.NET语言来实现。它也适用于Excel2003,在您的情况下,这是一个额外的功能。

• Multiple developers in a VBA project

• Exporting VBA code from Multiple Excel documents to put into version control

在另一个话题上回答了同样的问题。

Exporting VBA code from Multiple Excel documents to put into version control

(是否有办法将这些主题和/或答案交叉链接?)

你已经有了一个很好的答案,但请允许我补充一点。

很可能将所有VBA代码保存在电子表格中,与包含数据的电子表格分开。如果你这样做,可能会使你的比较更容易。