code_junkie

Best way to do Version Control for MS Excel

Nov 11th, 2011
118
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 1.30 KB | None | 0 0
  1. Sub SaveCodeModules()
  2.  
  3. 'This code Exports all VBA modules
  4. Dim i%, sName$
  5.  
  6. With ThisWorkbook.VBProject
  7. For i% = 1 To .VBComponents.Count
  8. If .VBComponents(i%).CodeModule.CountOfLines > 0 Then
  9. sName$ = .VBComponents(i%).CodeModule.Name
  10. .VBComponents(i%).Export "X:ToolsMyExcelMacros" & sName$ & ".vba"
  11. End If
  12. Next i
  13. End With
  14.  
  15. End Sub
  16.  
  17. Sub ImportCodeModules()
  18.  
  19. With ThisWorkbook.VBProject
  20. For i% = 1 To .VBComponents.Count
  21.  
  22. ModuleName = .VBComponents(i%).CodeModule.Name
  23.  
  24. If ModuleName <> "VersionControl" Then
  25. If Right(ModuleName, 6) = "Macros" Then
  26. .VBComponents.Remove .VBComponents(ModuleName)
  27. .VBComponents.Import "X:DataMySheet" & ModuleName & ".vba"
  28. End If
  29. End If
  30. Next i
  31. End With
  32.  
  33. End Sub
  34.  
  35. Private Sub Workbook_Open()
  36.  
  37. ImportCodeModules
  38.  
  39. End Sub
  40.  
  41. Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
  42.  
  43. SaveCodeModules
  44.  
  45. End Sub
  46.  
  47. 'This code Exports all VBA modules
  48. Dim i%, sName$
  49.  
  50. With ThisWorkbook.VBProject
  51. For i% = 1 To .VBComponents.Count
  52. If .VBComponents(i%).CodeModule.CountOfLines > 0 Then
  53. sName$ = .VBComponents(i%).CodeModule.Name
  54. .VBComponents(i%).Export "C:Code" & sName$ & ".vba"
  55. End If
  56. Next i
  57. End With
Add Comment
Please, Sign In to add comment