Aney Adventures Online
Aney Adventures Online
To understand your parents' love, you must raise children yourself.
Subscribe to Our FeedRSS Feed

Source Control workarounds for Excel xla files

Saturday, 12 April 2008 05:45 by Terry Aney

In my last post I explained reasons and gotchas to be concerned with when deciding to migrate from an Excel *.xla add-in to a C# add in.  One of the reasons revolved around not having an easy way to use a source control product (we use Visual SourceSafe) to manage the actual code files of a VBAProject.  This is because the *.xla is a binary file and using SourceSafe (and I would assume other products) you can not compare differences of a binary file (not to mention the VBAProject files that are part of the binary blob).

I mentioned that I automated getting the code files out of the VBAProject and into SourceSafe.  I am sure there third party add-ins that allow a source control product to integrate into Excel, but the code is fairly simple to automate it yourself.

Basically the code flow is:

  1. Checkout all the files in a specified project in SourceSafe
  2. Delete the local folder that holds exported VBAProject files
  3. Export all VBAProject files
  4. Check all matching files in
  5. Add any new files
  6. Undo checkout and delete any files in SourceSafe that are no longer present in VBAProject

Disclaimer: This code works 'well' for me because of the 'process' I follow.  I'm sure there are ways for SourceSafe to get out of synch and I try checking out a file when it's already checked out or something like that.  Please make sure to test the code to be assured it fits your needs.  I take no responsibility for problems that might arise in your source control.

Here is the code that I have attached to a menu handler:

   1: '// Sample params
   2: '// sourceSafeIni: "\\IPAddress\SourceSafe\srcsafe.ini"
   3: '// sourceCodeProject: "$/RBL/Excel Addin/Source"
   4: '// sourceCodeFolder: "F:\BTR\RBL\Excel Addin\Source"
   5: Sub CheckInCodeFiles(sourceSafeIni As String, sourceCodeProject As String, sourceCodeFolder As String)
   6:     
   7: On Error GoTo Err_Handler
   8:  
   9:     Dim sourceSafe As Object: Set sourceSafe = CreateObject("SourceSafe")
  10:     Call sourceSafe.Open(sourceSafeIni)
  11:     
  12:     Dim vssFolder As Object: Set vssFolder = Nothing
  13:     Set vssFolder = sourceSafe.vssItem(sourceCodeProject)    '// Project must exist or an error will occur
  14:     vssFolder.CheckOut                                      '// Check out all files
  15:     
  16:     Call ExportCodeFiles(sourceCodeFolder, True)
  17:     
  18:     Dim fso As Scripting.FileSystemObject: Set fso = New Scripting.FileSystemObject
  19:     Dim sourceFile As file
  20:     
  21:     For Each sourceFile In fso.GetFolder(sourceCodeFolder).Files
  22:         If LCase(fso.GetExtensionName(sourceFile.Name)) <> "scc" Then
  23:             Call CheckInSourceSafeItem(vssFolder, sourceFile)
  24:         End If
  25:     Next '// sourceFile
  26:     
  27:     Dim vssItem As Object
  28:     For Each vssItem In vssFolder.Items
  29:         If vssItem.IsCheckedOut Then
  30:             '// Still checked out means the file was no longer in VBAProject, so undo checkout and 'delete'
  31:             vssItem.UndoCheckout
  32:             vssItem.Deleted = True
  33:         End If
  34:     Next '// vssItem
  35:     
  36:     Set vssFolder = Nothing
  37:     Set sourceSafe = Nothing
  38:     
  39:     Exit Sub
  40:     
  41: Err_Handler:
  42:     
  43:     '// Clean-up and re-throw...
  44:     
  45:     Dim errNumber As Long: errNumber = Err.number
  46:     Dim errSource As String: errSource = Err.source
  47:     Dim errDescription As String: errDescription = Err.description
  48:     
  49:     If Not vssFolder Is Nothing Then Set vssFolder = Nothing
  50:     If Not sourceSafe Is Nothing Then Set sourceSafe = Nothing
  51:     
  52:     Call Err.Raise(errNumber, errSource, errDescription)
  53:     
  54:     Resume
  55: End Sub
  56:  
  57: Sub CheckInSourceSafeItem(sourceProject As Object, sourceFile As file)
  58:  
  59:     Dim vssFile As Object: Set vssFile = Nothing
  60:     On Error Resume Next
  61:     Set vssFile = sourceProject.Items(0)(sourceFile.Name)
  62:     On Error GoTo 0
  63:     If Not vssFile Is Nothing Then
  64:         vssFile.CheckIn , sourceFile.path
  65:     Else
  66:         sourceProject.Add sourceFile.path
  67:     End If
  68:  
  69: End Sub
  70:  
  71: Sub ExportCodeFiles(basePath As String, Optional cleanDirectory As Boolean = False)
  72:  
  73: On Error GoTo Err_Handler:
  74:  
  75:     Dim fso As Scripting.FileSystemObject: Set fso = New Scripting.FileSystemObject
  76:     
  77:     If Not fso.FolderExists(basePath) Then
  78:         Call fso.CreateFolder(basePath)
  79:     ElseIf cleanDirectory Then
  80:         Call fso.DeleteFolder(basePath, True)
  81:         Call fso.CreateFolder(basePath)
  82:     End If
  83:     
  84:     Dim component As VBComponent
  85:     For Each component In ThisWorkbook.VBProject.VBComponents
  86:         Dim ext As String
  87:         Select Case True
  88:             Case component.Type = vbext_ct_ClassModule
  89:                 ext = ".cls"
  90:             Case component.Type = vbext_ct_MSForm
  91:                 ext = ".frm"
  92:             Case component.Type = vbext_ct_StdModule
  93:                 ext = ".bas"
  94:             Case component.Type = vbext_ct_Document
  95:                 ext = ".cls" '// ThisWorkbook: Can't be imported back in, but *.cls lets it be viewed in VB
  96:             Case Else
  97:                 Call Err.Raise(vbObjectError, , "Not supported.")
  98:         End Select
  99:         Call component.Export(fso.BuildPath(basePath, component.Name) & ext)
 100:     Next '//component
 101:     
 102:     Set fso = Nothing
 103:     
 104:     Exit Sub
 105:     
 106: Err_Handler:
 107:     
 108:     '// Clean-up and re-throw...
 109:     
 110:     Dim errNumber As Long: errNumber = Err.number
 111:     Dim errSource As String: errSource = Err.source
 112:     Dim errDescription As String: errDescription = Err.description
 113:     
 114:     Set fso = Nothing
 115:     
 116:     Call Err.Raise(errNumber, errSource, errDescription)
 117: End Sub

Let me know what you think or if you see any improvements :)

Be the first to rate this post

  • Currently 0/5 Stars.
  • 1
  • 2
  • 3
  • 4
  • 5
Tags:   , ,
Categories:   VBA / Visual Basic
Actions:   E-mail | Permalink | Comments (1) | Comment RSSRSS comment feed

Related posts

Comments

April 14. 2008 02:58 | Permalink

For the fun of it, I was trying to use Google to find this article (to see if Google is indexing my site yet) and I used the search term "Source Control VBAProject". As I guessed in my article there appears to be an add-in (developed by Microsoft no less) that does this.

The documentation I am referring to can be found at http://msdn2.microsoft.com/en-us/library/aa165076(office.10).aspx.

For those of you that worked with me in my VB6 days, looks like CodeAid wasn't needed ;) "Code Commenter and Error-Handler Add-In" - the features of that look pretty sweet/comprable to CodeAid's error handling features. But they didn't have a 'Find Dialog' as sweet as mine!

So the only gotcha...I can't find a download link anywhere Frown.

Terry Aney

Search

Categories

Some of our favorite blogs

Our Blogroll...

Disclaimer

Our personal blogs are for anyone's reading entertainment, but primarily it is a way of Mom and Dad journallying for Josh and/or Audrey, at least the vast majority of the time (Dad will throw some technical mumbo jumbo articles in there as well).

If we brag and/or tell stupid stories too often, it's not because we're infatuated with our kids and we want you to be too...well we're infatuated, but by no means are we trying to overload/bore you, but rather we're just getting our journal committed to 'virtual paper' that we hope some day Josh and Audrey will find a bit entertaining.

So expect exagerations, heaps of sappiness, and a general overkill of doting on our kids because the bottom line is that this is for them and we want them to know how much we love them.

 
Powered by BlogEngine.NET 1.3.0.0
Sign in