Save workbook without macro

[sujittalukde]

I am saving a workbook from a workbook from a macro. It is saving the workbook correctly but saving the workbook with macro.
I want that the macro should save the workbook without any type of macro ie Macro in standard module, macro in THis Workbook module and macro in Sheet module.

[lynnnow]

Can you create a template for you output file and append data into the output file? It works for me like that. I can give you the code to copy paste data into the output file if that is the case.

[anandbohra]

I found this file from net or might be from other forum.

This is exactly what u need.
it can delete its own macro, modules, forms, objects ( so better keep a copy of it before running code)

one more thing before running module ensure that Trust access to Visual Basic Project is ticked under Trusted Publishers tab in Macro security (tools - macro - security - trusted published tab)

[anandbohra]

forgot attachment

[sujittalukde]

Thanks anandbohra, this is the type of code that I was looking for. But certains problems are there:
1. I am using the following code to save the file at D:\


Sub SaveAsFileAsDate()
Dim WSName As String, CName As String, Directory As String, savename As String
WSName = "Attendance"
CName = "A1"
Directory = "d:\"
savename = Sheets(WSName).Range(CName).Text
If Directory = "" Then Directory = CurDir & "\"
On Error GoTo errorsub:
ActiveWorkbook.SaveAs Filename:=Directory & savename & ".xls"
Call DeleteAllObjects
Call SaveClose
Exit Sub
errorsub:
Beep
MsgBox "Changes not saved! See INSTRUCTIONS for details on Saving the file", vbExclamation, Title:=savename & ".xls"
End Sub




Sub SaveClose()
ActiveWorkbook.Save
'ThisWorkbook.Save
'ThisWorkbook.Close
End Sub


In this workbook module:


Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.DisplayAlerts = False
ActiveWorkbook.Save
Application.DisplayAlerts = True
End Sub
Private Sub Workbook_Open()
Sheets("Sheet2").Select
End Sub


Now the file test asks for saving the file ie Do you want to save the changes?
Here if "YES" is clicked then on next time the file is opened, test.xls, doesnot ask for Enable/Disable macro as no code is attached with the test.xls.

But if No is clicked then on next time the file is opened, test.xls,asks for Enable/Disable macro and also code is attached with the test.xls.

How to proceed now?

[lynnnow]

If you want the output file to be without macros, then

Application.DisplayAlerts = False
.
.
.
'Remove the macro project
.
.
.
ActiveWorkbook.Save
.
.
.
Application.DisplayAlerts = True
.
.
End Sub

This shld help.

[anandbohra]

u can force to click yes by using this code

ActiveWorkbook.Close SaveChanges:=True

[sujittalukde]

I have tried the code and modified the same but still the same thing happens. If the user clicks NO to the test file, the macro remains there.
Attached the file for ready reference.

[anandbohra]

modification required in your code

Sub SaveAsFileAsDate()
Application.DisplayAlerts = False
Dim WSName As String, CName As String, Directory As String, savename As String
WSName = "Attendance"
CName = "A1"
Directory = "d:\"
savename = Sheets(WSName).Range(CName).Text
If Directory = "" Then Directory = CurDir & "\"
On Error GoTo errorsub:
ActiveWorkbook.SaveAs Filename:=Directory & savename & ".xls"
Call DeleteAllObjects
ActiveWorkbook.Save
Application.DisplayAlerts = True
Exit Sub
errorsub:
Beep
MsgBox "Changes not saved! See INSTRUCTIONS for details on Saving the file", vbExclamation, Title:=savename & ".xls"
Application.DisplayAlerts = True

End Sub

[sujittalukde]

Thanks for your continuing support.
Still the same problem is continuing....
When the file test.xls is being closed, it asks to save or not and if the NO is clicked, the test file is retaining the macro.

[anandbohra]

have u tried the attached workbook

its working fine at my end with all permutation combinations

or otherwise give me step by step method of what u have done after downloading my recent workbook.

[sujittalukde]

After downloading I did the followings:
1. Downloaded the file and saved to Desktop.
2. Opened the file with macros being enabled.
3. Sheet2 activated. Moved to Attendance Sheet.
4. Clicked the textbox where the save.. macro is assigned.
5. test.xls opened
6. CLosed the test file.
7. Excel prompted Do you want to save the changes?
8. I clicked "No"
9. test file closed.
10. Moved to D:\
11. Opened the test file
12. It asked to enable/disable macro. the macro are also saved to test.xls (Noted that only the macros in standard module are there, the macros in This Workbook module are deleted)

(NOTE: In step 8, if I click "Yes", then macros are not saved to test file.)

[sujittalukde]

Bump!

[anandbohra]

hi trying on it.
the cause is the moment delete macro code runs it wipes out all existing code so we can not let the workbook save after the code.
& as this happens if user close without save the code remains in the books ( as u told earlier)

so have to found other way out.


why don't u go for project password ????

[johnske]

I am saving a workbook from a workbook from a macro. It is saving the workbook correctly but saving the workbook with macro.
I want that the macro should save the workbook without any type of macro ie Macro in standard module, macro in THis Workbook module and macro in Sheet module.

Sub SaveSheetsAsBook()
'we want to save a copy of all the sheets
'in the current workbook as a different
'workbook - but without macros etc.
ThisWorkbook.Sheets.Copy

'the copied sheets now become the active workbook
With ActiveWorkbook
.SaveAs ActiveWorkbook.Path & "testme.xls" '< set your own path here
.Close
End With
End Sub


EDIT: if you also want to get rid of the workbook with the macros and just retain the one without code, run the code given here (http://www.vbaexpress.com/kb/getarticle.php?kb_id=540) after the code above...