Click here to Skip to main content
15,031,361 members
Articles / DevOps / Git
Tip/Trick
Posted 1 May 2021

Stats

3.9K views
93 downloads
8 bookmarked

Add Excel/Access Project to Source Version Control (Git) Repository

Rate me:
Please Sign up or sign in to vote.
0.00/5 (No votes)
6 May 2021CPOL2 min read
Add Excel/Access Project to Git- Part 1 of 2
It is not an easy task to add VBA project build in Excel/Access to SVN such as Git repository, then share the project between group of developers and truck source version control.

Introduction

Most projects build in VBA using Excel/Access as platform, developers think it is hard to share the project between a group of developers and track the changes made. In the early days, this can be done in MS VSS as repository with Add-Ins library form Microsoft, even TFS has provided the same technology, but limitation of VSS/TFS and comparing with Git as version control system that provides extensive benefits for developers by sharing the project code and tracks the change that has been made between developers, that's why Git is preferable repository of choices and it is free.

Mean issue to tackle is how to export all VBA code such as (modules, classes) and other components such as forms, queries, reports, macros, sheets, etc. as files and upload them to Git system not as binary files but as text file (source file) and easy to track changes as version control in Git repository system.

Same thing when file needs to be uploaded back to the project from text file to original format such as binary file.

Note: This article is not for how to use or install Git, but how to prepare VBA Project and set it ready to be submitted in Git system as repository.

Using the Code

The code provided is build in VBA for both Excel or Access.

It contains a function that will Export/Import all components in Excel project to/from your selected Source Version Control repository folder that was created in Git system.

Please create a new module called "mod_SVN" and copy the code below and past in the module.

To export all file code and odther components, you needs to run "ExportAll" method after providing SVN folder that was created in Git system to store all project file, also you need to provide Excel file project name with its path.

To import all file back to your project from Git repository, you needs to run "ImportAll" method after providing SVN folder that hold all project files in Git system, also you need to provides Excel project file name with its path that hold VBA project, also you needs to provide project name. Note, when importing source code to Excel file, please delete all components in the project or start with new Excel file to be used as working project file.

VB.NET
Public Sub ExportSourceFiles(strXLFilename As String, strSVCfolder As String)
Dim oWrkbook As Workbook
Dim oVBProject As VBProject, oVBComp As VBComponent, obj As Object
Dim strCompname As String, strFileExt As String, strProjFilename As String  
    '-open the selected excel file
    Set oWrkbook = Workbooks.Open(strXLFilename)
    strProjFilename = oWrkbook.VBProject.Name
    Set oVBProject = Application.VBE.VBProjects(strProjFilename)
    For Each oVBComp In oVBProject.VBComponents
        If oVBComp.Type = vbext_ct_ClassModule Or _
        oVBComp.Type = vbext_ct_StdModule Or oVBComp.Type = vbext_ct_MSForm Then
            strCompname = oVBComp.Name
            strFileExt = FileExtenstion(oVBComp.Type)
            oVBComp.Export (strSVCfolder & strCompname & strFileExt)
        End If
    Next
    Call ExportSheets(oWrkbook, strSVCfolder)
    oWrkbook.Close False
getOut:
    Set oWrkbook = Nothing
    Set oVBProject = Nothing
    Exit Sub
errHandler:
    MsgBox Err.Description
    GoTo getOut
    Resume
End Sub

Private Sub ExportSheets(oWrkbook As Workbook, strSaveFolder As String)
Dim oSheet As Worksheet
    Application.DisplayAlerts = False
    For Each oSheet In oWrkbook.Worksheets
        If Not (oSheet.Name = "ThisWorkbook") Then
            oSheet.SaveAs strSaveFolder & oSheet.Name, xlCSV
        End If
    Next
    Application.DisplayAlerts = True
End Sub

Private Function FileExtenstion(oCompType As vbext_ComponentType) As String
    Select Case oCompType
        Case vbext_ComponentType.vbext_ct_ClassModule
            FileExtenstion = ".cls"
        Case vbext_ComponentType.vbext_ct_StdModule
            FileExtenstion = ".bas"
        Case vbext_ComponentType.vbext_ct_MSForm
            FileExtenstion = ".frm"
        Case vbext_ComponentType.vbext_ct_ActiveXDesigner
            FileExtenstion = ".des"
        Case vbext_ComponentType.vbext_ct_Document
            FileExtenstion = ".doc"
        Case Else
            FileExtenstion = vbNullString
    End Select
getOut:
    Exit Function
errHandler:
    MsgBox Err.Description
    GoTo getOut
    Resume
End Function

Public Sub RemoveAllModules(oVBProject As VBProject)
'Dim oVBProject As VBProject
Dim comp As VBComponent
    For Each comp In oVBProject.VBComponents
        If comp.Type = vbext_ct_ClassModule Or _
        comp.Type = vbext_ct_StdModule Or comp.Type = vbext_ct_MSForm Then
            oVBProject.VBComponents.Remove comp
        End If
    Next
getOut:
    Exit Sub
 
errHandler:
    MsgBox Err.Description
    GoTo getOut
    Resume
End Sub

Private Sub RemoveAllSheets(oWrkbook As Workbook, strSaveFolder As String)
Dim oSheet As Worksheet
    Application.DisplayAlerts = False
    For Each oSheet In oWrkbook.Worksheets
        If Not (oSheet.Name = "ThisWorkbook") Then
            oSheet.Delete
        End If
    Next
    Application.DisplayAlerts = True
getOut:
    Exit Sub
errHandler:
    MsgBox Err.Description
    GoTo getOut
    Resume
End Sub

'===============================
Sub ExportAll()
Dim strSVCfolder As String, strFileProjecName As String
    strSVCfolder = "" '?????????????? please provide
    strFileProjecName = "" '?????????????? please provide
    Call ExportSourceFiles(strFileProjecName, strSVCfolder)
    MsgBox "All component exported successfully "
getOut:
    Exit Sub
errHandler:
    MsgBox Err.Description
    GoTo getOut
    Resume
End Sub

Public Sub ImportAllComponents(strXLFilename As String, strSVCfolder As String)
Dim oWrkbook As Workbook
Dim oVBProject As VBProject
Dim strFilename As String, strProjFilename As String
    On Error GoTo errHandler
    Set oWrkbook = Workbooks.Open(strXLFilename)
    strProjFilename = oWrkbook.VBProject.Name
    Set oVBProject = Application.VBE.VBProjects(strProjFilename)
   
    strFilename = Dir(strSVCfolder)
    While (strFilename <> vbNullString)
        If Not (LCase(Right(strFilename, 3)) = "csv" _
                Or LCase(Right(strFilename, 3)) = "frx") Then
             oVBProject.VBComponents.Import strSVCfolder & strFilename
        End If
        strFilename = Dir
    Wend 
getOut:
    Exit Sub
errHandler:
    MsgBox Err.Description
    GoTo getOut
    Resume
End Sub

Private Sub ImportAllSheets(strXLFilename As String, _
        strSVCfolder As String, strProjectName As String)
'Dim oCSVWrkbook As Workbook
Dim oWrkbook As Workbook
Dim vFilename As Variant
Dim strFiletype As String
Dim nIndex As Integer
On Error GoTo errHandler
    'Specify file type
    strFiletype = "*.csv"
    'open the workbook.
    vFilename = Dir(strSVCfolder & strFiletype)
    Set oWrkbook = Application.Workbooks.Open(strXLFilename)
    oWrkbook.Activate
    'Add Target Workbook
    'Set oWrkbook = ActiveWorkbook 'Workbooks.Add
    Application.VBE.VBProjects("VBAProject").Name = strProjectName
    'Loop through each CSV file in folder
    Do While vFilename <> ""
        Worksheets.Add(Before:=Worksheets("Sheet1")).Name = Replace(vFilename, ".csv", "")
        With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & strSVCfolder & vFilename _
                , Destination:=ActiveSheet.Range("$A$1"))
                .Name = vFilename
                .FieldNames = True
                .RowNumbers = False
                .FillAdjacentFormulas = False
                .PreserveFormatting = True
                .RefreshOnFileOpen = False
                .RefreshStyle = xlInsertDeleteCells
                .SavePassword = False
                .SaveData = True
                .AdjustColumnWidth = True
                .RefreshPeriod = 0
                .TextFilePromptOnRefresh = False
                .TextFilePlatform = 850
                .TextFileStartRow = 1
                .TextFileParseType = xlDelimited
                .TextFileTextQualifier = xlTextQualifierDoubleQuote
                .TextFileConsecutiveDelimiter = False
                .TextFileTabDelimiter = False
                .TextFileSemicolonDelimiter = False
                .TextFileCommaDelimiter = True
                .TextFileSpaceDelimiter = False
                .TextFileColumnDataTypes = _
                 Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, )
                .TextFileTrailingMinusNumbers = True
                .Refresh BackgroundQuery:=False
        End With
        nIndex = nIndex + 1
        vFilename = Dir
    Loop
    oWrkbook.SaveAs Filename:=strXLFilename, _
             FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False  
getOut:
    Exit Sub
errHandler:
    MsgBox Err.Description
    GoTo getOut
    Resume
End Sub
 
Sub test()
Dim strSVCfolder As String, strPojtname As String, strFilename As String
    strSVCfolder = "" '?????????????? please provide
    strPojtname = "" '????????????? please provide
    strFilename = "" '?????????? please provide
    Call ImportAllSheets(strFilename, strSVCfolder, strPojtname)
    Call ImportAllComponents(strFilename, strSVCfolder)
   
    MsgBox "All component imported successfully "
   
getOut:
    Exit Sub
 
errHandler:
    MsgBox Err.Description
    GoTo getOut
    Resume
End Sub

Points of Interest

Save the VBA in Source Control Versioning Git.

How to use VSS and TFS for VBA source control - with new version of Office Git is more useful and it is free.

this is the part 1 of 2.  Second part 2-2 is deal with MS Access project.

History

  • 1st May, 2021: Initial version

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

VBA55
Software Developer (Senior)
Canada Canada
No Biography provided

Comments and Discussions

 
QuestionExcel sheet data on SVC Pin
roberto galbiati6-May-21 1:54
professionalroberto galbiati6-May-21 1:54 
Hi all
probably a little bit Off Topic, but anyway related.

Do you have any idea on how to move data in excel sheet back and forth to a SVC ?

I explain: we have a main project, with customizations, when needed, for different customers
One on the customizations is related to custom (different) text to be associated to Key , an some other stuff
Imagine a table with 7 columns.

Excel file is mantained for different customers, but updated by MAIN branch, and i can't move away (for now) from Excel

I need to mantain history AT ROW LEVEL, because i need to compare different versions, not the Excel itself

I'm thinking an easy way to export to TFS in a comparable format (txt), and to get back in excel again

Hoping it's clear ... Smile | :)

Thanks
AnswerRe: Excel sheet data on SVC Pin
VBA556-May-21 5:45
MemberVBA556-May-21 5:45 
GeneralRe: Excel sheet data on SVC Pin
roberto galbiati7-May-21 3:33
professionalroberto galbiati7-May-21 3:33 
QuestionAccess Pin
GrandPapaShep3-May-21 23:47
MemberGrandPapaShep3-May-21 23:47 
AnswerRe: Access Pin
VBA554-May-21 9:42
MemberVBA554-May-21 9:42 
AnswerRe: Access Pin
VBA557-May-21 7:59
MemberVBA557-May-21 7:59 
QuestionI had the same problem Pin
pierrecoach3-May-21 21:41
professionalpierrecoach3-May-21 21:41 
AnswerRe: I had the same problem Pin
VBA555-May-21 4:13
MemberVBA555-May-21 4:13 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.