2

I have an ms access Application ( ms access 2013) and I'd like to export all vba code to separate files in a folder

What code can i add to do this?

Thanks,

Lamloumi Afif
  • 8,941
  • 26
  • 98
  • 191

1 Answers1

6

Try this:

Public Sub ExportVBAComponents()

  Dim wbPath As String
  Dim vbComp As Object
  Dim exportPath As String

  wbPath = ActiveWorkbook.Path

  For Each vbComp In ActiveWorkbook.VBProject.VBComponents
    exportPath = wbPath & "\" & vbComp.Name & Format$(Now, "_yyyymmdd_hhnnss")

    Select Case vbComp.Type
        Case 1 ' Standard Module
            exportPath = exportPath & ".bas"
        Case 2 ' UserForm
            exportPath = exportPath & ".frm"
        Case 3 ' Class Module
            exportPath = exportPath & ".cls"
        Case Else ' Anything else
            exportPath = exportPath & ".bas"
    End Select

    On Error Resume Next
    vbComp.Export exportPath
    On Error GoTo 0
  Next

End Sub

The code above will export all the VBA components/modules in your ActiveWorkbook to the same location as your workbook. It will use the component name as part of the file name and will add a timestamp. Since you have more than 100 modules, you'd better change the export path to include a subfolder to group them all together in one place.

NOTE: For this to work, you need to select Options>Trust Center>Trust Center Settings...>Macro Settings>Trust access to the VBA project object model. Otherwise you get some random error in the For Each line. You can tick that option off afterwards if you're worried about it.

djikay
  • 10,450
  • 8
  • 41
  • 52
  • it indicates that the variable `ActiveWorkbook` not found!! – Lamloumi Afif Jul 16 '14 at 12:53
  • 1
    @LamloumiAfif: Ah yes, I tested it with exporting modules from Excel. Not sure what it's called for Access (haven't used it for ages), I'll try to investigate and get back to you. – djikay Jul 16 '14 at 12:59
  • Thanks very much , I'm waiting :) – Lamloumi Afif Jul 16 '14 at 13:01
  • 2
    @LamloumiAfif: Actually, while searching for it I realised it's already been answered here: [Exporting MS Access Forms and Class / Modules Recursively to text files?](http://stackoverflow.com/a/17362688/3593811). Can you please take a look and see if that works for you? It's seems to cover all bases very well. – djikay Jul 16 '14 at 13:10
  • should be exporting to `.bas` file extension for `vbCompType = 1`, and to `.frm` file extension for `vbCompType = 3` and `.cls` for `vbCompType = 2`, this way you can also export class modules and forms. – David Zemens Jul 16 '14 at 13:12
  • 1
    @DavidZemens: My code was just for standard modules, you are correct. Thank you for your input. The Access-specific SO question I linked in my previous comment is much more comprehensive and can deal with all kinds of Access modules. – djikay Jul 16 '14 at 13:15
  • 1
    Remember to refer to / Check the **Microsoft Visual Basic For Applications Extensibility Library** in VBE > Tools > References. – Đức Thanh Nguyễn Jan 18 '15 at 22:16
  • This doesn't work in ms access. I recieved a compile error – Daniel L. VanDenBosch Aug 30 '17 at 14:54
  • 1
    To get to the VBComponents collection in the Access object model, use `For Each vbComp In VBE.VBProjects(1).VBComponents`. This worked for me (and I changed the setting of the path at `wbPath = ActiveWorkbook.Path` to just a constant.) – Mike Jul 31 '19 at 18:43