cvernon
04-24-2009, 12:41 PM
The following code works perfectly...
Module1:
Sub Test()
Call Module2.Process
Call DeleteModule
End Sub
Sub DeleteModule()
Dim vbCom As Object
Set vbCom = Application.VBE.ActiveVBProject.VBComponents
vbCom.Remove VBComponent:=vbCom.Item("Module2")
End Sub
Module2:
Sub Process()
Application.Wait Now + TimeValue("00:01:00")
ActiveSheet.PrintOut
Dim NewDate As String
NewDate = Format(Now() - 1, "yyyymmdd hhnnss")
ActiveWorkbook.SaveAs Filename:= _
"c:\Reports\Totalizer Report " + NewDate + ".xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveWorkbook.Save
End Sub
However, once 'Test' completes (i.e. I have processed everything and I want to remove Module 2), I want to close out Excel. I've added the following code to Module1:
Sub Test()
Call Module2.Process
Call DeleteModule
Application.Quit
ThisWorkbook.Close SaveChanges:=True
End Sub
Now, DeleteModule no longer functions. Module2.Process will fire and everything is fine. Then the application will close (great). But when I look at my newly created file, Module 2 is there (before I added the new code, Module2 would successfully remove itself).
I'm pretty new to the VBA world, but this just doesn't make sense to me. I'd really appreciate some help in removing my frustration.
Thank you!
Module1:
Sub Test()
Call Module2.Process
Call DeleteModule
End Sub
Sub DeleteModule()
Dim vbCom As Object
Set vbCom = Application.VBE.ActiveVBProject.VBComponents
vbCom.Remove VBComponent:=vbCom.Item("Module2")
End Sub
Module2:
Sub Process()
Application.Wait Now + TimeValue("00:01:00")
ActiveSheet.PrintOut
Dim NewDate As String
NewDate = Format(Now() - 1, "yyyymmdd hhnnss")
ActiveWorkbook.SaveAs Filename:= _
"c:\Reports\Totalizer Report " + NewDate + ".xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveWorkbook.Save
End Sub
However, once 'Test' completes (i.e. I have processed everything and I want to remove Module 2), I want to close out Excel. I've added the following code to Module1:
Sub Test()
Call Module2.Process
Call DeleteModule
Application.Quit
ThisWorkbook.Close SaveChanges:=True
End Sub
Now, DeleteModule no longer functions. Module2.Process will fire and everything is fine. Then the application will close (great). But when I look at my newly created file, Module 2 is there (before I added the new code, Module2 would successfully remove itself).
I'm pretty new to the VBA world, but this just doesn't make sense to me. I'd really appreciate some help in removing my frustration.
Thank you!