delete macro and auto save/quit

C

chris r

All -

I have been learning about how to delete macros in vba. It works. But take a
look at this code (in "Module1"):

Sub delete_save_quit()
Dim vbCom As Object
Set vbCom = Application.VBE.ActiveVBProject.VBComponents
Application.DisplayAlerts = False
vbCom.Remove VBComponent:= _
vbCom.Item("Module1")
ActiveWorkbook.Save
Application.Quit
' Application.DisplayAlerts = True
End Sub

Problem looking for a solution: I want to remove the module, and also save
and quit automatically since I am running excel via the command line (.bat
file). As a note, what follows is true even when I rename the subroutine to
"Auto_Open", which is what I want since everything needs to be
automated...opening the workbook, (running some macros), deleting the entire
module, saving, quitting.

Here is the behavior of this subroutine: Since I do not want to be prompted
to save the workbook, I set DisplayAlerts=False. If I do this, the module is
not deleted, but the workbook is saved (modified date is updated). If I do
not DisableAlerts, I will be prompted to save, but the module WILL be deleted
upon saving. It doesn't seem to matter where I put the
DisplayAlerts=False...before the "Set" or after the "Remove" doesn't change
the behavior I am seeing either. Lastly, I thought that I may need to
DisplayAlerts=True for some reason, so I would also try adding the line at
the end of the above subroutine. All this does is prompt me to save (this is
after the code says 'save' and 'quit')! help please...sounds like there are
some great vba programmers here...
 
D

Die_Another_Day

Try setting the "Save Changes" property of the workbook as you close
it.

ActiveWorkbook.Close False 'Don't Save
ActiveWorkbook.Close True 'Save

Charles
 
C

chris r

I just tried your suggestion...doesn't seem to make a difference regardless
of where I place the "ActiveWorkbook.Close True" statement. I will remember
the property for future vba coding though, thanks for the info...

So...still need help on this one...

--
- chris


Die_Another_Day said:
Try setting the "Save Changes" property of the workbook as you close
it.

ActiveWorkbook.Close False 'Don't Save
ActiveWorkbook.Close True 'Save

Charles
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top