Kill a Macro?

[Andyjim]

Hi-

I am still stumped. I already submitted this question once, but the
response related to whether we launched the macro with a shortcut key which
we do not. We start the macro by either selecting the TOOLS MACRO Run method
from the Excel spreadsheet or in VBA itself. When we get it working, it will
be user-launched with a button. I may finally be onto what is causing this
problem though, but don't know the solution (probably simple!). Here's the
sequence, without code:
Macro1 is activated in File1
Macro1 copies the name of File1 into a cell in File2
Macro1 calls Macro2, which is located in File2
Macro2, using the File1 name which is now in a cell in File2, copies data
from File1 to File2. This process involves going back and forth a few times
between File1 & File2. That works fine.
After the data copy, Macro2 activates File1 one last time, saves it and
closes it. (All OK so far but right here is where it all stops. Nothing more
happens after File1 closes, and no error message.)
The next thing we want to happen is for Macro2 to save File2 As the name of
File1, which is stored in a cell in File2. That process works OK IF we
manually launch it from File2 VBA rather than from Macro1 (i.e. File1)
I THINK THE PROBLEM is that when File1 closes, Macro1 has not reached End
Sub, so when File1 closes the macro process cannot continue since we've
closed the file from which Macro1 was running. Is there a way to handle this
situation? Can we somehow kill the Macro1 process after it calls Macro2? I
think that might solve the problem.

Maybe you can answer the question from the above, but here's all the code
anyway:

Code for FIle 1:
Sub RunUpdate()
'Update button in user file Account Summary Sheet runs this macro

Unprotect

Dim updfile As String
Dim usrfile As Workbook
Dim backname As String
Dim p As String
p = ActiveWorkbook.Path
updfile = p & "\" & "fxRM_Update.xls"
Set usrfile = ActiveWorkbook
backname = p & "\" & "Backup" & usrfile.Name
currentuserfile = p & "\" & usrfile.Name

'Save backup copy of user's file, in same folder
If InStr(1, p, "\xlstart", vbTextCompare) = 0 Then 'If XLSTART is not in
the path
usrfile.SaveCopyAs filename:=backname 'do the save
Else 'Else if XLSTART is in the
path
'Do nothing, i.e. don't save
End If

'Insert user filename in "filename" cell
Sheets("Lookup").Select
Range("filename") = ActiveWorkbook.Name
'Insert user file path in "path1" cell
Range("path1") = ActiveWorkbook.Path

'Find & open fxRM_Update.xls
If Dir(updfile) = "" Then 'If fxRM_Update not there, error message
MsgBox ("fxRM_Update.xls not found. Move fxRM_Update.xls to this
folder.")
Exit Sub
End If 'On Error Resume Next

If IsFileOpen(updfile) Then 'If fxRM_Update.xls is open
'Do nothing, No error
Else 'If not open,
Workbooks.Open filename:=updfile 'Open it
End If

'Activate & Unprotect fxRM_Update.xls
Windows("fxRM_Update.xls").Activate
Unprotect

'Copy user file name to fxRM_Update.xls
usrfile.Activate
Sheets("Lookup").Select
Application.GoTo Reference:="Filename"
Selection.Copy
Windows("fxRM_Update.xls").Activate
Sheets("Lookup").Select
Application.GoTo Reference:="UserFilename"
ActiveSheet.Paste

'Copy user file path to fxRM_Update.xls
usrfile.Activate
Sheets("Lookup").Select
Application.GoTo Reference:="path1"
Selection.Copy
Windows("fxRM_Update.xls").Activate
Sheets("Lookup").Select
Application.GoTo Reference:="path2"
ActiveSheet.Paste

'Copy user file version to Update file, Old Version cell
usrfile.Activate
Sheets("Lookup").Select
Application.GoTo Reference:="CurrentVersion"
Application.CutCopyMode = False
Selection.Copy
Windows("fxRM_Update.xls").Activate
Sheets("Lookup").Select
Application.GoTo Reference:="OldVersion"
ActiveSheet.Paste
Range("K7").Select

'Run Update macro
Application.Run ("fxRM_Update.xls!Update")

End Sub

Code from File2:

Sub Update() '1/2008 - this adds data from the user file to
fxRM_Update_v1.32.2.xls

'Requires Update filename: "fxRM_Update.xls"
'Runs from Update file
'Copies data from bk1 (User file) to "fxRM_Update.xls" (Update file)
'"fxRM_Update.xls" becomes new user file, saved as user's filename
'Update2 defines user file in fxRM_Update.xls & activates it



'Copy AccountSummary data from User file to Update file
Update2 'every time you run Update2, you are reactivating Userfile
Sheets("AccountSummary").Select
Range("C6:C8").Select
Selection.Copy
Windows("fxRM_Update.xls").Activate
Sheets("AccountSummary").Select
Range("C6").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Update2
Sheets("AccountSummary").Select
Range("K7:M506").Select
Application.CutCopyMode = False
Selection.Copy
Windows("fxRM_Update.xls").Activate
Sheets("AccountSummary").Select
Range("K7").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("A1").Select
Update2
Sheets("AccountSummary").Select
Application.CutCopyMode = False
Range("A1").Select

'Copy TradeHistory data from User file to Update file
Sheets("TradeHistory").Select
Range("A6:Av15000").Select
Selection.Copy
Windows("fxRM_Update.xls").Activate
Sheets("TradeHistory").Select
Range("A6").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("A1").Select
Update2
Sheets("Analysis").Select
Range("A10").Select
Selection.Copy
Windows("fxRM_Update.xls").Activate
Sheets("Analysis").Select
Range("A10").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Update2
Sheets("Analysis").Select
Range("C10").Select
Application.CutCopyMode = False
Selection.Copy
Windows("fxRM_Update.xls").Activate
Sheets("Analysis").Select
Range("C10").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("A1").Select

'Copy AnalysisSheet data from User file to Update file
Update2
Sheets("Analysis").Select
Range("A17:F56").Select
Selection.Copy
Windows("fxRM_Update.xls").Activate
Sheets("Analysis").Select
Range("A17").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Update2
Sheets("Analysis").Select
Range("K17:M56").Select
Application.CutCopyMode = False
Selection.Copy
Windows("fxRM_Update.xls").Activate
Sheets("Analysis").Select
Range("K17").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Update2
Sheets("Analysis").Select
Range("O17:S56").Select
Application.CutCopyMode = False
Selection.Copy
Windows("fxRM_Update.xls").Activate
Sheets("Analysis").Select
Range("O17").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Update2
Sheets("Analysis").Select
Range("A16:AV16").Select
Selection.Copy
Windows("fxRM_Update.xls").Activate
Sheets("Analysis").Select
Range("A16").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Update2

ActiveWorkbook.Close (savechanges = True) 'MACRO Stops here when you
start with RUNUPDATE from user file.
'runs fine when you run UPDATE from
fxRM_Update file

Windows("fxRM_Update.xls").Activate


'Protect

'Save Update file as User file name
' Dim bk As Workbook
Dim saveString As String
Dim sstr2 As String
Dim path2 As String
path2 = ActiveWorkbook.Path
sstr2 = Worksheets("lookup").Range("d42").Value
saveString = path2 & "\" & sstr2


Application.DisplayAlerts = False


ActiveWorkbook.SaveAs filename:=saveString

Protect

'ActiveWorkbook.savechanges = False

ActiveWorkbook.Close

End Sub

Sub Update2()
'Defines user file in fxRM_Update.xls
'Update macro calls this macro each time it must return to user file

Dim bk As Workbook, bk1 As Workbook
Dim sstr As String
Set bk = Workbooks("fxRM_Update.xls")
sstr = bk.Worksheets("lookup").Range("userfilename").Value
Set bk1 = Workbooks(sstr)
bk1.Activate

End Sub

 

[anon]

By no means am I an expert but I think your presumption is correct. I
did not read all your code but I presume the reaosn you have to close
file1 is because you want to save file 2 as the same name, ie.
overwrite file 1.

If this is correct then you have no need to save file1 (as you are
going to save file2 as the same name anyway). So before closing file1
save file2 as the new name (disable warnings/pop up messages) and then
close file1 without saving.

Would this work for you? I haven't tested so may not be physically
possible.

 

[Conan Kelly]

Andyjim,

I'm not an expert, but if you have ever steped through code, you'll notice
that the execution goes back to the calling routine after the called routine
is finished running. Even if there is no more code in the calling routine
after the called routine, the execution still wants (?needs?) to go back to
the calling routine...???to execute "End Sub" (maybe...don't know...sounds
good)??? Because you are closing File 1, the calling routine is no longer
available, so the execution can not go back to it, so it stops right where
it is at.

I didn't look too closely at your code, but are both files open before
running any code? If so, will that always be the case? If not, does the
first macro in File 1 open File 2?

If they are both open to begin with, why not put both macros in file 2? The
other thing you might want to consider is not calling macro 2 from macro 1
at all, but create a whole new routine (and maybe keep it in your
personal.xls file) that will call macro 1 then call macro 2 immediately
after macro 1. That way, they both should run in succession from start to
finish without crashing.

Something else you might consider, you do not need to activate a workbook,
sheet, and cell/range in order to paste to it (or to copy from it for that
matter). You have a lot of extra lines of code in your routines that could
be simplified. You could do something like this:

Sub testing()
Dim pwkbSource As Workbook
Dim pwkbDestination As Workbook

Dim pwksSource As Worksheet
Dim pwksDestination As Worksheet

Dim prngSource As Range
Dim prngDestination As Range

Set pwkbSource = Application.Workbooks("File1.xls")
Set pwkbDestination = Application.Workbooks("File2.xls")

Set pwksSource = pwkbSource.Worksheets("Sheet1")
Set pwksDestination = pwkbDestination.Worksheets("Sheet1")

Set prngSource = pwksSource.Range("A1:A5")
Set prngDestination = pwksDestination.Range("A1:A5")

'for a straight copy-paste
prngSource.Copy prngDestination


'for a copy-paste special
prngSource.Copy
prngDestination.PasteSpecial [Paste special arguments]

End Sub


This way, you do not need to activate/select a workbook, activate/select a
worksheet, activate/select a range, copy, activate/select a workbook, work
sheet, range, and then paste.

You would need to reset your range object variables for each copy/paste
function.

HTH,

Conan

 

[Tom Hutchins]

Just a thought... Maybe you can split the part of Macro2 that runs after
File1 is closed into another macro (Macro3). Use an OnTime command to
schedule it a few seconds in the future, after Macro2 and Macro1 have
completed and File1 is closed. For Example:

Application.OnTime Now + TimeValue("00:00:04"), "Macro3"

runs Macro3 four seconds from Now. Also, maybe you could put the code to
close File1 at the end of Macro1, so when Macro2 ends File1 will close itself.

Hope this helps,

Hutch