Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- Public Sub ExportModules()
- Dim bExport As Boolean
- Dim wkbSource As Excel.Workbook
- Dim szSourceWorkbook As String
- Dim szExportPath As String
- Dim szFileName As String
- Dim cmpComponent As VBIDE.VBComponent
- 'In the VBE Editor set a reference to "Microsoft Visual Basic For Applications Extensibility 5.3"
- 'and to "Microsoft Scripting Runtime" and then save the file.
- Application.Calculation = xlCalculationManual
- Application.ScreenUpdating = False
- FolderWithVBAProjectFiles = "C:\Users\MyUser\Documents\GitHub\crypto_vba\"
- ProjectFile = "crypto_vba_example.xlsm"
- ''' The code modules will be exported in a folder named.
- ''' VBAProjectFiles in the Documents folder.
- ''' The code below create this folder if it not exist
- ''' or delete all files in the folder if it exist.
- If FolderWithVBAProjectFiles = "Error" Then
- MsgBox "Export Folder not exist"
- Exit Sub
- End If
- ''' NOTE: This workbook must be open in Excel.
- szSourceWorkbook = ActiveWorkbook.Name
- Set wkbSource = Application.Workbooks(szSourceWorkbook)
- szExportPath = FolderWithVBAProjectFiles
- Set wkbDest = Application.Workbooks.Open(FolderWithVBAProjectFiles & ProjectFile)
- wkbDest.Windows(1).Visible = False
- Set Cmps = wkbDest.VBProject.VBComponents
- For Each cmpComponent In Cmps
- If cmpComponent.Type = vbext_ct_StdModule Then
- Cmps.Item(cmpComponent.Name).Name = cmpComponent.Name & "_OLD"
- End If
- Next cmpComponent
- For Each cmpComponent In wkbSource.VBProject.VBComponents
- bExport = True
- szFileName = cmpComponent.Name
- ''' Concatenate the correct filename for export.
- Select Case cmpComponent.Type
- Case vbext_ct_ClassModule
- szFileName = szFileName & ".cls"
- Case vbext_ct_MSForm
- szFileName = szFileName & ".frm"
- Case vbext_ct_StdModule
- szFileName = szFileName & ".bas"
- Case vbext_ct_Document
- ''' This is a worksheet or workbook object.
- ''' Don't try to export.
- bExport = False
- End Select
- Select Case szFileName
- Case "ModHash.bas", "JsonConverter.bas", "ModJSON.bas", "ModFunctions.bas"
- ' If 'Item' is in the list then do something.
- On Error Resume Next
- Kill FolderWithVBAProjectFiles & szFileName
- On Error GoTo 0
- ''' Export the component to a text file.
- cmpComponent.Export szExportPath & szFileName
- wkbDest.VBProject.VBComponents.Import szExportPath & szFileName
- End Select
- Next cmpComponent
- For Each cmpComponent In Cmps
- If cmpComponent.Type = vbext_ct_StdModule Then
- If InStr(cmpComponent.Name, "_OLD") > 0 Then
- Cmps.Remove Cmps.Item(cmpComponent.Name)
- End If
- End If
- Next cmpComponent
- wkbDest.Windows(1).Visible = True
- wkbDest.Close Savechanges:=True
- Application.Calculation = xlCalculationAutomatic
- Application.ScreenUpdating = True
- End Sub
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement