Exporting
Every object in a Project can be saved to a separate file.
Saving an object to an separate file is known as exporting. To export an object (File > Export) or (Ctrl + E).
You will get a dialog box asking for the filename.
A copy of the file is then moved. The original file is not moved.
Userforms
When you export a Userform any code associated with that userform will also get exported.
.frm - when exported
.frx File
link - excelvbasolutions.com/2021/01/frx-file.html
If you try to delete a file from the Export File dialog box this seems to take forever.
'add a reference to "Microsoft Visual Basic for Applications Extensibility 5.3
Private Sub Document_Open()
Dim sFolderPrefix As String
Dim sFolderPath As String
sFolderPrefix = "C:\Temp\"
sFolderPath = "Folder\Source Code\Exported Code\"
Call ExportAllModules(sFolderPrefix & sFolderPath, "_2", True)
End Sub
Public Sub ExportAllModules( _
ByVal sFolderPath As String, _
ByVal sFileSuffix As String, _
ByVal bSaveInSubFolders As Boolean)
Dim VBProj As VBIDE.VBProject
Dim VBComp As VBIDE.VBComponent
Dim CodeMod As VBIDE.CodeModule
Set VBProj = ActiveDocument.VBProject
For Each VBComp In VBProj.VBComponents
If ((VBComp.Type = vbext_ct_Document) Or _
(VBComp.Type = vbext_ct_StdModule) Or _
(VBComp.Type = vbext_ct_MSForm)) Then
Set CodeMod = VBComp.CodeModule
Call ExportVBComponent(VBComp, sFolderPath, sFileSuffix, VBComp.CodeModule, True, bSaveInSubFolders)
End If
Next VBComp
End Sub
Public Function ExportVBComponent( _
ByVal VBComp As VBIDE.VBComponent, _
ByVal sFolderPath As String, _
ByVal sFileSuffix As String, _
Optional ByVal sFileName As String, _
Optional ByVal bOverwriteExisting As Boolean = True, _
Optional ByVal bSaveInSubFolders As Boolean = False) As Boolean
Dim sExtension As String
Dim sFName As String
sExtension = GetFileExtension(VBComp:=VBComp)
sFName = sFileName
If InStr(1, sFName, ".", vbBinaryCompare) = 0 Then
If (sFileSuffix = "") Then
sFName = sFName & sExtension
Else
sFName = sFName & sFileSuffix & sExtension
End If
End If
If StrComp(Right(sFolderPath, 1), "\", vbBinaryCompare) <> 0 Then
sFolderPath = sFolderPath & "\"
End If
If (bSaveInSubFolders = False) Then
sFName = sFolderPath & sFName
End If
If (bSaveInSubFolders = True) Then
sFName = sFolderPath & VBComp.Name & "\" & sFName
If (Folder_Exists(sFolderPath & VBComp.Name) = False) Then
Call MkDir(sFolderPath & VBComp.Name)
End If
End If
If Dir(sFName, vbNormal + vbHidden + vbSystem) <> vbNullString Then
If (bOverwriteExisting = True) Then
Kill sFName
Else
ExportVBComponent = False
Exit Function
End If
End If
VBComp.Export FileName:=sFName
ExportVBComponent = True
End Function
Public Function GetFileExtension( _
ByVal VBComp As VBIDE.VBComponent) As String
Select Case VBComp.Type
Case vbext_ct_ClassModule
GetFileExtension = ".cls"
Case vbext_ct_Document
GetFileExtension = ".cls"
Case vbext_ct_MSForm
GetFileExtension = ".frm"
Case vbext_ct_StdModule
GetFileExtension = ".bas"
Case Else
GetFileExtension = ".bas"
End Select
End Function
Public Function Folder_Exists( _
ByVal sFolderPath As String) As Boolean
Dim iTemp As Integer
On Error GoTo AnError
iTemp = GetAttr(sFolderPath)
Folder_Exists = True
Exit Function
AnError:
Folder_Exists = False
End Function
Public Sub ExportVisualBasicCode()
Const Module = 1
Const ClassModule = 2
Const Form = 3
Const Document = 100
Const Padding = 24
Dim VBComponent As Object
Dim Count As Integer
Dim path As String
Dim directory As String
Dim extension As String
Dim fso As New FileSystemObject
directory = ActiveWorkbook.path '& "\Export-Folder"
Count = 0
If Not fso.FolderExists(directory) Then
Call fso.CreateFolder(directory)
End If
Set fso = Nothing
For Each VBComponent In ActiveWorkbook.VBProject.VBComponents
Select Case VBComponent.Type
Case ClassModule, Document
extension = ".cls"
Case Form
extension = ".frm"
Case Module
extension = ".bas"
Case Else
extension = ".txt"
End Select
path = directory & "\" & VBComponent.Name & extension
Call VBComponent.Export(path)
If Err.Number <> 0 Then
Call MsgBox("Failed to export " & VBComponent.Name & " to " & path, vbCritical)
Else
Count = Count + 1
Debug.Print "Exported " & Left(VBComponent.Name & ":" & Space(Padding), Padding) & path
End If
Next
Debug.Print "Successfully exported " & CStr(Count) & " VBA files to " & directory
End Sub
© 2024 Better Solutions Limited. All Rights Reserved. © 2024 Better Solutions Limited TopPrevNext