Giving Macro control to another workbook

[Guest]

I am trying to write a macro that will check to see if a newer version of the
workbook that is being run exists. If it does, my thought was to pass control
to a second workbook, close the first, copy/update the first workbook and
restart the first workbook and finally close out the second.

While I understand how to run a macro in another workbook, if I close the
first workbook the second workbook macros just stop. Below is my code
example, any ideas? Or indeed is there a much smarter way to do this? Because
of course this will cause the Enable macro's message several times.

In Workbook1:

Sub Start()
Workbooks.Open Filename:="Workbook2.xls"
Run "Workbook2.xls" & "!MyMacro2", param1, param2, param3
ThisWorkbook.Close SaveChanges:=False
End Sub

Sub MyMacro1()
' continue doing stuff
End Sub


In Workbook2:

Sub MyMacro2(param1, param2, param3)
' Do stuff
Workbooks.Open Filename:="Workbook1.xls"
Run "Workbook1.xls" & "!MyMacro1"
ThisWorkbook.Close SaveChanges:=False
End Sub

 

[NickHK]

Why not put a routine in Personal or an add-in that does the checking.
It can use the Activeworkbook as the "old" version then make your check for
a newer one based on filename or however you do it now.
If this code finds a newer version, close the "old" and open the new, may be
with a Kill of the old and a move of the new, if necessary.
Less conflict.

NickHK

 

[Guest]

NickHK,

Many thanks for your reply. An interesting thought, but I need to distribute
this application and that means being in control of my app on someone elses
machine.

 

[NickHK]

Yes, so you can place an xls in their start folder, create an add-in etc do
the management of your files.
Otherwise every file you send will have to have the file checking code
included in itself, which seems a waste.

NickHK