I’m writing a macro to automatically export the code modules. I’m adapting code I found here: /questions/62744015/how-to-programmatically-export-and-import-code-into-excel-worksheet. I have a command button on a worksheet to initiate the process. I like to group my exports by type; a folder for worksheets, forms, modules, and classes. The problem is the code runs without error but does not actually export or do anything from what I can tell. I do realize that there are more backups than I need. It’s an silly office process to back up in a couple of locations. Anyway, any help would be greatly appreciated.

Button Click in the worksheet module:

Private Sub Backup_Click()

Dim i, j
Dim vbcomp As VBComponent
Dim vbmod As VBIDE.CodeModule
Dim msg As Integer

Dim wkspath As String: wkspath = "\MyBackupLocation" & _
                                 "Worksheets"

Dim frmpath As String: frmpath = "\MyBackupLocation" & _
                                 "Forms"


Dim modpath As String: modpath = "\MyBackupLocation" & _
                                 "Modules"

Dim clspath As String: clspath = "\MyBackupLocation\" & _
                                 "Classes"

'turns stuff off
ThisWorkbook.Application.ScreenUpdating = False
ThisWorkbook.Application.DisplayStatusBar = False
ThisWorkbook.Application.EnableEvents = False
ThisWorkbook.Application.DisplayAlerts = False

For Each vbcomp In ThisWorkbook.VBProject.VBComponents
    Select Case vbcomp.name
    Case Is = "Worksheet1", _
              "Worksheet2", _
              "Worksheet3", _
              "Worksheet4", _
              "Worksheet5", _
              "Worksheet6", _
              "ThisWorkbook":
                  
        Call SaveSoftwareFile(wkspath, vbcomp.name, vbcomp.name & ".cls")
    Case Is = "Form1", _
              "Form2", _
              "Form3", _
              "Form4", _
              "Form5":
                  
        Call SaveSoftwareFile(frmpath, vbcomp.name, vbcomp.name & ".frm")
    Case Is = "Mod1", _
              "Mod2", _
              "Mod3", _
              "Mod4", _
              "Mod5", _
              "Mod6", _
              "Mod7", _
              "Mod8", _
              "Mod9", _
              "Mod10", _
              "Mod11", _
              "Mod12", _
              "Mod13", _
              "Mod14", _
              "Mod15", _
              "Mod16", _
              "Mod17", _
              "Mod18":

        Call SaveSoftwareFile(modpath, vbcomp.name, vbcomp.name & ".bas")
    Case Is = "clsFormChange", _
              "Dictionary", _
              "MouseOverControl":
        
        Call SaveSoftwareFile(clspath, vbcomp.name, vbcomp.name & ".cls")
    End Select
Next vbcomp

ThisWorkbook.SaveAs "\MyBackupLocation" & Replace(ThisWorkbook.name, "Dev", "Prod")

ThisWorkbook.SaveAs "\MyBackupLocation" & ThisWorkbook.name

ThisWorkbook.SaveAs "C:MyBackupLocation" & Replace(ThisWorkbook.name, "Prod", "Dev")


msg = MsgBox("Master Workook code exported and file saved", vbOKOnly, "Finished")

'turns stuff off
ThisWorkbook.Application.ScreenUpdating = True
ThisWorkbook.Application.DisplayStatusBar = True
ThisWorkbook.Application.EnableEvents = True
ThisWorkbook.Application.DisplayAlerts = True

End Sub

And the function in a regular module:

Public Sub SaveSoftwareFile(ByVal path As String, ByVal CodeModuleName As String, ByVal FileName As String)

Dim WsModuleCode As String, sCM As VBIDE.CodeModule, strPath As String, FileNum As Long

Set sCM = ThisWorkbook.VBProject.VBComponents(CodeModuleName).CodeModule
WsModuleCode = sCM.lines(1, sCM.CountOfLines)

Debug.Print WsModuleCode
strPath = path & FileName
FileNum = FreeFile
Open strPath For Output As #FileNum
    Print #FileNum, WsModuleCode
Close #FileNum
End Sub