Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Excel macro to export all VBA source code in this project to text files for proper source control versioning
' Excel macro to export all VBA source code in this project to text files for proper source control versioning
' Requires enabling the Excel setting in Options/Trust Center/Trust Center Settings/Macro Settings/Trust access to the VBA project object model
Public Sub ExportVisualBasicCode()
Const Module = 1
Const ClassModule = 2
Const Form = 3
Const Document = 100
Const Padding = 24
Dim VBComponent As Object
Dim count As Integer
Dim path As String
Dim directory As String
Dim extension As String
Dim fso As New FileSystemObject
directory = ActiveWorkbook.path & "\VisualBasic"
count = 0
If Not fso.FolderExists(directory) Then
Call fso.CreateFolder(directory)
End If
Set fso = Nothing
For Each VBComponent In ActiveWorkbook.VBProject.VBComponents
Select Case VBComponent.Type
Case ClassModule, Document
extension = ".cls"
Case Form
extension = ".frm"
Case Module
extension = ".bas"
Case Else
extension = ".txt"
End Select
On Error Resume Next
Err.Clear
path = directory & "\" & VBComponent.name & extension
Call VBComponent.Export(path)
If Err.Number <> 0 Then
Call MsgBox("Failed to export " & VBComponent.name & " to " & path, vbCritical)
Else
count = count + 1
Debug.Print "Exported " & Left$(VBComponent.name & ":" & Space(Padding), Padding) & path
End If
On Error GoTo 0
Next
Application.StatusBar = "Successfully exported " & CStr(count) & " VBA files to " & directory
Application.OnTime Now + TimeSerial(0, 0, 10), "ClearStatusBar"
End Sub
@AdrianoBortoloto
Copy link

AdrianoBortoloto commented Sep 9, 2015

The "ClearStatusBar" called at last line is missing!
Besides this, it runs OK: generating the folder and project files!
Forked!
I will share my excel projects on github soon (using this macro).
Thank you very much!

@Hung-process
Copy link

Hung-process commented May 17, 2016

The "ClearStatusBar" called at last line is missing!

You can create new one with this name and

Application.Statusbar = False

@maxwellmckinnon
Copy link

maxwellmckinnon commented Jul 15, 2016

Is there an import function as well?

@dhavalmalvania
Copy link

dhavalmalvania commented Feb 15, 2017

HOW TO EXPORT THE DATA OF EXCEL FILE TO TEXT FILE INDIVIDUALLY RAW WISE NOW I WANT TO EXPORT THE DATA OF THIS LIKE BELOW IN INDIVIDUALLY TEXT FILE FOR EACH RAW. 1ST TEXT FILE CONTAINS THE DATA OF A1,B1,C1,A2,B2,C2 2ND TEXT FILE CONTAINS THE DATA OF A1,B1,C1,A3,B3,C3 SO ON ACCORDINGLY TILL THE DATA IS THERE . PLEASE REPLY TO dhavalmalvania@gmail.com

@C0ppert0p
Copy link

C0ppert0p commented Feb 24, 2017

Thanks! This is a huge savings in time for us

@emeraldjava
Copy link

emeraldjava commented Jun 14, 2017

@maxwellmckinnon Did you ever find a method to import the source controlled VBA files back into the excel workbook?

@danielleevandenbosch
Copy link

danielleevandenbosch commented Aug 30, 2017

error2 ran into an issue

@GeorgHaupt
Copy link

GeorgHaupt commented Sep 12, 2017

FileSystemObject needs Microsoft Scripting Runtime

grafik

also allow the macro access the vba-code, as described above

grafik

@skatun
Copy link

skatun commented Dec 7, 2017

Instead of using fso, I prefer to use mkdir:

 If Dir(directory, vbDirectory) = "" Then
      MkDir directory
    End If

@skatun
Copy link

skatun commented Dec 7, 2017

Any idea how to use git shell and at the end of your code directly push it to git?

@marcucciboy2
Copy link

marcucciboy2 commented Dec 21, 2017

@danielleevandenbosch

you can instead add the line:
Set fso = CreateObject("Scripting.FileSystemObject")

after your error on line 15.

@P3012852
Copy link

P3012852 commented Jan 24, 2018

@steve-jansen

Upon Changing Application.StatusBar = False it has worked Perfectly!

@bstiel
Copy link

bstiel commented Jan 28, 2018

In case you are interested, there is an alternative approach which does not require any VBA at all. The trick is to use a pre-commit hook to execute some Python code that extracts the VBA code from the workbook file (without using Excel itself): https://www.xltrail.com/blog/auto-export-vba-commit-hook

@djeffc
Copy link

djeffc commented Feb 4, 2018

Awesome. Thank you for this!

I just changed line 41 to prepend the active workbook name:

path = directory & "" & ActiveWorkbook.Name & " " & VBComponent.Name & extension

@Bobby-82
Copy link

Bobby-82 commented May 7, 2018

I commented-out the last two lines and added a Msgbox that uses the text from one of the lines. The Msgbox displays immediately upon completion instead of a delay at the status line.

The end of the macro now looks like this:
' Application.StatusBar ...
' Application.OnTime ...
MsgBox "Successfully exported " & CStr(count) & " VBA files to " & directory
End Sub

Clearer in hindsight, but it took me some time to get there: To save the macros in the Personal Workbook, the Personal Workbook needs to be visible when the macro is started.

Making the Personal Workbook visible was not intuitive for me and, in Excel 2013, it requires accessing the Unhide button on the View tab of the ribbon. Then the macros are saved to the 'VisualBasic' folder at:
C:\Users*username*\AppData\Roaming\Microsoft\Excel\XLSTART\VisualBasic,
as advertised in the msgbox when the macro is done.

@o1sowise
Copy link

o1sowise commented May 8, 2018

This is FANTASTIC! Thank you!!!!

@pescara
Copy link

pescara commented Feb 11, 2019

Great Tool! Thank you a lot!

@jellisjo
Copy link

jellisjo commented May 13, 2019

why is it adding blank lines in my userform .frm files ?

github is telling me the code changed but it really didnt.

image

@ryanpotato
Copy link

ryanpotato commented Oct 14, 2019

This is great. it worked on my machine except I have all my macros as an Add-in .xlam, multiple modules. Any way to get revise this code to export those? I bet it's just one change on line 25

Edit: I figured it out. It was trivial btw. Just change ActiveWorkbook to ThisWorkbok on line 25.
Thanks to https://sourcedaddy.com/ms-excel/distribute-macros.html for the clue.

@ArthurAttout
Copy link

ArthurAttout commented Dec 13, 2019

If anyone is looking for the equivalent code to import VBA, you might use this

Sub ImportVisualBasicCode()
 
    Dim oFSO As Object
    Dim oFolder As Object
    Dim oFile As Object
    Dim i As Integer
    Dim directory As String
     
    Set oFSO = CreateObject("Scripting.FileSystemObject")
     
    Set oFolder = oFSO.GetFolder(ActiveWorkbook.path & "\VisualBasic")
     
    For Each oFile In oFolder.Files
     
        directory = ActiveWorkbook.path & "\VisualBasic\" & oFile.Name
        ActiveWorkbook.VBProject.VBComponents.Import directory
        
        If Err.Number <> 0 Then
            Call MsgBox("Failed to import " & oFile.Name, vbCritical)
        End If
     
    Next oFile
 
End Sub

@jellisjo
Copy link

jellisjo commented Dec 13, 2019

Occasionally when i am importing and exporting modules. The name will come in with a suffix of 1. Does any one know of a fix? This is annoying as eventually the name will look like this "modulename1111111111"

@ArthurAttout
Copy link

ArthurAttout commented Dec 14, 2019

This might happen because you're importing a module with the same name as an already existing module. You should delete it before importing.

@baietoo
Copy link

baietoo commented Feb 27, 2020

Thank you for this!

@RayInAZ
Copy link

RayInAZ commented Jun 12, 2020

This routine saved me a ton of time converting a Excel 2007 set of VBA code to Excel 2019 VB.NET. This routine helped me get past the first step in the process (using Visual Studio).

@jugaso
Copy link

jugaso commented Oct 23, 2020

Great code, thanks!

@jellisjo
Copy link

jellisjo commented Oct 23, 2020

@ArthurAttout yes I do delete all the modules before importing but sometimes it still happens. But rarely.

@Siphonophora
Copy link

Siphonophora commented Feb 24, 2021

This is super helpful. For anyone who is working with macros in word documents, not excel, simply replace all instances of ActiveWorkbook with ActiveDocument

One other note. Every time you run this the FRX files will show up as having changed in GIT. This is a known issue and some cursory reading suggests fixing this wouldn't be simple. hilkoc/vbaDeveloper#5

@JoeGearhart
Copy link

JoeGearhart commented Jun 9, 2021

This is amazing. Thank you.
I changed the directory definition to meet my needs and had to remove the line to "ClearStatusBar". Instead of using the status bar at all, I created a MsgBox line to notify that the data was exported and where.

I then saved this as an add-in so that it's available for any file I open now.

It works great and I'm able to keep this in version control with GitHub now.

directory = "C:\Users\<REDACTED>\OneDrive\VisualBasic\" & ActiveWorkbook.Name

MsgBox ("Successfully exported " & CStr(count) & " VBA files to " & directory)
'REMOVED: Application.StatusBar = "Successfully exported " & CStr(count) & " VBA files to " & directory
'REMOVED: Application.OnTime Now + TimeSerial(0, 0, 10), "ClearStatusBar"

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment