xl2000 doesn't support saving as XML. So you might want to use code like I
posted or go to Chip's site.


--
Regards,
Tom Ogilvy

"J_J" wrote in message
...
I am using WinXP with Office2K and using your code I received an object
SaveAs method error with the line

.SaveAs sTemp & Replace(sFile, "xls", "xml"), xlXMLSpreadsheet

Does that mean's that I cannot use your code then?.


"keepITcool" wrote in message
...
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