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

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

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

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

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

IF the user has ENABLED access to the VB object model..
then I suggest you use some code to clear the modules.
just google or go to Chip Pearson's site for some example code.


Else.. i've made this one..
saves as xmlSpreadsheet (xlXP+)..then reopens and saves as .xls


you'll get rid of the macro's but it also clears any objects.
formulas,formatting,validation etc are preserved.

Plus it's slow :(

Sub SaveCopyWithoutMacros()
Dim sFull$, sTemp$, sPath$, sFile$
sFull = Application.GetSaveAsFilename("Copy of " & ActiveWorkbook.Name)
If sFull = vbNullString Then
Exit Sub
ElseIf Right(sFull, 1) = "." Then
sFull = sFull & "xls"
End If
If Dir(sFull) < vbNullString Then
If vbCancel = MsgBox("File Exists!. OverWrite?)", vbOKCancel) Then
Exit Sub
End If
Kill sFull
End If
sPath = Left$(sFull, InStrRev(sFull, "\") - 1) & "\"
sFile = Mid$(sFull, InStrRev(sFull, "\") + 1)
sTemp = Environ("Temp") & "\"


Application.DisplayAlerts = False
Application.ScreenUpdating = False
Application.EnableEvents = False

'First save a copy in the tempdir
ActiveWorkbook.SaveCopyAs sTemp & sFile
'Open the copy , save as xml
With Workbooks.Open(sTemp & sFile)
.SaveAs sTemp & Replace(sFile, "xls", "xml"), xlXMLSpreadsheet
.Close 0
End With
'open the xml, save as xls in final destination
With Workbooks.Open(sTemp & Replace(sFile, "xls", "xml"))
.SaveAs sFull, xlWorkbookNormal
.Close 0
End With

Application.DisplayAlerts = True
Application.ScreenUpdating = True
Application.EnableEvents = True

Kill sTemp & sFile
Kill sTemp & Replace(sFile, "xls", "xml")


End Sub





keepITcool

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


"J_J" wrote:

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

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