Skip to content

Instantly share code, notes, and snippets.

@steve-jansen
Created November 21, 2013 20:56
Show Gist options
  • Save steve-jansen/7589478 to your computer and use it in GitHub Desktop.
Save steve-jansen/7589478 to your computer and use it in GitHub Desktop.
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
@jellisjo
Copy link

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

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!

Copy link

ghost 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

@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

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"

@slimjame
Copy link

Thank you so much for posting this! Super time saver.

Also, Thanks Joe Gearhart for:

MsgBox ("Successfully exported " & CStr(count) & " VBA files to " & directory)

@huikinglam02gmail
Copy link

Wonderful!

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