How to Export VBAProject in Excel

Question

I prepared a VBA Project in Microsoft Excel that has many userforms and macros. I want to export all of the files, but it appears you can only do this one by one, which would take me a very long time.

Is there any way to export the whole project? Thanks!

Answer 1

Here is some VBA code that I use to export VBA code:

'Requires Microsoft Visual Basic for Applications Extensibility
Private Function exportvba(Path As String)
Dim objVbComp As VBComponent
Dim strPath As String
Dim varItem As Variant
Dim fso As New FileSystemObject
Dim filename As String

filename = fso.GetFileName(Path)

On Error Resume Next
    MkDir ("C:\Create\directory\for\VBA\Code\" & filename & "\")
On Error GoTo 0

'Change the path to suit the users needs
strPath = "C:\Give\directory\to\save\Code\in\" & filename & "\"

  For Each varItem In ActiveWorkbook.VBProject.VBComponents
  Set objVbComp = varItem

  Select Case objVbComp.Type
     Case vbext_ct_StdModule
        objVbComp.Export strPath & "\" & objVbComp.name & ".bas"
     Case vbext_ct_Document, vbext_ct_ClassModule
        ' ThisDocument and class modules
        objVbComp.Export strPath & "\" & objVbComp.name & ".cls"
     Case vbext_ct_MSForm
        objVbComp.Export strPath & "\" & objVbComp.name & ".frm"
     Case Else
        objVbComp.Export strPath & "\" & objVbComp.name
  End Select
Next varItem
End Function

The Path variable being passed in is the path to the file you want to export code from. If you have more than one file, just use this function in a loop.