Extracting all VBA modules from a project

  • Thread starter Thread starter Andrew Black (delete obvious bit)
  • Start date Start date
A

Andrew Black (delete obvious bit)

hi
Is there a way of extracting all the modules in a VBA project
into seperate text (or .BAS) files. I can see how to export
one but this gets tiresome?

Thanks
 
One by one unless you have Office Developer, which has the ability to save
multiple modules at one time.
 
I found the following code on the web somewhere- can't
remember where, apologies to the writer for the lack of
credit.

It's Excel-specific, but claims to be readily portable to
other Office apps.

HTH,

-Andrew
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Reference the "Microsoft Visual Basic for Applications
Extensibility 5.?"
library. Using this you can access the modules
(ThisWorkbook, Worksheets, standard
Modules, Class Modules and Forms).

This code will list all Module, Procedure Name, Procedure
Type and the Code for each
procedure:

Public Sub ListAll()
Dim vbp As VBProject
Dim vbc As VBComponent
Dim colProcItem As Collection
Dim piItem As ProcInfo

Set vbp = ActiveWorkbook.VBProject

' Iterate all components (modules)
For Each vbc In vbp.VBComponents

' Create collection of ProcInfo objects (one per
procedure)
Set colProcItem = GetVBProcedureNames
(vbc.CodeModule)
For Each piItem In colProcItem

' Module name, procedure name
With piItem
Debug.Print .Module, .ProcName, .ProcType

' Now display the code
''' Debug.Print .ProcCode
End With
Next piItem
Next vbc
End Sub ' ListAll

Private Function GetVBProcedureNames(ByVal modAny As
CodeModule) As Collection
Dim lngCLines As Long
Dim lngType As Long
Dim colProcNames As New Collection
Dim strProcName As String

Dim piItem As ProcInfo

' Loop through modules collection looking for valid
procedure names
With modAny

' Loop through all code lines, looking for a
procedure
For lngCLines = 1 To .CountOfLines

' Get the name of the procedure on the current
line
strProcName = .ProcOfLine(lngCLines, lngType)

' If non-blank then we've found a proc
If LenB(strProcName) > 0 Then

' Collect all of the relevant procedure
information
Set piItem = New ProcInfo
piItem.Module = modAny.Name
piItem.ProcName = strProcName
piItem.ProcType = lngType
piItem.ProcCode = modAny.Lines(lngCLines, _
.ProcCountLines(strProcName, lngType))

' Skip the code lines by adding the number
of lines in the proc (less one)
' to the current line number
lngCLines = lngCLines + .ProcCountLines
(strProcName, lngType) - 1

' Save the procedure information
colProcNames.Add piItem
End If
Next lngCLines
End With
Set GetVBProcedureNames = colProcNames
End Function ' GetVBProcedureNames


Add this to a Class Module named "ProcInfo"
<=========Start of Class Module ProcInfo==========>
Public Module As String
Public ProcName As String
Public ProcType As String
Public ProcCode As String
<=========End of Class Module ProcInfo===========>

The above is an Excel VBA example, but will port readily.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 
Public Sub ListAll()
Dim vbp As VBProject ....
Set vbp = ActiveWorkbook.VBProject
Click to expand...

Thanks Andrew

This looks like the sort of thing that will be useful but I can't work out
how to get into the data structure. As you say the code is Excel specific.
How can I get the Outlook VBproject.

Andrew
 
There is no "Project" in Outlook VBA. I would imagine
you'd have to look at "ThisOutlookSession" instead.

I didn't expect the code to port directly...you'll have to
do some work to convert it, and it may not be doable at
all. I just offered it as a possible approach, since no
one else had anything to offer...

-Andrew
====================================================
 
Outlook has no comparable way to automate saving modules. I just export
manually.

--
Sue Mosher, Outlook MVP
Author of
Microsoft Outlook Programming - Jumpstart for
Administrators, Power Users, and Developers
 
Back
Top