Solved: VBA strangeness, need help please

[cvernon]

The following code works perfectly...

Module1:
[vba]
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
[/vba]

Module2:
[vba]
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
[/vba]

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:

[vba]
Sub Test()
Call Module2.Process
Call DeleteModule

Application.Quit
ThisWorkbook.Close SaveChanges:=True
End Sub
[/vba]

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!

[Bob Phillips]

Surely that is because Process saves it with Module2, before you delete it?

[cvernon]

If I REM out the ActiveWorkbook.Save line in Module2 it still behaves the same way (Module2 fails to remove itself).

[mdmackillop]

In your workbook
[vba]
Sub Test()
Application.Run "personal.xls!Process"
'or
'Application.Run "personal.xlsb!Process" '2007
End Sub
[/vba]


In Personal.xls
[vba]
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.Copy
Cells.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
ActiveWorkbook.Save
Application.Quit
End Sub


[/vba]

[cvernon]

I'm sorry, mdmackillop, I definately want to try whatever it is you're sharing with me, but (as I am a noob) I'm a bit confused.

What do you mean with 'In your workbook' (I think this means put the Test sub in the 'ThisWorkbook' object, please confirm).

As far as the Process sub going in the XLS, I'm not sure where. Does it go in Sheet1?

Thank you!

[mdmackillop]

Put Test into a standard module in the book you wish to copy/save. Put the other code in a standard module of your Personal.xls.file

[cvernon]

Thank you for that, it certainly works. However, I've come across another issue as a result. Perhaps it would help if I explained the goal:

I'm utilizing Task Scheduler to run a .bat file to open this XLS in the middle of the night. Here is what needs to be accomplished (and I will explain my new problem at the bottom).

1. Open the XLS and allow for external data sources to update (to which I have granted a minute's time; more than enough)
2. Print a copy to the default printer for the Operator's Logbook
3. Save a copy locally on the C drive (this is done because this is an isolated network [i.e. no access to the internet or the regular business network] and the operator will copy the file over to a thumb drive to then move over to the normal network in order to eMail a copy of the report to various department heads [we lovingly call this the sneaker-net solution])

All of this is accomplished, however, I'm going to move the Test logic into the Auto_Open sub (so it will fire automatically). My new problem (in case you didn't guess when I started this explanation) is that the department heads will get an error because they don't have the local Personal.XLS library (nor do I want them to).

What may not be obvious here is that I'm blowing out the formulas that grab the external data because the normal network can't see the SQL box.

Am I making any sense?

[mdmackillop]

Put this in Module1 of your workbook
[VBA]
Sub Test()
Dim NewDate As String
Dim FName As String
Dim WB As Workbook
Dim vbCom As Object

Application.Wait Now + TimeValue("00:00:02")
'ActiveSheet.PrintOut

NewDate = Format(Now() - 1, "yyyymmdd hhmmss")
FName = "c:\Reports\Totalizer Report " + NewDate + ".xls"


ActiveWorkbook.SaveCopyAs FName
Set WB = Workbooks.Open(FName)
Cells.Copy
Cells.PasteSpecial Paste:=xlPasteValues
Cells(1, 1).Select

WB.VBProject.VBComponents.Remove WB.VBProject.VBComponents("Module1")
ActiveWorkbook.Save
Application.DisplayAlerts = False
Application.Quit
End Sub
[/VBA]

[cvernon]

You, sir, are awesome!

I really can't thank you enough.