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