Introduction

Recently another freelancer introduced GIT to me.

"Git is a free and open source distributed version control system designed to handle everything from small to very large projects with speed and efficiency." Copied this text from its home page, which you can find here.

http://git-scm.com/

From my point of view it is a very nice tool, written by a developer for developers.

But now for the actual topic of this article. Having a nice tool like GIT, it is still necessary to provide it with text files as changes to binaries cannot be displayed in a change history.

This raised the question as how to extract the source code from Excel VBA projects. Copying everything from modules, classes and forms into text files manually is painful and error prone.

Therefore the other freelancer suggested to provide me with some code he wrote long ago to extract VBA code from Excel binaries.

As I am very impatiant and could not wait for his code, I implemented a little tool in VB.NET.

VBaby - A tool to export and import VBA source code from Access and Excel files.

A new version of this free software including a GUI is available from the following link.

I do not provide support for the tool. However, if you find a bug let me know and I will try to fix it whenever I find time.

VBaby.exe

In case you are only interested in some source code to deal with VBA programmatically you'll find the whole source code for the tool in the section "The Source Code behind VBaby".

Prerequsites

Prerequsite for using either the GUI version of the tool or only the source code from the class files is to activate programmatic access to an impacted Microsoft applications VBProject.

If you do not grant programmatic access to the VBProject the tool will show the following error message.

Programmatic access to an applications VBProjects can be granted in Excel by following below instructions.

Exporting Source Code using VBaby

Using the tool including the GUI is quite easy. Put the .exe in any directory and execute it. You will see following window.

Click on "File > Select Source File" and select any Excel or Access file conatining a VBA project with source code.

The source file has to be processable by your current version of the application that is associated with a source file. E.g. xlsm files will cause exceptions, if the current version is Excel 2003.

After selecting the file, the available objects possibly containing VBA source code are listed in the left bottom frame of the main window.

 

Selecting a target directory on your system will automatically trigger the export of VBA source code from currently referenced source file.

You will receive a little log in the right botton frame of the main window stating, if an objects source code was exported or the export failed.

Now navigate to the target directory youselected before.

The result are new text files in selected target directory. The following table applies for source objects and file extensions.

Source Object Type File Extension Comment
Class .cls  
Module .bas  
User Form .frm, .frx

For Excel user forms two files per user form are created in target directory. One binary file (.frx) one text file holding the source code (.frm).

For Access user forms only one text file (.frm) is created also containing the encoded content to create a binary.

Excel Workbook .xld

An Excel object called ThisWorkbook available from a VBProject.

This file type is only created, if the object ThisWorkbook contains source code.

Importing VBA source code using VBaby.

In order to import VBA source code from before exported text files select "File > Reference Directory" from the menu bar.

The available source files from the directory will be listed in the bottom left frame of the main wiondow.

The software is currently not checking for valid file extensions like ".cls", but this will be incorporated in future versions.

For the moment be careful what you place in a source directory. It should only contain exported source files.

Then select a target file via "File > Select Target File" and the tool will automatically start importing the source files available in referenced source directory.

Failed imports will be flagged in the little log screen provided in the bottom right frame of the main window.

If one code module could not be exported this does not necessarily mean that the export of source code failed. It can also mean that the object a code module is associated with does not contain any source code.

Known Bugs

Please drop me an email, if encounter any bugs. I only did little testing on this, but I want to improve it, since I do not see me stopping VBA coding very soon.

Meanwhile you can view a list of known bugs below.

Excel object ThisWorkbook is imported as Class.

Source text files representing an Excel Workbook (.xld) can not imported fully automated yet.

This will be solved in a future version, but for now you will have to move this source code manually into the right place.

Access displays during processing.

Actually the application should not be displayed, but stay hidden in the background.

However I did not find the root cause for this bug yet.

Access asks to confirm saving changes to VBA Modules, Classes and User Forms for each VBComponent.

So far I know no solution to prevent this behaviour of Access. I will try to fix it in near future.

Just confirm saving each single VBA module or class and your done. The rest happens automatically again.

The Source Code behind VBaby

Below you can find the source code for the application.

VBabyApp

An interface used to provide an abstract view on applications like Excel and Access.

Imports Microsoft.Vbe.Interop

Public Interface VbabyApp
''' <summary>
''' Opening a source file, which should be processed by the application.
''' The source file is then referenced for later usage in a global object.
''' </summary>
''' <param name="fileInf">An IO.FileInfo object referencing a source file, which should be processed.</param>
''' <returns>True, if the file could be opened properly, esle false.</returns>
''' <remarks></remarks>
Function openFile(ByVal fileInf As IO.FileInfo) As Boolean

''' <summary>
''' Exiting an application and saving a referenced source or target file, if desired.
''' </summary>
''' <param name="saveChanges">
''' True, if the changes to a referenced source or target file should be saved, else false.
''' </param>
''' <remarks></remarks>
Sub exitApp(ByVal saveChanges As Boolean)

''' <summary>
''' Determining, if a provided FileInfo object is refernecing
''' a file that is matching the implementations representation
''' of a Microsoft Application that allows VBA coding.
''' </summary>
''' <param name="fileInf">A source or target file,
''' which should be tested for a representation
''' of an Microsoft Application.</param>
''' <returns>
''' True, if tested file matches this representation
''' of a specific Microsoft Application, else false.
''' </returns>
''' <remarks></remarks>
Function isFileMatchingApp(ByVal fileInf As IO.FileInfo) As Boolean

''' <summary>
''' Getting the VBProject from a referenced source or target file.
''' </summary>
''' <returns>An object of type VBProject.</returns>
''' <remarks></remarks>
Function getVbProject() As VBProject

''' <summary>
''' Determining, if a referenced source or target file has a VBProject.
''' </summary>
''' <returns>True, if referenced source or target file has a VBProject, else false.</returns>
''' <remarks></remarks>
Function hasVbProject() As Boolean

''' <summary>
''' Initializing a Microsoft Application represented by this implementation.
''' </summary>
''' <returns>True, if application was initialized successfully, else false.</returns>
''' <remarks></remarks>
Function initMsApplication() As Boolean

''' <summary>
''' Returning a name matching the Microsoft Application represented by
''' an implementation of this interface.
''' </summary>
''' <returns>A String matching the name as it is returned
''' by an Microsoft Application object of the application
''' represented by an implementation of this interface.</returns>
''' <remarks></remarks>
Function getAppName() As String

''' <summary>
''' Exporting a user form from a referenced source file.
''' </summary>
''' <param name="trgPath">A path to a directory a user form should be exported to.</param>
''' <param name="vbComp">A VbComponent representing a user form, which should be exported.</param>
''' <returns>True, if exporting a user form was successful, else false.</returns>
''' <remarks></remarks>
Function exportForm(ByVal trgPath As String, ByRef vbComp As VBComponent) As Boolean

''' <summary>
''' Importing a user form from a referenced target file.
''' </summary>
''' <param name="srcPath">The full path including file name to a text file representation of a user form, which should be imported.</param>
''' <param name="vbCompName">The target name of the user form. If already existing, it will be replaced by imported source file.</param>
''' <returns>True, if the user form has been imported successfully, else false.</returns>
''' <remarks></remarks>
Function importForm(ByVal srcPath As String, ByVal vbCompName As String) As Boolean

''' <summary>
''' Determining, if the VBProject can be accessed.
''' </summary>
''' <returns>True, if the VBProject of referenced source or target file is accessible, else false.</returns>
''' <remarks></remarks>
Function isVBAccessible() As Boolean
End Interface

VbabyExcel

An implementation of the interface VBabyApp representing Microsoft Excel.

Imports Microsoft.Vbe.Interop
Imports System.Collections

Public Class VbabyExcel
Implements VbabyApp

''' <summary>
'''
''' </summary>
''' <returns></returns>
''' <remarks></remarks>
Function isVBAccessible() As Boolean Implements VbabyApp.isVBAccessible
Dim isAccessible As Boolean
Dim pro As VBProject

pro = getVbProject()
isAccessible = Not IsNothing(pro)

If isAccessible Then
System.Runtime.InteropServices.Marshal.ReleaseComObject(pro)
pro = Nothing
End If

Return isAccessible
End Function

Function importForm(ByVal srcPath As String, ByVal vbCompName As String) As Boolean Implements VbabyApp.importForm
Dim ret As Boolean
Dim pro As VBProject = Nothing
Dim vbComps As VBComponents = Nothing
Dim vbComp As VBComponent = Nothing

Try
deleteVbComponent(vbCompName)
pro = getVbProject()
vbComps = pro.VBComponents
vbComp = vbComps.Import(srcPath)
ret = Not IsNothing(vbComp)
Catch ex As Exception
ret = False
Finally
releaseObject(vbComp)
vbComp = Nothing
releaseObject(vbComps)
vbComps = Nothing
releaseObject(pro)
pro = Nothing
End Try

Return ret
End Function

Private Function deleteVbComponent(ByVal vbCompName As String) As Boolean
Dim ret As Boolean
Dim comps As VBComponents = Nothing
Dim vbProj As VBProject = Nothing

vbProj = getVbProject()
Try
comps = vbProj.VBComponents
comps.Remove(comps.Item(vbCompName))
ret = True
Catch ex As Exception
ret = False
End Try

releaseObject(comps)
comps = Nothing
releaseObject(vbProj)
vbProj = Nothing

Return ret
End Function

Private Sub releaseObject(ByVal obj As Object)
If Not IsNothing(obj) Then
System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
obj = Nothing
End If
End Sub

Function exportForm(ByVal trgPath As String,
ByRef vbComp As VBComponent) As Boolean Implements VbabyApp.exportForm
Dim ret As Boolean

Try
vbComp.Export(trgPath)
ret = True
Catch
ret = False
End Try

Return ret
End Function

Function getAppName() As String Implements VbabyApp.getAppName
Return appName
End Function

Function openFile(ByVal fileInf As IO.FileInfo) As Boolean Implements VbabyApp.openFile
Dim isOpen As Boolean
Dim wrks As Object = Nothing

Try
wrks = app.Workbooks
wrk = wrks.Open(fileInf.FullName)
isOpen = True
Catch
isOpen = False
Finally
If Not IsNothing(wrks) Then
System.Runtime.InteropServices.Marshal.ReleaseComObject(wrks)
wrks = Nothing
End If
End Try

Return isOpen
End Function

Sub exitApp(ByVal saveChanges As Boolean) Implements VbabyApp.exitApp
Try
If saveChanges Then
wrk.Close(True)
Else
wrk.Close(False)
End If
Catch ex As Exception
'wrk.Close(False)
End Try

System.Runtime.InteropServices.Marshal.ReleaseComObject(wrk)
wrk = Nothing

app.Quit()
System.Runtime.InteropServices.Marshal.ReleaseComObject(app)
app = Nothing
End Sub

Function isFileMatchingApp(ByVal fileInf As IO.FileInfo) As Boolean Implements VbabyApp.isFileMatchingApp
Dim isMatch As Boolean

isMatch = supportedFileType.ContainsKey(fileInf.Extension)

Return isMatch
End Function

Function getVbProject() As VBProject Implements VbabyApp.getVbProject
Dim pr As VBProject = Nothing
Try
pr = wrk.VBProject
Catch ex As Exception
pr = Nothing
End Try

Return pr
End Function

Function hasVbProject() As Boolean Implements VbabyApp.hasVbProject
Dim hasPr As Boolean

hasPr = wrk.HasVBProject

Return hasPr
End Function

Public Sub New()
initFileTypeCollection()
End Sub

Function initMsApplication() As Boolean Implements VbabyApp.initMsApplication
Try
app = CreateObject(appName)
app.EnableEvents = False
Catch
If Not IsNothing(app) Then
System.Runtime.InteropServices.Marshal.ReleaseComObject(app)
app = Nothing
End If
End Try

Return IsNothing(app)
End Function

Private Sub initFileTypeCollection()
supportedFileType = New Hashtable()
supportedFileType.Add(".xlsm", ".xlsm")
supportedFileType.Add(".xlsb", ".xlsb")
supportedFileType.Add(".xlam", ".xlam")
supportedFileType.Add(".xla", ".xla")
supportedFileType.Add(".xls", ".xls")
End Sub

Private supportedFileType As Hashtable
Private app As Object
Private wrk As Object
Private Const appName As String = "Excel.Application"
End Class

VbabyAccess

A class implementing the interface VBabyApp representing Microsoft Access.

Imports Microsoft.Vbe.Interop
Imports System.Collections

Public Class VbabyAccess
Implements VbabyApp

Function isVBAccessible() As Boolean Implements VbabyApp.isVBAccessible
Dim isAccessible As Boolean
Dim pro As VBProject

pro = getVbProject()
isAccessible = Not IsNothing(pro)

If isAccessible Then
System.Runtime.InteropServices.Marshal.ReleaseComObject(pro)
pro = Nothing
End If

Return isAccessible
End Function

Function importForm(ByVal srcPath As String, ByVal vbCompName As String) As Boolean Implements VbabyApp.importForm
Dim ret As Boolean

Try
app.LoadFromText(vbAcForm, vbCompName, srcPath)
ret = True
Catch
ret = False
End Try

Return ret
End Function

Function exportForm(ByVal trgPath As String, ByRef vbComp As VBComponent) As Boolean Implements VbabyApp.exportForm
Dim ret As Boolean

Try
app.SaveAsText(vbAcForm, Replace(vbComp.Name, "Form_", ""), trgPath)
ret = True
Catch
ret = False
End Try

Return ret
End Function

Function getAppName() As String Implements VbabyApp.getAppName
Return appName
End Function

Function openFile(ByVal fileInf As IO.FileInfo) As Boolean Implements VbabyApp.openFile
Dim isOpen As Boolean

app.OpenCurrentDatabase(fileInf.FullName)
isOpen = True

Return isOpen
End Function

Sub exitApp(ByVal saveChanges As Boolean) Implements VbabyApp.exitApp
If saveChanges Then
app.Quit(vbAcQuitSaveAll)
Else
' Quitting the application without saving anything.
app.Quit(vbAcQuitSaveNone)
End If
System.Runtime.InteropServices.Marshal.ReleaseComObject(app)
app = Nothing
End Sub

Function isFileMatchingApp(ByVal fileInf As IO.FileInfo) As Boolean Implements VbabyApp.isFileMatchingApp
Dim isMatch As Boolean

isMatch = supportedFileType.ContainsKey(fileInf.Extension)

Return isMatch
End Function

Function getVbProject() As VBProject Implements VbabyApp.getVbProject
Dim pr As VBProject = Nothing

pr = app.VBE.ActiveVBProject

Return pr
End Function

Function hasVbProject() As Boolean Implements VbabyApp.hasVbProject
Dim hasPr As Boolean

hasPr = app.VBE.VBProjects.Count > 0

Return hasPr
End Function

Public Sub New()
initFileTypeCollection()
End Sub

Function initMsApplication() As Boolean Implements VbabyApp.initMsApplication
Try
app = CreateObject(appName)
Catch
If Not IsNothing(app) Then
System.Runtime.InteropServices.Marshal.ReleaseComObject(app)
app = Nothing
End If
End Try

Return IsNothing(app)
End Function

Private Sub initFileTypeCollection()
supportedFileType = New Hashtable()
supportedFileType.Add(".accdb", ".accdb")
supportedFileType.Add(".mdb", ".mdb")
End Sub

Private supportedFileType As Hashtable
Private app As Object
Private Const vbAcQuitSaveAll As Integer = 1
Private Const vbAcQuitSaveNone As Integer = 2
Private Const vbAcForm As Integer = 2
Private Const appName As String = "Access.Application"
End Class

VbaHandler

This is where the magic happens. A class for managing the applications VBProject and VBComponents. This class uses the VBabyApp implementations VbabyExcel and VbabyAccess.

Imports Microsoft.Vbe.Interop
Imports System.Runtime.InteropServices
Imports System.IO
Imports System.Text
Imports System.Collections

Public Class VbaHandler
Implements IDisposable
''' <summary>
''' @Author - Alexander Bolte
''' Bolte Consulting
''' A class for exporting VBA source code from Excel files.
'''
''' Supported file formats are all files, which contain VBA source code
''' and can be opened by an Excel version installed on an executing
''' client machine.
''' This is due to the application only automating installed Excel
''' in the background.
'''
''' E.g. Excel 2007 and later will support .xls, .xla, .xlam, .xlsm
''' Excel 2003 of course will NOT support .xlam, .xlsm
'''
''' You get it...
''' </summary>
''' <remarks>Enjoy</remarks>

Public Function isVbAccessible() As Boolean
Dim isAccessible As Boolean

isAccessible = app.isVBAccessible

Return isAccessible
End Function

''' <summary>
''' Returning a collection of visual basic components
''' available in a visual basic project of a currently referenced file.
''' </summary>
''' <returns>A collection of type ArrayList holding Strings,
''' which represent the names of the vb modules,
''' classes and forms in a vb project.</returns>
''' <remarks>If programmatic access to VBproject is not allowed in
''' corresponding application Nothing will be returned.</remarks>
Public Function getAvailableComponents() As ArrayList
Dim comps As New ArrayList()
Dim vbProj As VBProject = Nothing
Dim vbComps As VBComponents = Nothing
Dim vbComp As VBComponent = Nothing

Try
If app.hasVbProject Then
vbProj = app.getVbProject
vbComps = vbProj.VBComponents
For Each vbComp In vbComps
comps.Add(vbComp.Name)
Next
End If
Catch ex As Exception
comps = Nothing
Finally
releaseObject(vbComp)
releaseObject(vbComps)
releaseObject(vbProj)
End Try

Return comps
End Function

''' <summary>
''' Resets lists, which are globally available for logging purposes.
''' The reset lists are
''' - processedFiles
''' - failedFiles
''' </summary>
''' <remarks></remarks>
Private Sub resetLists()
processedFiles = New ArrayList
failedFiles = New ArrayList
End Sub

''' <summary>
''' Importing the source code from before exported text files
''' according to the file extension of each imported text file.
'''
''' The function walks through all files in provided directory
''' and tries to match their file extension to a vbComponent type in a VBA Project.
'''
''' If a file can be matched to a vbComponent type, the source code is imported.
'''
''' If a vbComponent is not yet existing in the target VBA Project,
''' it will be created under the name of the file, which contains the corresponding source code.
''' </summary>
''' <param name="srcPath">The source directory the text files, you want to import are stored in.</param>
''' <returns>True, if the import succeeded. False, if the import of one of the source files failed.</returns>
''' <remarks>
''' VBA Forms are only partly supported as the tool
''' can only handle the source code, not a forms controls.
''' </remarks>
Public Function importSourceCode(ByVal srcPath As String) As Boolean
Dim srcDir As New IO.DirectoryInfo(srcPath)
Dim files As IO.FileInfo()
Dim fileInf As IO.FileInfo
Dim fileName As String
Dim ret As Boolean = True

' TODO:
' What if a vbComponent is not existing yet?
' What if a vbComponent has been deleted?
resetLists()
files = srcDir.GetFiles()
For Each fileInf In files
fileName = fileInf.Name
fileName = Replace(fileName, fileInf.Extension, "")

' Import the source code into CodeModule
If getCompTypeBasedOnFileExtension(fileInf.Extension) <> 0 Then
If fileInf.Extension <> cFileExtFrm Then
ret = ret And importVbaModuleOrClass(fileName, fileInf.FullName)
Else
ret = ret And app.importForm(fileInf.FullName, Replace(fileName, "Form_", ""))
If ret Then
processedFiles.Add(fileName)
Else
failedFiles.Add(fileName)
End If
End If
End If
Next fileInf

fileInf = Nothing
files = Nothing
srcDir = Nothing

Return ret
End Function

''' <summary>
''' Deleting a visual basic component from currently referenced VBProject.
''' </summary>
''' <param name="vbCompName">A string giving the name of the VbComponent to be deleted.</param>
''' <returns>True, if VBComponent was exists and could be deleted.</returns>
''' <remarks>Programmatic access to VBProject has to be allowed.</remarks>
Function deleteVbComponent(ByVal vbCompName As String) As Boolean
Dim ret As Boolean
Dim comps As VBComponents = Nothing
Dim vbProj As VBProject = Nothing

If isVbComponentExisting(vbCompName) Then
vbProj = app.getVbProject
Try
comps = vbProj.VBComponents
comps.Remove(comps.Item(vbCompName))
ret = True
Catch ex As Exception
ret = False
End Try

releaseObject(comps)
comps = Nothing
releaseObject(vbProj)
vbProj = Nothing
End If

Return ret
End Function

''' <summary>
''' Checking, if a VBComponent is existing under given name.
''' </summary>
''' <param name="vbCompName">Name of the VBComponent to check existance for.</param>
''' <returns>True, if the component exists, else false.</returns>
''' <remarks></remarks>
Function isVbComponentExisting(ByVal vbCompName As String) As Boolean
Dim ret As Boolean
Dim aComp As VBComponent = Nothing
Dim comps As VBComponents = Nothing
Dim vbProj As VBProject = Nothing

vbProj = app.getVbProject
Try
comps = vbProj.VBComponents
aComp = comps.Item(vbCompName)
ret = True
Catch
ret = False
End Try

releaseObject(aComp)
aComp = Nothing
releaseObject(comps)
comps = Nothing
releaseObject(vbProj)
vbProj = Nothing

Return ret
End Function

''' <summary>
''' Saves the changes applied in a target binary file currently referenced.
''' </summary>
''' <remarks></remarks>
Public Sub commit()
app.exitApp(True)
End Sub

''' <summary>
''' Releases a COM object.
''' After an object has been released, it must not and cannot be used anymore.
'''
''' Handing an object byRef would cause a COM Exception as the function
''' would try to indirectly return an already released object.
''' </summary>
''' <param name="anObject">A COM object, which should be released by System.Marshal.</param>
''' <remarks>It is not possible to hand an object ByRef for this
''' kind of operation as this would imply a return of the handed object.</remarks>
Private Sub releaseObject(ByVal anObject As Object)
If Not IsNothing(anObject) Then
Marshal.ReleaseComObject(anObject)
anObject = Nothing
End If
End Sub

''' <summary>
''' Deletes the source code from a given vbComponents
''' CodeModule and replaces the source code with given String.
''' </summary>
''' <param name="vbCompName">A target name of a VBComponent to be replaced in target project.</param>
''' <param name="src">A file path to a before exported VBComponent.</param>
''' <returns>True, if source file has been imported successfully, else false.</returns>
''' <remarks>Programmatic access to project has to be granted by corresponding application.</remarks>
Public Function importVbaModuleOrClass(
ByVal vbCompName As String,
ByVal srcFilePath As String
) As Boolean
Dim pro As VBProject = Nothing
Dim vbComp As VBComponent = Nothing
Dim vbComps As VBComponents = Nothing
Dim ret As Boolean

Try
deleteVbComponent(vbCompName)
pro = app.getVbProject
vbComps = pro.VBComponents
vbComp = vbComps.Import(srcFilePath)
ret = True
processedFiles.Add(vbComp.Name)
Catch ex As Exception
failedFiles.Add(vbCompName)
Finally
releaseObject(pro)
pro = Nothing
releaseObject(vbComps)
vbComps = Nothing
releaseObject(vbComp)
vbComp = Nothing
End Try

Return ret
End Function

''' <summary>
''' Creates a vbComponent under given name according to given vbComponent Type.
''' </summary>
''' <param name="compName">A desired name of the target VBComponent.</param>
''' <param name="vbCompType">
''' An integer from enumeration 'vbext_ComponentType'.
''' The tool supports forms, modules, classes and documents.
''' </param>
''' <returns>A newly created vbComponent in currently referenced application.</returns>
''' <remarks></remarks>
Private Function createVbComponent( _
ByVal compName As String, _
ByVal vbCompType As Integer _
) As VBComponent

Dim vbProj As VBProject = Nothing
Dim vbComps As VBComponents = Nothing
Dim newComp As VBComponent = Nothing

Try
vbProj = app.getVbProject
vbComps = vbProj.VBComponents
newComp = vbComps.Add(vbCompType)
newComp.Name = compName
Catch ex As Exception
releaseObject(newComp)
newComp = Nothing
Finally
releaseObject(vbComps)
vbComps = Nothing
releaseObject(vbProj)
vbProj = Nothing
End Try

Return newComp
End Function

''' <summary>
''' Returning a collection holding the names of all successfully processed VBComponents.
''' </summary>
''' <returns>An ArrayList holding the names of all successfully processed VBComponents.</returns>
''' <remarks></remarks>
Public ReadOnly Property processedModuleList As ArrayList
Get
Return processedFiles
End Get
End Property

''' <summary>
''' Returning an ArrayList holding the names of all VBComponents, which could not be processed in before applied / called function.
''' </summary>
''' <returns>
''' All names of VBComponents, which could not be processed.
''' If nothing was processed before an empty collection is returned.
''' </returns>
''' <remarks></remarks>
Public ReadOnly Property failedModuleList As ArrayList
Get
Return failedFiles
End Get
End Property

''' <summary>
''' Creates a new instantiation of this class
''' and sets an internal object of class MyXlHandler
''' to reference an Excel file available under given file path.
'''
''' If the Excel file is not opened yet it will be opened and then referenced by this class.
''' If the workbook is already opened, it will not be opened again, but only referenced.
''' </summary>
''' <param name="fileInf">A String pointing to a file.</param>
''' <remarks></remarks>
Public Sub New(ByVal fileInf As IO.FileInfo)
app = New VbabyAccess
If Not app.isFileMatchingApp(fileInf) Then
app = Nothing
End If

If IsNothing(app) Then
app = New VbabyExcel
End If

If Not IsNothing(app) Then
app.initMsApplication()
app.openFile(fileInf)
End If

resetLists()
End Sub

''' <summary>
''' Returns a file ending corresponding a provided VBComponents type attribute.
''' </summary>
''' <param name="vbaComp">An instantiated object of Excel.VBA class VbComponent.</param>
''' <returns>
''' A String representing the type of a handed VBComponent.
''' If a VBA module is handed, ".mod" is returned.
''' If a VBA class is handed, ".cls" is returned.
''' If a VBA form is handed, ".frm" is returned.
''' </returns>
''' <remarks></remarks>
Private Function getFileExtensionBasedOnModuleType(ByRef vbaComp As VBComponent) As String
Dim fileExt As String = ""

If vbaComp.Type = vbext_ComponentType.vbext_ct_StdModule Then
fileExt = cFileExtMod
ElseIf vbaComp.Type = vbext_ComponentType.vbext_ct_ClassModule Then
fileExt = cFileExtCls
ElseIf vbaComp.Type = vbext_ComponentType.vbext_ct_MSForm Then
fileExt = cFileExtFrm
ElseIf vbaComp.Type = vbext_ComponentType.vbext_ct_Document Then
If app.getAppName = "Access.Application" Then
fileExt = cFileExtFrm
Else
fileExt = cFileExtDoc
End If
End If

Return fileExt
End Function

''' <summary>
''' Returning a valid component type from the 'vbext_ComponentType' Enumaration based on a provided file extension.
''' </summary>
''' <param name="ext">A file extension of a file exported from a VBProject.</param>
''' <returns>Component type from the 'vbext_ComponentType' Enumaration matching provided file extension.</returns>
''' <remarks></remarks>
Private Function getCompTypeBasedOnFileExtension(ByVal ext As String) As Integer
Dim modType As Integer = 0

Select Case ext
Case cFileExtCls
modType = vbext_ComponentType.vbext_ct_ClassModule
Case cFileExtFrm
modType = vbext_ComponentType.vbext_ct_MSForm
Case cFileExtMod
modType = vbext_ComponentType.vbext_ct_StdModule
Case cFileExtDoc
modType = vbext_ComponentType.vbext_ct_Document
End Select

Return modType
End Function

''' <summary>
''' Getting a VBComponent, if existing under given name.
''' </summary>
''' <param name="vbName">A String holding a name of a VBComponent, which is existing in currently referenced VBProject.</param>
''' <returns>A VBComponent, if identified under given name. Else Nothing.</returns>
''' <remarks></remarks>
Private Function getVbComponentByName(ByVal vbName As String) As VBComponent
Dim ret As VBComponent = Nothing
Dim comps As VBComponents = Nothing
Dim vbProj As VBProject = Nothing

vbProj = app.getVbProject
Try
comps = vbProj.VBComponents
ret = comps.Item(vbName)
Catch ex As Exception
ret = Nothing
End Try

releaseObject(comps)
comps = Nothing
releaseObject(vbProj)
vbProj = Nothing

Return ret
End Function

''' <summary>
''' Extracts the source code from each VbComponent available in the referenced Excel file (.xlam, .xlsm, .xls, .xla).
''' The content of each vbComponent is written into a text file in target path directory.
''' </summary>
''' <param name="path">The target path where the text files (one per VbComponent) should be saved to.</param>
''' <remarks></remarks>
Public Sub exportVbaModules(ByVal path As String)
Dim src As String
Dim vbComponent As VBComponent
Dim vbModule As CodeModule
Dim vbProj As VBProject

resetLists()
If app.hasVbProject Then
vbProj = app.getVbProject
' Walk through all available vbComponents in VBA project.
For Each vbComponent In vbProj.VBComponents()
' Reference a vbComponents code module.
vbModule = getCodeModule(vbComponent)
If Not (vbModule Is Nothing) Then
src = getSourceCode(vbModule)
If src <> "" Then
If getFileExtensionBasedOnModuleType(vbComponent) <> cFileExtFrm Then
vbComponent.Export(path & "\" & vbModule.Name &
getFileExtensionBasedOnModuleType(vbComponent))
Else
app.exportForm(path & "\" & vbModule.Name &
getFileExtensionBasedOnModuleType(vbComponent), vbComponent)
End If
processedFiles.Add(vbComponent.Name)
Else
failedFiles.Add(vbComponent.Name)
End If
Else
failedFiles.Add(vbComponent.Name)
End If

' Free all resources.
releaseObject(vbModule)
vbModule = Nothing
releaseObject(vbComponent)
vbComponent = Nothing
Next vbComponent
End If
End Sub

''' <summary>
''' Writing the provided String into a text file,
''' which will be created in given directory.
'''
''' If the file already exists, it will be deleted
''' and recreated using the contents provided in a provided String.
''' </summary>
''' <param name="src">
''' A String representing the source code in a vbCompnent.
''' </param>
''' <param name="filePath">
''' A String representing a file path
''' to an existing or new file, which should be created.
''' </param>
''' <returns>
''' True, if writing the contents into a file succeeded.
''' False, if any exception occurs during attempt of writing a file.
''' </returns>
''' <remarks></remarks>
Private Function writeTextToFile(ByVal src As String, ByVal filePath As String) As Boolean
Dim out As StreamWriter = Nothing
Dim ret As Boolean

If System.IO.File.Exists(filePath) Then
System.IO.File.Delete(filePath)
End If
Try
out = File.CreateText(filePath)
out.Write(src)
ret = True
Catch
ret = False
Finally
If Not (out Is Nothing) Then
out.Flush()
out.Close()
out = Nothing
End If
End Try

Return ret
End Function

''' <summary>
''' Getting the lines of code from a provided module as one String.
''' </summary>
''' <param name="vbModule">A VbComponent.CodeModule object representing the VBA source code.</param>
''' <returns>A String containing the source code from provided CodeModule.</returns>
''' <remarks>
''' Not tested yet for very big modules
''' with thousands of lines of source code and comments.
''' </remarks>
Private Function getSourceCode(ByRef vbModule As CodeModule) As String
Dim src As String = ""

Try
src = vbModule.Lines(1, vbModule.CountOfLines)
Catch
End Try

Return src
End Function

''' <summary>
''' Returning the CodeModule as a representation of VBA source code from a provided vbComponent object.
''' </summary>
''' <param name="vbComponent">An initialized VbComponent object.</param>
''' <returns>An object of type CodeModule.</returns>
''' <remarks>
''' Had to be wrapped in extra function, since an exception is thrown,
''' if the vbComponent does not have any source code.
''' </remarks>
Private Function getCodeModule(ByRef vbComponent As Object) As CodeModule
Dim vbModule As CodeModule = Nothing

Try
vbModule = vbComponent.CodeModule
Catch ex As Exception
vbModule = Nothing
End Try

Return vbModule
End Function

''' <summary>
''' A referenced Microsoft Application like Excel is started in the
''' background as a separate process / instance
''' and not visible for the user.
'''
''' Then the source file is loaded
''' and several other objects are referenced from the Applications VBA API.
'''
''' In order to completely close a referenced application all referenced objects
''' have to be released before setting them to Null using a calss called System.Runtime.Marshal.
'''
''' However, if an obejct is released twice, an exception is thrown.
'''
''' On top it is dangerous to call objects through objects,
''' without pointing to each object with variables in an object / API path.
'''
''' Calling this function will do the job for us,
''' since the applications implementation of the interface VBabyApp
''' is wrapping most of an applications automation.
''' </summary>
''' <remarks></remarks>
Private Sub close()
app.exitApp(True)
app = Nothing
End Sub

#Region "IDisposable Support"
Private disposedValue As Boolean ' To detect redundant calls

' IDisposable
Protected Overridable Sub Dispose(disposing As Boolean)
If Not Me.disposedValue Then
If disposing Then
Try
' Make sure to always close the VbaHandler properly.
' This will also call the functions to close the object of type MyXlHandler
Call close()
' Enforce garbadge collection.
System.GC.Collect()
Catch
Finally
End Try
End If

' TODO: free unmanaged resources (unmanaged objects) and override Finalize() below.
' TODO: set large fields to null.
End If
Me.disposedValue = True
End Sub

''' <summary>
''' This method was automatically generated when implementing the IDisposable interface of VB.NET.
''' It emphasises closing a class / application in a clean state to help successful garbade collection.
''' This is especially important when automating Excel in the background.
''' </summary>
''' <remarks></remarks>
Public Sub Dispose() Implements IDisposable.Dispose
' Do not change this code. Put cleanup code in Dispose(disposing As Boolean) above.
Dispose(True)
GC.SuppressFinalize(Me)
End Sub
#End Region

Private app As VbabyApp

Private cFileExtMod As String = ".bas"
Private cFileExtCls As String = ".cls"
Private cFileExtFrm As String = ".frm"
Private cFileExtDoc As String = ".xld"

Private processedFiles As ArrayList
Private failedFiles As ArrayList

Private Const appXl As String = "MsExcel"
Private Const appAc As String = "MsAccess"
End Class 

MyTextImport

A little class for importing text files in VB.NET.

 Imports System.Xml
Imports System.IO
Imports System.Text
Imports System.Collections
Imports System.Data

Public Class MyTextImport
Private myFilePath As String
Private myStreamIn As StreamReader
Private myStringBuilder As StringBuilder
Private myLineList As ArrayList
Private fileWasFound As Boolean

''' <summary>
''' Indicating that the specified file was found.
''' </summary>
''' <returns>False, if a file specified in one of the called functions or provided in constructor was not found. True otherwise.</returns>
''' <remarks></remarks>
Public ReadOnly Property fileExists As Boolean
Get
fileExists = fileWasFound
End Get
End Property

Public Sub New(ByVal filePath As String)
myFilePath = filePath
Try
myStreamIn = New StreamReader(myFilePath)
Catch ex As System.IO.FileNotFoundException
fileWasFound = False
End Try
myStringBuilder = New StringBuilder
myLineList = New ArrayList
End Sub

Public Sub New()
myStringBuilder = New StringBuilder
myLineList = New ArrayList
End Sub

Public Function readFileToString() As String
Dim sLine As String = ""
Dim ret As String = ""

If myStreamIn IsNot Nothing Then
Do
sLine = myStreamIn.ReadLine
ret = ret & sLine & vbCrLf
Loop Until (myStreamIn.EndOfStream)
myStreamIn.Close()
myStreamIn = Nothing
End If

Return ret
End Function

Public Function readFileToArrayList() As ArrayList
Dim sLine As String

If myStreamIn IsNot Nothing Then
Do
sLine = myStreamIn.ReadLine
If Not sLine Is Nothing Then
myLineList.Add(sLine)
End If
Loop Until (sLine Is Nothing)

myStreamIn.Close()
myStreamIn = Nothing
End If

Return myLineList
End Function

Public Function getLineList() As ArrayList
getLineList = myLineList
End Function

Public Function readXML(ByVal filePath As String) As XmlDocument
Dim myDoc As XmlDocument = Nothing

myDoc = New XmlDocument
myDoc.Load(filePath)

Return myDoc
End Function

''' <summary>
''' Searches for a XML file available under provided file path and returns a DataSet containing the information in a DataTable available at index (0).
''' </summary>
''' <param name="filePath">A full file path including file name and file type.</param>
''' <returns>A DataSet with </returns>
''' <remarks>In case the provided file path is not referencing an XML file this function will fail.</remarks>
Public Function readXMLToDataSet(ByVal filePath As String) As DataSet
Dim myData As New DataSet

Try
If FileIO.FileSystem.FileExists(filePath) Then
myData.ReadXml(filePath)
fileWasFound = True
Else
fileWasFound = False
myData = Nothing
End If
Catch
' Anything can happen during read attempt on flat file.
fileWasFound = False
myData = Nothing
End Try

Return myData
End Function
End Class

FileFunctions 

A little module for providing static functions for file operations like getting a folder path from a user in VB.NET.

Imports System.Windows.Forms
Imports System.Collections

Module FileFunctions

Public Const cTextFilesFilter As String = "TXT files|*.txt;*.csv"
Public Const cAllFilesFilter As String = "All files|*.*"
Public Const cXlFilesFilter As String = "Excel files|*.xls;*.xlsx;*.xlsb;*.xlsm"
Public Const cXmlFilesFilter As String = "XML files|*.xml"
Public Const cInitialDirectory As String = "C:\"

' ### prompts a file picker dialog to a user gets a single file path to a file picked by a user ###
' returns an empty string, if user aborts
Public Function getSingleFilePath( _
Optional ByVal myTitle As String = "Select File", _
Optional ByVal initDirectory As String = cInitialDirectory, _
Optional ByVal filterStr As String = cAllFilesFilter _
) As String
Dim ret As String
Dim fPick As OpenFileDialog = New OpenFileDialog

fPick.Title = myTitle
fPick.InitialDirectory = initDirectory
'fPick.RestoreDirectory = True
fPick.Filter = filterStr

If fPick.ShowDialog = DialogResult.OK Then
ret = fPick.FileName
Else
ret = ""
End If

Return ret
End Function

Public Function getFileNameListFromDirectory(ByVal srcPath As String) As ArrayList
Dim fileList As New ArrayList
Dim srcDir As New IO.DirectoryInfo(srcPath)
Dim files As IO.FileInfo()
Dim fileInf As IO.FileInfo

files = srcDir.GetFiles()
For Each fileInf In files
fileList.Add(fileInf.Name)
Next

fileInf = Nothing
files = Nothing
srcDir = Nothing

Return fileList
End Function

Public Function getSingleFolderPath( _
Optional ByVal myTitle As String = "Select Folder", _
Optional ByVal initDirectory As Integer = Environment.SpecialFolder.MyComputer _
) As String
Dim ret As String
Dim fPick As FolderBrowserDialog = New FolderBrowserDialog

fPick.RootFolder = initDirectory
If fPick.ShowDialog = DialogResult.OK Then
ret = fPick.SelectedPath
Else
ret = ""
End If

Return ret
End Function

Public Function createFolder(ByVal fullPath As String) As Boolean
Dim ret As Boolean

Try
FileIO.FileSystem.CreateDirectory(fullPath)
ret = True
Catch ex As SystemException
ret = False
End Try

Return ret
End Function

''' <summary>
''' Checks, if a given directory (folder not file) does exist.
''' </summary>
''' <param name="directoryPath">Full directory / folder path, which should be searched.</param>
''' <returns>True, if directory exists, false otherwise.</returns>
''' <remarks></remarks>
Public Function folderExists(ByVal directoryPath As String) As Boolean
Dim ret As Boolean
Dim splitted() As String
Dim directories() As String
Dim i As Integer
Dim j As Integer
Dim subPath As String = ""

'ret = FileIO.FileSystem.DirectoryExists(directoryPath)
' FileSystem.DirectoryExists is not working properly on Adidas machines.
' Adidas laptops show a very restrictive setup with limited write access on certain drives.
' Directory.Exists tourned out to be more reliable.
'ret = System.IO.Directory.Exists(directoryPath)

' Split given path in order to walk through each directory and check existance.
splitted = Split(directoryPath, "\")
ret = True
' Let's see, if we can find next directory in path.
For i = LBound(splitted) To UBound(splitted) - 1
' If sub directory was found.
If ret Then
' Build a sub path string.
subPath = subPath & splitted(i) & "\"
' Get all directories available in sub path.
Try
directories = System.IO.Directory.GetDirectories(subPath)
Catch ex As System.IO.DirectoryNotFoundException
directories = Nothing
ret = False
End Try

If directories IsNot Nothing Then
' Check, if the next derectory is available in gotten sub directories.
For j = LBound(directories) To UBound(directories)
If directories(j) = subPath & splitted(i + 1) Then
ret = True
Exit For
Else
ret = False
End If
Next j
End If
Else
' One of the sub directories was not found.
Exit For
End If
Next i

Return ret
End Function
End Module

VBabyMain

A GUI class putting evrything together. The GUI class as displayed here is missing the source code created by the Visual Studio Form Designer.

Imports System.Collections

Public Class VBabyMain
Private Sub enableAllMenuItemControls()
SelectTargetFileToolStripMenuItem.Enabled = True
SelectTargetDirectoryToolStripMenuItem.Enabled = True
End Sub

Private Sub VBabyMain_FormClosing(sender As Object, e As Windows.Forms.FormClosingEventArgs) Handles Me.FormClosing
Me.Dispose()
End Sub

Private Sub resetSrcParams()
srcPath = ""
labelSourcePath.Text = "Source"
End Sub

Private Sub ReferenceCodeFileToolStripMenuItem_Click(sender As Object, e As EventArgs) Handles ReferenceCodeFileToolStripMenuItem.Click
Dim vbHandle As VbaHandler = Nothing
Dim availComps As ArrayList
Dim aComp As String
Dim fileInf As IO.FileInfo

resetSrcParams()
Me.srcComponentPreview.Items.Clear()
SelectTargetFileToolStripMenuItem.Enabled = False
SelectTargetDirectoryToolStripMenuItem.Enabled = True

If SourceFileBrowser.ShowDialog = Windows.Forms.DialogResult.OK Then
srcPath = SourceFileBrowser.FileName
labelSourcePath.Text = srcPath
fileInf = New IO.FileInfo(srcPath)
vbHandle = New VbaHandler(fileInf)

If vbHandle.isVbAccessible Then
availComps = vbHandle.getAvailableComponents()
For Each aComp In availComps
Me.srcComponentPreview.Items.Add(aComp)
Next
Else
MsgBox("The VbProject is not accessible." & vbCrLf & _
"Go to settings of MS application matching source file and allow programmatic access to VBProject.", _
MsgBoxStyle.Exclamation, _
"VBaaaaaabyyyy :0(")
resetSrcParams()
enableAllMenuItemControls()
End If

vbHandle.Dispose()
vbHandle = Nothing
Else
enableAllMenuItemControls()
MsgBox("File selection aborted.", vbInformation, "V ... v ... baby? :0(")
End If
End Sub

Private Sub SelectTargetDirectoryToolStripMenuItem_Click(sender As Object, e As EventArgs) Handles SelectTargetDirectoryToolStripMenuItem.Click
Dim vbHandle As VbaHandler = Nothing
Dim succeeded As ArrayList
Dim failed As ArrayList
Dim anItem As String
Dim fileInf As IO.FileInfo

resetTrgParams()

If srcPath <> "" Then
If TargetFolderBrowser.ShowDialog() = Windows.Forms.DialogResult.OK Then
trgPath = TargetFolderBrowser.SelectedPath
labelTargetPath.Text = trgPath

fileInf = New IO.FileInfo(srcPath)
vbHandle = New VbaHandler(fileinf)
vbHandle.exportVbaModules(trgPath)
succeeded = vbHandle.processedModuleList
For Each anItem In succeeded
trgLog.Items.Add("Exported: " & anItem)
Next
succeeded = Nothing

failed = vbHandle.failedModuleList
For Each anItem In failed
trgLog.Items.Add("Failed: " & anItem)
Next
failed = Nothing

''' Call close in order to dispose of objects used for export properly.
''' Not calling close on this object
''' will cause Excel to stay open as background process.
vbHandle.Dispose()
MsgBox("Fnished exporting vba modules.", MsgBoxStyle.Information, "Baby VBABY! :0P")
Else
MsgBox("Export aborted.", vbInformation, "V ... v ... baby? :0(")
End If
Else
MsgBox("Select a source file first.", vbInformation, "v ... V ... baby? :0{")
End If
End Sub

Private Sub SelectTargetFileToolStripMenuItem_Click(sender As Object, e As EventArgs) Handles SelectTargetFileToolStripMenuItem.Click
Dim vbHandle As VbaHandler
Dim succeeded As ArrayList
Dim failed As ArrayList
Dim anItem As String
Dim fileinf As IO.FileInfo

resetTrgParams()

trgLog.Items.Clear()
If srcPath <> "" Then
If SourceFileBrowser.ShowDialog = Windows.Forms.DialogResult.OK Then
trgPath = SourceFileBrowser.FileName
labelTargetPath.Text = trgPath

fileInf = New IO.FileInfo(trgPath)
vbHandle = New VbaHandler(fileinf)
If vbHandle.isVbAccessible Then
If vbHandle.importSourceCode(srcPath) Then
' Save the changes.
vbHandle.commit()
MsgBox("Changes applied.", vbInformation, "VBaby baby! :0)")
Else
MsgBox("Failed update.", vbExclamation, "V ... :0(")
End If
succeeded = vbHandle.processedModuleList
For Each anItem In succeeded
trgLog.Items.Add("Imported: " & anItem)
Next
succeeded = Nothing

failed = vbHandle.failedModuleList
For Each anItem In failed
trgLog.Items.Add("Failed: " & anItem)
Next
failed = Nothing
Else
MsgBox("The VbProject is not accessible." & vbCrLf & _
"Go to settings of MS application matching source file and allow programmatic access to VBProject.", _
MsgBoxStyle.Exclamation, _
"VBaaaaaabyyyy :0(")
resetTrgParams()
End If
vbHandle.Dispose()
vbHandle = Nothing
End If
Else
MsgBox("No source directory referenced.", vbExclamation, "VBaby :0{")
End If
End Sub

Private Sub resetTrgParams()
trgPath = ""
labelTargetPath.Text = "Target"
End Sub

Private Sub ReferenceDirectoryToolStripMenuItem_Click(sender As Object, e As EventArgs) Handles ReferenceDirectoryToolStripMenuItem.Click
Dim fileList As ArrayList
Dim fileName As String

resetSrcParams()
SelectTargetDirectoryToolStripMenuItem.Enabled = False
SelectTargetFileToolStripMenuItem.Enabled = True

Me.srcComponentPreview.Items.Clear()
If TargetFolderBrowser.ShowDialog() = Windows.Forms.DialogResult.OK Then
srcPath = TargetFolderBrowser.SelectedPath
labelSourcePath.Text = srcPath

fileList = getFileNameListFromDirectory(srcPath)
For Each fileName In fileList
Me.srcComponentPreview.Items.Add(fileName)
Next
fileList = Nothing
Else
MsgBox("Setting reference aborted.", vbExclamation, "VBaby")
enableAllMenuItemControls()
End If
End Sub

Private Sub AboutToolStripMenuItem_Click(sender As Object, e As EventArgs) Handles AboutToolStripMenuItem.Click
MsgBox( _
"VBaby" & vbCrLf & "Version 1.2.0" & vbCrLf & _
"A tool for exporting and importing VBA source code text files" & _
vbCrLf & "to support usage of versioning for VBA coding." & _
vbCrLf & vbCrLf & "Author - Alexander Bolte (Bolte Consulting)", _
MsgBoxStyle.Information, "VBaby" _
)
End Sub

Private srcPath As String
Private trgPath As String
End Class

Unit Testing

Below you will find some unit tests, which I used during developing the tool.

Imports System.Text
Imports Microsoft.VisualStudio.TestTools.UnitTesting
Imports VBaby

<TestClass()> Public Class TestVBaby
Private Sub clearTargetDirectory(ByVal trgDir As String)
Dim fileNames As IReadOnlyCollection(Of String)
Dim i As Integer

fileNames = My.Computer.FileSystem.GetFiles(trgDir)
For i = 0 To fileNames.Count - 1
My.Computer.FileSystem.DeleteFile(fileNames(i), FileIO.UIOption.OnlyErrorDialogs, FileIO.RecycleOption.DeletePermanently)
Next i

fileNames = Nothing
End Sub

<TestMethod()> Public Sub TestExportExcel()
Dim vbHandle As VBaby.VbaHandler
Dim actual As Integer
Const srcFilePath As String = "C:\Users\Alexander\Documents\Deutsche Bank\DB-SoD-Excel-Add-In\bin\DB-SoD-Add-In-1.7.1.xlam"
Const trgDirectoryPath As String = "C:\Users\Alexander\Desktop\Testing"
Const expected As Integer = 18

clearTargetDirectory(trgDirectoryPath)
vbHandle = New VbaHandler(New IO.FileInfo(srcFilePath))
vbHandle.exportVbaModules(trgDirectoryPath)
actual = vbHandle.processedModuleList.Count

vbHandle.Dispose()
vbHandle = Nothing

Assert.AreEqual(expected, actual)
End Sub

<TestMethod()> Public Sub TestExportAccess()
Dim vbHandle As VBaby.VbaHandler
Dim actual As Integer
Const srcFilePath As String = "C:\Users\Alexander\Documents\SAP RFC Tools\Master-Data-Validation-Tool-1.1\Master-Data-Validation-Tool-1.2.mdb"
Const trgDirectoryPath As String = "C:\Users\Alexander\Desktop\Testing"
Const expected As Integer = 8

clearTargetDirectory(trgDirectoryPath)
vbHandle = New VbaHandler(New IO.FileInfo(srcFilePath))
vbHandle.exportVbaModules(trgDirectoryPath)
actual = vbHandle.processedModuleList.Count

vbHandle.Dispose()
vbHandle = Nothing

Assert.AreEqual(expected, actual)
End Sub

<TestMethod()> Public Sub TestImportSourceCodeExcel()
Dim vbHandle As VbaHandler
Const expected As Boolean = True
Dim actual As Boolean
Const trgFilePath As String = "C:\Users\Alexander\Desktop\DB-SoD-Add-In-1.6.23.1.xlam"
Const srcDirPath As String = "C:\Users\Alexander\Desktop\Testing"

vbHandle = New VbaHandler(New IO.FileInfo(trgFilePath))
actual = vbHandle.importSourceCode(srcDirPath)

' Save the changes.
vbHandle.commit()

vbHandle.Dispose()
vbHandle = Nothing

Assert.AreEqual(expected, actual)
End Sub

<TestMethod()> Public Sub TestImportSourceCodeAccess()
Dim vbHandle As VbaHandler
Const expected As Boolean = True
Dim actual As Boolean
Const trgFilePath As String = "C:\Users\Alexander\Desktop\Master-Data-Validation-Tool-1.2.mdb"
Const srcDirPath As String = "C:\Users\Alexander\Desktop\Testing"

vbHandle = New VbaHandler(New IO.FileInfo(trgFilePath))
actual = vbHandle.importSourceCode(srcDirPath)

' Save the changes.
vbHandle.commit()

vbHandle.Dispose()
vbHandle = Nothing

Assert.AreEqual(expected, actual)
End Sub
End Class

Summary

In the end I have to say it was fun coding it, but also annoying.

Why is Microsoft providing an API to handle VBComponents called Microsoft.Vbe.Interop when it is not working the same for all Microsoft applications that support VBA.

Again I had to regard application specific things, which I could not overcome by following my initial design.

The initial design was to have only one class to handle all operations related to VBProjects and VBComponents (the VbaHandler).

Now I ended up with an interface and one class per application I want to support in the tool.

Crap! Microsoft. Crap!