<

Automate Exporting all Components in an Excel Project

Published on
20,071 Points
10,171 Views
4 Endorsements
Last Modified:
Awarded
Community Pick
Source Control is Important
This is just one reason why you might be interested in exporting your Excel project code. Excel files are in a format such that revision control software can't do much other than save a copy of every version of your file. Especially if you're most concerned about the code you develop in a VBA project, you might be interested in rolling back changes, differences, and comparing code in other ways. The only way to do that is to export all sheet code, modules, and forms to text based files and add those to a repository.

 Source Control
You might also want to export your modules simply to create personal backups of your code, add it to a visual studio project, send it to a friend, etc. Whatever your reason, it's a tedious task. Each time you want to export code, you have to do it one module at a time, browse for the save location, save it, and then move on to the next file. Often times it's done infrequently enough that you don't need a script, but if it becomes a regular part of your day, or you decide to back up years worth of excel projects, it might be time to automate the process.

Automating Module Exporting
I've created a VB script that does an all-round decent job of exporting any of the 4 component types in an automated way. As a bonus, it checks the components to see if there is actually any code in them - if not, there's no point in exporting an empty file, so I skip it.
Option Explicit
'Remember to add a reference to Microsoft Visual Basic for Applications Extensibility 
'Exports all VBA project components containing code to a folder in the same directory as this spreadsheet.
Public Sub ExportAllComponents()
    Dim VBComp As VBIDE.VBComponent
    Dim destDir As String, fName As String, ext As String 
    'Create the directory where code will be created.
    'Alternatively, you could change this so that the user is prompted
    If ActiveWorkbook.Path = "" Then
        MsgBox "You must first save this workbook somewhere so that it has a path.", , "Error"
        Exit Sub
    End If
    destDir = ActiveWorkbook.Path & "\" & ActiveWorkbook.Name & " Modules"
    If Dir(destDir, vbDirectory) = vbNullString Then MkDir destDir
    
    'Export all non-blank components to the directory
    For Each VBComp In ActiveWorkbook.VBProject.VBComponents
        If VBComp.CodeModule.CountOfLines > 0 Then
            'Determine the standard extention of the exported file.
            'These can be anything, but for re-importing, should be the following:
            Select Case VBComp.Type
                Case vbext_ct_ClassModule: ext = ".cls"
                Case vbext_ct_Document: ext = ".cls"
                Case vbext_ct_StdModule: ext = ".bas"
                Case vbext_ct_MSForm: ext = ".frm"
                Case Else: ext = vbNullString
            End Select
            If ext <> vbNullString Then
                fName = destDir & "\" & VBComp.Name & ext
                'Overwrite the existing file
                'Alternatively, you can prompt the user before killing the file.
                If Dir(fName, vbNormal) <> vbNullString Then Kill (fName)
                VBComp.Export (fName)
            End If
        End If
    Next VBComp
End Sub

Open in new window

There's room to make some changes. The behaviour that suits me best is making a new directory alongside the workbook called "(Workbook name) Modules" and filling it with all the code. This is coded in the script, but could easily be changed to some other path, or to prompt the user for the path. You can also customize the exporting extension, but I use the standard extensions excel uses so that files can be re-imported easily. Finally, I automatically overwrite any existing versions of the exported files, but you could easily arrange to prompt the user first. If using the code directly, remember to use the Reference mentioned at the top of the code.

But adding this code to every workbook is also tedious...
And so we must go to the next level - converting it into an Excel Add-In - this way we can use the export feature easily on any open workbook. Following these handy tips on how to build an Add-In, I created one. I've attached it to this article so that others can benefit from it too:
Export-Components.xls

It's attached it as an xls file, but by just renaming an xla file, it will be an Add-In. To make it easily findable, drag it to your (username)/Application Data/Microsoft/AddIns/ folder and it will be easily findable under options AddIns. Will work in Excel 2003 or 2007. Here I show how I've added the macro to the toolbar as a small button next to save:
AddIn and button
Note that to use the AddIn, you'll of course need to give your VBProject trusted access in Excel. If you haven't already configured excel to trust the VBProject, you must do so. In Excel 2007, you go to Options > Trust Center > Add-ins and deselect "Require Application Add-ins to be signed by Trusted Publisher". You should also go to "Macro Settings" in this same location and Enable macros and check "Trust access to the VBA project object model. I'm sure this isn't a problem for you though, because if you're interested in this automated method to export macros and modules you're written in Excel VBA, you're probably already all over the Excel security stuff.

Summary
Now that there's an easy way to export all of your Excel VBA project into a human-readable format, maintaining a code repository or doing proper versioning isn't so bad. Look - I know Excel VBA isn't widely considered a proper development environment, but sometimes you're forced to adapt to what you have to work with. Relieving that bottleneck so that you can work with code in tools like Diff can really take the edge off of stressful Excel programming.

 Now we can Diff!
By the way, all source control screen-shots are of tools provided by Tortoise SVN.
Here's to better coding practice, in spite of Microsoft's outdated constructs!

--
Alain Bryden

References
Creating an Excel Add-In
Exporting VBA Modules
VBComponents
Tortoise SVN
4
Comment
Author:alainbryden
  • 3
  • 2
6 Comments

Expert Comment

by:JonahGroup
Hi Alain,

Your add-in is exactly what I was looking for. I can manage to get it installed, but I can't get the button to show beside the save button. I'm using Excel 2007 on Vista sp1.Any suggestions?

Scott.
0
LVL 21

Author Comment

by:alainbryden
Hey Jonah,

For the part where you actually create an Add-In (and add the button), that's not my specialty. I pulled it off by following the steps in the article I linked to. You should follow it closely too: http://www.fontstuff.com/vba/vbatut03.htm
0

Expert Comment

by:JonahGroup
Thanks Alain.

Yea, that's the part where I'll have to do some research. The page you've referenced isn't very explicit about this. Near the bottom of the page, there's a section titled:
Assigning Add-In Macros to Custom Toolbar Buttons and Menu Items
...but I don't quite understand it. Ideally, I'd like to create a button in the Custom Toolbar of the "Add-In" ribbon, and attach your function to it.

Scott.
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

LVL 21

Author Comment

by:alainbryden
Ah, yeah I kind of wanted to do that too, but knowing nothing about modifying the ribbon, I settled for the simple "Quick Access Toolbar" which you can add to just by right clicking it.

It's not just us either. According to this xtremevbtalk thread, the existing methods of manipulating the ribbon involve programatically changing it from a COM or .NET add-in,  or hacking the XML of an XLA add-in. It does give advice on how to do the latter in the simplest way possible, but it's still not very simple.

Perhaps this 'ribbon customizer' add-on would be of use?
http://word.mvps.org/faqs/customization/customizeribbon.htm

Alain
0
LVL 85

Expert Comment

by:Rory Archibald
One of the best sites for Ribbon customisation is Ron de Bruin's here: http://www.rondebruin.nl/ribbon.htm
0

Expert Comment

by:JonahGroup
That was the part which I didn't clue-in; when you say:
"Here I show how I've added the macro to the toolbar as a small button next to save"
I didn't know how to do that.

"Quick Access Toolbar" was the missing key....

1. Click the Office button, top left.
2. Press "Excel Options" button
3. Choose "Customize"
4. From the "Popular Commands" select list, choose: Macros
5. Select "ExportAllComponents" from the list, and click "Add".
6. Click Ok.

0

Featured Post

Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

Join & Write a Comment

Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month