Macro to Backup Excel VBA Code within an Excel Workbook

Access to the VBProject Object must be trusted in order for this code to run.

I'll update this hopefully to reference the root directory of the project file and replace the current static path. This would be a great script to implement along with some Git version control so that you can make your changes in the Excel VBA IDE and then push down to your git repo to be committed up to Github.

Option Explicit


Sub export_all_the_code()
  
  ' reference to extensibility library
  Dim objMyProj As VBProject
  Dim objVBComp As VBComponent
  
  Set objMyProj = Application.VBE.ActiveVBProject

  For Each objVBComp In objMyProj.VBComponents
    If objVBComp.CodeModule.CountOfLines > 2 Then
      If objVBComp.Type = vbext_ct_StdModule Then
        objVBComp.Export "D:\Project\vba backup\" & "latest\" & "modules\" & objVBComp.name & ".bas"
      ElseIf objVBComp.Type = vbext_ct_Document Then
        objVBComp.Export "D:\Project\vba backup\" & "latest\" & "objects\" & objVBComp.name & ".cls"
      ElseIf objVBComp.Type = vbext_ct_ClassModule Then
        objVBComp.Export "D:\Project\vba backup\" & "latest\" & "classes\" & objVBComp.name & ".cls"
      Else
        objVBComp.Export "D:\Project\vba backup\" & "latest\" & objVBComp.name
      End If
    End If
  Next

End Sub