Add a Module in your VBA Project. Name it “Exports” and paste the following code into the module in its entirety. When your project is ready for export (and subsequent version control), click on the “Macros” button in the Developer ribbon, select “ExportVBAModules” and click “Run.”
Option Explicit
‘ Define a constant for the export location
Const EXPORT_PATH As String = “F:\VBAProjects\”
Sub ExportVBAModules()
Dim VBProj As VBIDE.VBProject
Dim VBComp As VBIDE.VBComponent
Dim objFSO As Object
Dim objFile As Object
Dim strPath As String
Dim strFile As String
Dim LineNum As Long
Dim modCode As String
Dim wbName As String
‘ Get the name of the workbook without extension
wbName = Left(ThisWorkbook.Name, InStrRev(ThisWorkbook.Name, “.”) – 1)
‘ Create a new folder in the specified location with workbook name and timestamp
strPath = EXPORT_PATH & wbName & “_VBAExport_” & Format(Now, “yyyymmdd_hhmmss”)
‘ Check if the EXPORT_PATH exists, if not, create it
Set objFSO = CreateObject(“Scripting.FileSystemObject”)
If Not objFSO.FolderExists(EXPORT_PATH) Then
objFSO.CreateFolder EXPORT_PATH
End If
‘ Create the export folder
MkDir strPath
‘ Reference Microsoft Visual Basic for Applications Extensibility 5.3 library
Set VBProj = ThisWorkbook.VBProject
For Each VBComp In VBProj.VBComponents
Select Case VBComp.Type
Case vbext_ct_ClassModule
strFile = strPath & “\” & VBComp.Name & “.cls”
Case vbext_ct_MSForm
strFile = strPath & “\” & VBComp.Name & “.frm”
Case vbext_ct_StdModule
strFile = strPath & “\” & VBComp.Name & “.bas”
Case vbext_ct_Document
‘ This is a worksheet or workbook object.
‘ Don’t export these unless you want to.
strFile = strPath & “\” & VBComp.Name & “.cls”
Case Else
strFile = strPath & “\” & VBComp.Name & “.txt”
End Select
If VBComp.CodeModule.CountOfLines > 0 Then
modCode = VBComp.CodeModule.Lines(1, VBComp.CodeModule.CountOfLines)
Set objFile = objFSO.CreateTextFile(strFile)
objFile.Write modCode
objFile.Close
End If
Next VBComp
MsgBox “Modules exported to ” & strPath
End Sub
Leave a Reply