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

Thank you, Steve, for your response
--
Regards

Robin

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, once again, Chip !

I think that's probably exactly what I need !


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