从多个Excel文档导出VBA代码以进行版本控制 [英] Exporting VBA code from Multiple Excel documents to put into version control

问题描述

有没有人知道从多个Excel文档中导出VBA代码的方式，以便可以将代码添加到Subversion存储库中？无需手动打开每个文档并导出代码。

解决方案

您将在此找到一个工具：

http://www.pretentiousname.com/excel_extractvba /index.html

这是一个自动化excel的VBS脚本。您可以根据自己的需要进行修改 - 请注意，这并不完美（请阅读网页上的注意事项）。

  
 
 Const vbext_ct_ClassModule = 2 
 Const vbext_ct_Document = 100 
 Const vbext_ct_MSForm = 3 
 Const vbext_ct_StdModule = 1 
 
主
 
 Sub Main 
 Dim xl 
 Dim fs 
 Dim WBook 
 Dim VBComp 
 Dim Sfx 
 Dim ExportFolder 
 
如果Wscript.Arguments.Count<> 1然后
 MsgBox作为唯一的参数，将FULL路径提供给XLS文件以从中提取所有VBA。 
 Else 
 
设置xl = CreateObject（Excel.Application）
设置fs = CreateObject（Scripting.FileSystemObject）
 
 xl.Visible = true 
 
设置WBook = xl.Workbooks.Open（Trim（wScript.Arguments（0）））
 
 ExportFolder = WBook.Path& \& fs.GetBaseName（WBook.Name）
 
 fs.CreateFolder（ExportFolder）
 
对于每个VBComp在WBook.VBProject.VBComponents 
选择案例VBComp.Type 
案例vbext_ct_ClassModule，vbext_ct_Document 
 Sfx =.cls
案例vbext_ct_MSForm 
 Sfx =.frm
案例vbext_ct_StdModule 
 Sfx =.bas
 Case Else 
 Sfx =
 End选择
如果Sfx& 然后
 On Error Resume Next 
 Err.Clear 
 VBComp.Export ExportFolder& \& VBComp.Name& Sfx 
如果Err.Number<> 0然后
 MsgBox无法导出& ExportFolder& \& VBComp.Name& Sfx 
如果
发生错误Goto 0 
如果
结束
 
 xl.Quit 
如果
 End Sub 
  

-Adam

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.

解决方案

You'll find a tool for this here:

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).

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

这篇关于从多个Excel文档导出VBA代码以进行版本控制的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！