SHARE
TWEET

Untitled

a guest Nov 20th, 2017 193 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. Public Sub ExportModules()
  2.     Dim bExport As Boolean
  3.     Dim wkbSource As Excel.Workbook
  4.     Dim szSourceWorkbook As String
  5.     Dim szExportPath As String
  6.     Dim szFileName As String
  7.     Dim cmpComponent As VBIDE.VBComponent
  8.  
  9.     'In the VBE Editor set a reference to "Microsoft Visual Basic For Applications Extensibility 5.3"
  10.    'and to "Microsoft Scripting Runtime" and then save the file.
  11.    Application.Calculation = xlCalculationManual
  12.     Application.ScreenUpdating = False
  13.    
  14.     FolderWithVBAProjectFiles = "C:\Users\MyUser\Documents\GitHub\crypto_vba\"
  15.     ProjectFile = "crypto_vba_example.xlsm"
  16.     ''' The code modules will be exported in a folder named.
  17.    ''' VBAProjectFiles in the Documents folder.
  18.    ''' The code below create this folder if it not exist
  19.    ''' or delete all files in the folder if it exist.
  20.    If FolderWithVBAProjectFiles = "Error" Then
  21.         MsgBox "Export Folder not exist"
  22.         Exit Sub
  23.     End If
  24.    
  25.     ''' NOTE: This workbook must be open in Excel.
  26.    szSourceWorkbook = ActiveWorkbook.Name
  27.     Set wkbSource = Application.Workbooks(szSourceWorkbook)
  28.     szExportPath = FolderWithVBAProjectFiles
  29.        
  30.     Set wkbDest = Application.Workbooks.Open(FolderWithVBAProjectFiles & ProjectFile)
  31.     wkbDest.Windows(1).Visible = False
  32.    
  33.     Set Cmps = wkbDest.VBProject.VBComponents
  34.     For Each cmpComponent In Cmps
  35.         If cmpComponent.Type = vbext_ct_StdModule Then
  36.             Cmps.Item(cmpComponent.Name).Name = cmpComponent.Name & "_OLD"
  37.         End If
  38.     Next cmpComponent
  39.    
  40.     For Each cmpComponent In wkbSource.VBProject.VBComponents
  41.        
  42.         bExport = True
  43.         szFileName = cmpComponent.Name
  44.  
  45.         ''' Concatenate the correct filename for export.
  46.        Select Case cmpComponent.Type
  47.             Case vbext_ct_ClassModule
  48.                 szFileName = szFileName & ".cls"
  49.             Case vbext_ct_MSForm
  50.                 szFileName = szFileName & ".frm"
  51.             Case vbext_ct_StdModule
  52.                 szFileName = szFileName & ".bas"
  53.             Case vbext_ct_Document
  54.                 ''' This is a worksheet or workbook object.
  55.                ''' Don't try to export.
  56.                bExport = False
  57.         End Select
  58.        
  59.         Select Case szFileName
  60.           Case "ModHash.bas", "JsonConverter.bas", "ModJSON.bas", "ModFunctions.bas"
  61.             ' If 'Item' is in the list then do something.
  62.            On Error Resume Next
  63.             Kill FolderWithVBAProjectFiles & szFileName
  64.             On Error GoTo 0
  65.             ''' Export the component to a text file.
  66.            cmpComponent.Export szExportPath & szFileName
  67.             wkbDest.VBProject.VBComponents.Import szExportPath & szFileName
  68.         End Select
  69.     Next cmpComponent
  70.    
  71.     For Each cmpComponent In Cmps
  72.         If cmpComponent.Type = vbext_ct_StdModule Then
  73.             If InStr(cmpComponent.Name, "_OLD") > 0 Then
  74.                 Cmps.Remove Cmps.Item(cmpComponent.Name)
  75.             End If
  76.         End If
  77.     Next cmpComponent
  78.  
  79.    
  80.     wkbDest.Windows(1).Visible = True
  81.     wkbDest.Close Savechanges:=True
  82.    
  83.     Application.Calculation = xlCalculationAutomatic
  84.     Application.ScreenUpdating = True
  85.  
  86. End Sub
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
Top