Export VBA Modules

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Robin Clay - 02 Dec 2007 12:56 GMT

Greetings !

I have created an AddIn Library comprising quite a few different modules.

Yes, I know I can export the text of a module into a *.bas file - but is
there any way that I can export them all "at one stroke" ?

Regards

Robin

Reply to this Message

SteveM - 02 Dec 2007 13:23 GMT

On Dec 2, 7:56 am, Robin Clay <Robin_B DOT Clay AT virgin DOT net>
wrote:

> Greetings !
>
[quoted text clipped - 6 lines]
>
> Robin

Here's a link to a web page that shows you how to create an Excel Add-
in. Pretty simple.

http://www.fontstuff.com/vba/vbatut03.htm

SteveM

Reply to this Message

Robin Clay - 02 Dec 2007 18:26 GMT

Thank you, Steve, for your response

Signature

Regards

Robin

Reply to this Message

Chip Pearson - 02 Dec 2007 14:23 GMT

You can try code like the following:

Sub ExportAllModules()

Dim VBComp As VBIDE.VBComponent
Dim ExportDir As String
Dim Ext As String
Dim FName As String

ExportDir = "C:\Temp" '<<<< CHANGE DIRECTORY

For Each VBComp In ActiveWorkbook.VBProject.VBComponents
If VBComp.Type = vbext_ct_ClassModule Then
Ext = ".cls"
ElseIf VBComp.Type = vbext_ct_Document Then
Ext = ".cls"
ElseIf VBComp.Type = vbext_ct_StdModule Then
Ext = ".bas"
Else
Ext = vbNullString
End If

If Ext <> vbNullString Then
FName = ExportDir & "\" & VBComp.Name & Ext
If Dir(FName, vbNormal) <> vbNullString Then
Select Case MsgBox("File: " & FName & " already exists.
Overwrite?" & vbCrLf & _
"Click 'Yes' to overwrite the file." & vbCrLf & _
"Click 'No' to skip this file." & vbCrLf & _
"Click 'Cancel' to termiante the export operation.",
vbYesNoCancel, "Export Modules")
Case vbYes
Kill FName
VBComp.Export FName
Case vbNo
' do nothing
Case vbCancel
Exit Sub
End Select
Else
VBComp.Export FName
End If
End If
Next VBComp

End Sub

You'll need to set a Reference (in VBA, Tools menu, References) to
"Microsoft Visual Basic For Applications Extensibility 5.3". Also, you'll
need to enable "Trust Access To The Visual Basic Editor" in the Macro
Security, Trusted Sources settings. Change the line marked with '<<<<' to
the appropriate directory in which to save the exported files.

For more detail about working with VBA code and the VBProject, see
http://www.cpearson.com/Excel/vbe.aspx

Signature

Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)

> Greetings !
>
[quoted text clipped - 6 lines]
>
> Robin

Reply to this Message

Robin Clay - 02 Dec 2007 18:31 GMT

Thank you, once again, Chip !

I think that's probably exactly what I need !

Regards

Robin

Reply to this Message

Robin Clay - 12 Dec 2007 22:30 GMT

Sorry to bother you again, but....

How should this Code be amended to write them all into one file ?

Signature

Regards

Robin

> You can try code like the following:
>
[quoted text clipped - 62 lines]
> >
> > Robin

Reply to this Message

Chip Pearson - 12 Dec 2007 22:57 GMT

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

Signature

Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)

> Sorry to bother you again, but....
>
[quoted text clipped - 69 lines]
>> >
>> > Robin

Reply to this Message