Robin,
You can't Export multiple modules to a single text file. Moreover, except
for archiving purposes, there is little use for combining all the code into
a single file. The reason are that you cannot later Import the code to the
VBProject. The code in the output file will be imported into a single module
file, not broken out into the various modules whence it came. Also, you will
lose the Attribute statements. These are compiler directives that are not
visible within the VBA Editor but control aspects of a module or class.
That said, the following will dump all the code from the ActiveWorkbook into
a single text file.
Sub ExportAllModules()
Dim VBComp As VBIDE.VBComponent
Dim Ext As String
Dim FName As String
Dim FNum As Integer
Dim LineNum As Long
If ActiveWorkbook.Path = vbNullString Then
MsgBox "You must save the workbook before exporting code"
Exit Sub
End If
FName = ActiveWorkbook.FullName & ".txt"
FNum = FreeFile()
Open FName For Output Access Write As #FNum
For Each VBComp In ActiveWorkbook.VBProject.VBComponents
Print #FNum, vbNullString
Print #FNum, "''''''''''''''''''''''''''''''''''''''"
Print #FNum, "'''' START: " & VBComp.Name
Print #FNum, "''''''''''''''''''''''''''''''''''''''"
With VBComp.CodeModule
For LineNum = 1 To .CountOfLines
Print #FNum, .Lines(LineNum, 1)
Next LineNum
End With
Print #FNum, "''''''''''''''''''''''''''''''''''''''"
Print #FNum, "'''' END: " & VBComp.Name
Print #FNum, "''''''''''''''''''''''''''''''''''''''"
Print #FNum, vbNullString
Next VBComp
Close #FNum
End Sub
--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)
