Running a macro from a macro 4

[ReggB]

I will not get into the specifics of why I want to do this to keep this post short. I have a macro in a workbook that calls a macro in a second workbook. The second macro then closes the first workbook afterwhich it is suppose to do some clean up. Everything works find until I close the first workbook, then the second macro stops. If I do not close the first workbook then the rest the code executes as it should. I think the second macro stops because the calling macro closes and thus has nowhere to return to when finished. Is there a way to keep the second macro running after I close the first workbook?

Regards,

ReggB

 

[SkipVought]

In VBA, the code is being run from the VB Project within a WORKBOOK.

Closing the WORKBOOK, ends the function of the VB Project and all the procedures spawned by the Project. The Procedure in the other workbook is called from the Project and runs in that Project memory area, and consequently has no memory space once the WORKBOOK closes.

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}

 

[ReggB]

SkipVought,

Makes sense. Still, is there a way to do what I want to do?

Regards,

ReggB

 

[TonyJollans]

Hi ReggB,

Easiest way is probably to use the OnTime method to fire the macro in the second workbook as a separate process.

In the first workbook ...

[blue]Sub FirstSub()
    :
    :
    :
    Application.OnTime Now, "SecondBook.xls!SecondSub"
End Sub[/blue]

And in the second workbook ...

[blue]Sub SecondSub()
    :
    :
    :
    Workbooks("FirstBook").Close
    :
    :
    :
End Sub[/blue]

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[FaneDuru]

If the line closing the first workbook is the last it will do the job... Is anything bothering in this approach?
Fane Duru

 

[FaneDuru]

Or you may use this code which closes the first workbook through a vbs file:

Sub RunningMacroAndClosing()
 x = ThisWorkbook.FullName: z = ThisWorkbook.Name: w = "'Second Workbook.xls'!MacroTest"
Cale = ThisWorkbook.Path & "\"
y = "Set OpenedExcel = GetObject(,""Excel.application"")" & vbCrLf & _
"With OpenedExcel" & vbCrLf & _
"  .Workbooks(" & Chr(34) & z & Chr(34) & ").Saved = True" & vbCrLf & _
"  .Workbooks(" & Chr(34) & z & Chr(34) & ").Close" & vbCrLf & _
"   .Run " & Chr(34) & w & Chr(34) & vbCrLf & _
"End With" & vbCrLf & _
"Set OpenedExcel = Nothing" & vbCrLf & _
"Set fso = CreateObject(""Scripting.FileSystemObject"")" & vbCrLf & _
"fso.GetFile(WScript.ScriptFullName).Delete"

Set R = CreateObject("WScript.Shell")
Temp = R.SpecialFolders(2)
Set fso = CreateObject("Scripting.FileSystemObject")
Set VB = fso.OpenTextfile(Temp & "\Test.vbs", 2, True)
   VB.Write y
   VB.Close
Set Loc1 = fso.GetFile(Temp & "\Test.vbs")
Set shl = CreateObject("Shell.Application")
 shl.Open Loc1.Path
End Sub

Fane Duru

 

[ReggB]

No, the close workbook would be followed by other code but, I will give the OnTime code a go.

I have never used scripting but will give it a try if the first suggestion does not work.

I will post my results within a day or two.

Thanks for the ideas.

Regards,

ReggB

 

[FaneDuru]

OK!
But you will not write any script. It is a volatile one made through the code and it is self deleted after running...

Fane Duru

 

[ReggB]

FaneDuru,

Finally got a change to look closer at your code example. Which macro do I put your code in. The macro that calls the second macro (in the second workbook) or the second macro?

Regards,

ReggB

 

[FaneDuru]

This macro is in the first workbook and calls the one named "MacroTest" from the second workbook.
Practically the macro memorizes the name of the first workbook (the one having it) in variable "z" and a string containing names of the second workbook plus second macro in variable "w". After that it creates a script and runs it. This script finds the open Excell session, finds the first workbook and closes it without saving. The next step is to run the macro in the second workbook to clean the OpenedExcel object and kill himself.

Fane Duru

P.S. "x = ThisWorkbook.FullName" it is not necessary. I used it for a procedure making the Excel file to commit suicide.

 

[WalterContrata]

Forgive me for kibitzing, but both of these solutions are really neat! FaneDuru's method was flagged by Norton Anitivirus when the VB Script tried to delete itself. Why does it do that?

Best Regards

 

[ReggB]

Tony & Fane,

Finally found some time to sit down and actually run your suggestions. Both solutions worked with my test code. However, like wcon, my Norton Antivirus produced a dialog informing me "Malicious script detected, High Risk, Your computer is halted and needs to do something about this script." That could be a problem since I do not know who will run my code. Fane, do you have any suggestions for getting around the warning message? Anyway, a star to both of you. I can continue with writing the rest of my code and see what problems I encounter there.

Regards,

ReggB

 

[fumei]

Why not just hide the first wookbook - but still have it open for processing the code? When the second macro finishes, it will return process to the hidden first wokbook. Which can then close the file neatly. No need for other script.

Gerry

 

[ReggB]

I cannot hide the first workbook. The first workbook was opened by another application. I need the first workbook to close because it will trigger an event in the calling application. The second workbook traps this event. The catch is, I do not want use any saved files, macros or programs unless absolutely necessary. The idea is; the first workbook creates the second workbook, then copies a sheet from the first workbook to the second. This copied sheet contains the VBA code which is then called by the first workbook (only way I could figure out how to transfer code between workbooks without using the VBProject.VBComponents Export and Import commands). This code sets up an event handler for the calling application, closes the first workbook, waits for the event to happen, processes the event and then closes itself (without saving). Using the solutions provided in this post, I am able to do this (almost). Right now I need to figure out how set up references to the calling application's object library in the second workbook through code so I can set up the withevents code. If I cannot find an existing post with a solution, look for a new post by me asking if anyone knows how to do this. I have spent some time looking through the excel VBA help to no avail. I will also try MSDN and VB6 help. This must be possible.

Regards,

ReggB

 

[ReggB]

Just an update. After looking through the excel VBA help some more, I figured out how to add references to my new workbook (VBProject.References.AddFromFile) and have started working on my event code. Thanks again to Tony and Fane for your help.

Regards,

ReggB