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

Question

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.

Answer 1

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

Answer 2

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.

http://www.codeproject.com/KB/office/SourceTools.aspx

Answer 3

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.

Answer 4

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.

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!