Close a workbook without saving macros

[Guest]

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

 

[keepITcool]

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 >

 

[Guest]

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]

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 >

 

[J_J]

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]

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 >

 

[Tom Ogilvy]

http://groups.google.com/groups?threadm=e3uxzQP0BHA.2228@tkmsftngp07


''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

 

[J_J]

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?.

 

[Tom Ogilvy]

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

 

[J_J]

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

 

[Dave Peterson]

I'm not Tom, but that code only removes the macros.

It'll be up to you to save the resulting workbook as a new name -- so the
original workbook is unaffected.

 

[J_J]

Thanks Dave...

 

[Guest]

Thanks everyone!

Thanks Dave...