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

On Dec 2, 7:56 am, Robin Clay <Robin_B DOT Clay AT virgin DOT net
wrote:
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

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

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


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



"Robin Clay" <Robin_B DOT Clay AT virgin DOT net wrote in message
...
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

Thank you, Steve, for your response
--
Regards

Robin

Thank you, once again, Chip !

I think that's probably exactly what I need !


Regards

Robin

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)



"Robin Clay" <Robin_B DOT Clay AT virgin DOT net wrote in message
...
Sorry to bother you again, but....

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

Robin


"Chip Pearson" wrote:

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


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



"Robin Clay" <Robin_B DOT Clay AT virgin DOT net wrote in message
...
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

Hi, this is very nearly what I require, but I really need to select an
individual worksheet, by name, and then export that to a .cls file. I also
need to subsequently import it (via a macro) into another version of my
workbook.

I want something like this:

VBComp = ActiveWorkbook.Worksheets("A1") 'tried this, it doesnt work
VBComp.Export FName

VBComp.Import FName

Can either of these things be done?

Sorry, I am a hack ;-)

"Chip Pearson" wrote:

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


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



"Robin Clay" <Robin_B DOT Clay AT virgin DOT net wrote in message
...
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

It doesn't work! Lots of error messages.



"Poindexter" wrote:

Hi, this is very nearly what I require, but I really need to select an
individual worksheet, by name, and then export that to a .cls file. I also
need to subsequently import it (via a macro) into another version of my
workbook.

I want something like this:

VBComp = ActiveWorkbook.Worksheets("A1") 'tried this, it doesnt work
VBComp.Export FName

VBComp.Import FName

Can either of these things be done?

Sorry, I am a hack ;-)

"Chip Pearson" wrote:

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


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



"Robin Clay" <Robin_B DOT Clay AT virgin DOT net wrote in message
...
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