Thank you Tom,
One additional question...Does your code put back the original settings of
Excel before closing?. Because I do not want to make any permenant changes
to the default excel workbook. Just need to save a "macro-less" copy of the
file that includes macros.
Regards
J_J

"Tom Ogilvy" wrote in message
...
http://groups.google.com/groups?thre...%40tkmsftngp07


''Needs a reference to the VB Extensibility library set
'Removes from active workbook all:
''Regular modules
''Class modules
''Userforms
''Code in sheet and workbook modules
''Non built-in references
''Excel 4 macro sheets
''Dialog sheets
Sub RemoveAllCode()
Dim VBComp As Object, AllComp As Object, ThisProj As Object
Dim ThisRef As Reference, WS As Worksheet, DLG As DialogSheet
Set ThisProj = ActiveWorkbook.VBProject
Set AllComp = ThisProj.VBComponents
For Each VBComp In AllComp
With VBComp
Select Case .Type
Case vbext_ct_StdModule, vbext_ct_ClassModule, _
vbext_ct_MSForm
AllComp.Remove VBComp
Case vbext_ct_Document
.CodeModule.DeleteLines 1, .CodeModule.CountOfLines
End Select
End With
Next
For Each ThisRef In ThisProj.References
If Not ThisRef.BuiltIn Then ThisProj.References.Remove ThisRef
Next
Application.DisplayAlerts = False
For Each WS In Excel4MacroSheets
WS.Delete
Next
For Each DLG In DialogSheets
DLG.Delete
Next
End Sub

--
Regards,
Tom Ogilvy

"J_J" wrote in message
...
Hi,
Can your code be modified so that the saved copy of the file does not
include any VBA module and VBA sheet&workbook macros but the rest?
TIA

"keepITcool" wrote in message
...

instead of saving the file source itself you save a copy of the file.
the openworkbook remains "Source.xls" but on the disk you'll find your
copy.



If your workbook is an empty "fill the form" type of sheet
then consider saving it as a Template..

If your workbook is a continuing "database" then saving a copy
will allow you/your user a safety "snapshot".

to "clear" the screen and reload the "fresh source".. reopen source.
to show the copy .. open the copy... note that the macros will now
appear twice.



keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"?B?UGFwYSBKb25haA==?="
wrote:

I'm not sure I understand. I have an inputbox that the user enters
the name to be used. This inputbox appears as part of a macro in a
workbook called, "Source". When the user enters a name like
"personal
safety", I want the macro to continue to save the file as "persoanal
safety." This works.

However, the next time I use "source" all of my macros are
duplicated
with "personal safety" attached to the beginning of each macro
title.


"keepITcool" wrote:


saving it as a copy wont give you that problem.


Sub SaveACopy()
Dim vName
vName = Application.GetSaveAsFilename
If vName < False Then
If vName < ThisWorkbook.FullName Then
ThisWorkbook.SaveCopyAs vName
End If
End If
End Sub



keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"?B?UGFwYSBKb25haA==?="

wrote:

I have a workbook with a number of macros. One of the macros
saves
the file as with a name provided through an input.

However, the orignial workbook then has a list of all of the
macros
duplicated with references to the newly saved file.
I want the original workbook's macros to stay - unchanged so that
it can then be reused as a template.

Any ideas?

TIA