Home > VBA / Visual Basic > Source Control workarounds for Excel xla files

Source Control workarounds for Excel xla files

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:

'// sourceSafeIni: "\\[IPAddress}\{VSS DB Name}\srcsafe.ini"
'// sourceCodeProject: "{VSSBackupPath}/Source"
'// sourceCodeFolder: "{BackupPath}\Source"
Public Sub CheckInCodeFiles(sourceSafeIni As String, 
	sourceCodeProject As String, 
	sourceCodeFolder As String)
    
On Error GoTo Err_Handler

   Dim sourceSafe As Object
	Set sourceSafe = CreateObject("SourceSafe")
	Call sourceSafe.Open(sourceSafeIni)
    
   Dim vssFolder As Object: Set vssFolder = Nothing
	'// Project must exist or an error will occur
   Set vssFolder = sourceSafe.vssItem(sourceCodeProject)    
	'// Check out all files
   Call vssFolder.CheckOut(, sourceCodeFolder)
    
    Call ExportCodeFiles(sourceCodeFolder, True)
    
   Dim fso As Scripting.FileSystemObject
	Set fso = New Scripting.FileSystemObject
   Dim sourceFile As file
    
   For Each sourceFile In fso.GetFolder(sourceCodeFolder).Files
   		If LCase(fso.GetExtensionName(sourceFile.name)) <> "scc" Then
       	Call CheckInSourceSafeItem(vssFolder, sourceFile)
        End If
    Next '// sourceFile
    
	Dim vssItem As Object
   For Each vssItem In vssFolder.Items
		If vssItem.IsCheckedOut Then
			'// Still checked out means the file was no longer in 
			'// VBAProject, so undo checkout and 'delete'
			vssItem.UndoCheckout
			vssItem.Deleted = True
		End If
	Next '// vssItem
    
   Set vssFolder = Nothing
   Set sourceSafe = Nothing
    
   Exit Sub
    
Err_Handler:
    
   '// Clean-up and re-throw...
    
   Dim errNumber As Long: errNumber = Err.number
   Dim errSource As String: errSource = Err.source
   Dim errDescription As String: errDescription = Err.description
    
   If Not vssFolder Is Nothing Then Set vssFolder = Nothing
   If Not sourceSafe Is Nothing Then Set sourceSafe = Nothing
    
   Call Err.Raise(errNumber, errSource, errDescription)
    
   Resume
End Sub

Sub CheckInSourceSafeItem(sourceProject As Object, sourceFile As file)

    Dim vssFile As Object: Set vssFile = Nothing
    On Error Resume Next
    Set vssFile = sourceProject.Items(0)(sourceFile.name)
    On Error GoTo 0
    If Not vssFile Is Nothing Then
        vssFile.CheckIn , sourceFile.path
    Else
        sourceProject.add sourceFile.path
    End If

End Sub

Sub ExportCodeFiles(basePath As String, 
	Optional cleanDirectory As Boolean = False)

On Error GoTo Err_Handler:

   Dim fso As Scripting.FileSystemObject
	Set fso = New Scripting.FileSystemObject
    
   If Not fso.FolderExists(basePath) Then
        Call fso.CreateFolder(basePath)
   ElseIf cleanDirectory Then
        Call fso.DeleteFolder(basePath, True)
        Call fso.CreateFolder(basePath)
   End If
    
   Dim component As VBComponent
   For Each component In ThisWorkbook.VBProject.VBComponents
        Dim ext As String
        Select Case True
            Case component.Type = vbext_ct_ClassModule
                ext = ".cls"
            Case component.Type = vbext_ct_MSForm
                ext = ".frm"
            Case component.Type = vbext_ct_StdModule
                ext = ".bas"
            Case component.Type = vbext_ct_Document
                	'// ThisWorkbook: Can't be imported back in, 
					'// but *.cls lets it be viewed in VB
					ext = ".cls"
            Case Else
                Call Err.Raise(vbObjectError, , "Not supported.")
        End Select
		 Dim exportPath as String
		 exportPath = fso.BuildPath(basePath, component.name) & ext
        Call component.Export(exportPath)
   Next '//component
    
    Set fso = Nothing
    
    Exit Sub
    
Err_Handler:
    
    '// Clean-up and re-throw...
    
    Dim errNumber As Long: errNumber = Err.number
    Dim errSource As String: errSource = Err.source
    Dim errDescription As String: errDescription = Err.description
    
    Set fso = Nothing
    
    Call Err.Raise(errNumber, errSource, errDescription)
    Resume
End Sub

Let me know what you think or if you see any improvements 🙂

Advertisements
Categories: VBA / Visual Basic
  1. April 14, 2008 at 2:58 am

    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 [i]appears[/i] 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 :(.

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: