extract_source_from_excel_file.md

How to extract to text source files the content of an excel spreadsheet code

I have a pair of excels with some interesting code I want to keep track in git. Keeping the whole excel is the easiest way to do, but I wondered how store a copy of the source files in a separate folder in order to maintain an adequate control over the source files itself.

Below is an vbs script I have to export the code of all the excel files of a directory into another directory. You have to do two things:

1. configure the excelDir and sourcesDir with the apropiate values for your project and 2) allow the macro inspection of the projects, which is done by:

Excel -> options -> Trust Center -> Trust Center Settings -> Macro settings 

then click on "Trust access to the VBA project object model"

' 
' https://stackoverflow.com/questions/22911487/extract-excel-vba-macros-from-excel-files-programatically
'

option explicit

Const vbext_ct_ClassModule  = 2
Const vbext_ct_Document     = 100
Const vbext_ct_MSForm       = 3
Const vbext_ct_StdModule    = 1

Dim WB
Dim VBProj
Dim VBComp
Dim excelDir
Dim sourcesDir
Dim StrFile
dim fso 
dim excelApp
dim file 
dim file_extension

excelDir = "HERE-GOES-THE-PATH-TO-THE-EXCEL-DIRECTORY"
sourcesDir = "HERE-GOES-THE-PATH-WHERE-THE-SOURCES-ARE-LEFT"

set fso = createobject("scripting.filesystemobject")
set excelApp = createObject("excel.application")
excelApp.visible = true 

If not fso.folderExists( sourcesDir) Then 
    fso.createFolder( sourcesDir )
end if 


for each file in fso.getFolder( excelDir ).files
    excelApp.ScreenUpdating = False
    excelApp.EnableEvents = False
    If LCase(Right(file.Name, 5)) = ".xlsm" and lcase(left(file.name, 2)) <> "~$" Then
        Set WB = excelApp.Workbooks.Open(excelDir & "\" & file.name, False)
        Set VBProj = WB.VBProject
            For Each VBComp In VBProj.vbcomponents
                Select Case VBComp.Type
                    Case vbext_ct_ClassModule, vbext_ct_Document:
                        file_extension = ".cls"
                    Case vbext_ct_MSForm:
                        file_extension = ".frm"
                    Case vbext_ct_StdModule:
                        file_extension = ".bas"
                    Case Else:
                        file_extension = ".txt"
                End Select                
                If VBComp.codemodule.countoflines > 0 Then 
                    VBComp.Export sourcesDir & "\" & file.Name & "." & VBComp.Name & file_extension
                end if 
            Next
        WB.Close False
    end if 
    excelApp.ScreenUpdating = true
    excelApp.EnableEvents = true
next 

set excelApp = nothing