how to move from one series of code, execute a different on in another module, and step back in

Question

In this bit of code i am working on it will open a defined workbook which has an "on open" event that will handle transferring a lot of data and then saving as. All of that is fine, this is where i am lost...

After the transfer bit it needs to set focus back to the parent book, easy, but its needs to step back into the parent code to handle the last steps and closing the transfer book...

Now before it gets going i am separating the code because this workbook is already a heavy file, i am trying not to overwhelm it, and also keep in mind my clients computers are not typically as good as mine, so bogging it down really bogs it on their computer.

I've kicked around a continue event, but i am not sure how to reset the focus onto that line of code.

Sub TransferMe()
    'Runs the script to start the transfer manager

    answer = MsgBox("This will transfer then clear all data for new period, are you sure you want to continue?", vbYesNo, Title:="New Period")
    If answer = vbYes Then
        MsgBox ("Please be patient this may take a few minuets."), Title:="Please Wait..."

        Application.Cursor = xlWait

        'open the transfer manager
        Workbooks.Open Filename:="C:\Users\dlroy\Dropbox\SSS\WORKING OR COMPLETE\Ian McDermid - Pump Bar\Prime Cost Suite\TransManager.xlsm"

        'this is where the transfer workbook opens which has an "on open" event
        'that will handle transferring all of my data
        'it then needs to set focus back on the original worksheet and restart the code

        'Ending code will handle closing the transfer workbook with out 
        'saving as it will already save as
        'and then complete the last couple of steps and end the macro.

        Application.Cursor = xlDefault
    Else
        MsgBox ("Goodbye."), Title:="Exit"
        Exit Sub     
    End If             
End Sub

I just need it to step back into the parent code and continue execution. Any ideas would be awesome! Thank you in advance!

Answer 1

You can handle this by an Application.OnTime timer.

By opening the second workbook, you start the timer:

Option Explicit

Private TimerStart As Double
Private Const SecondsToWait As Double = 10
Private OtherWorkbook As Workbook

Private Sub StartOtherWorkbookAndTimer()
    TimerStart = Timer
    Application.OnTime Now + TimeValue("00:00:01"), "CheckIfOtherWorkbookFinished"
    Workbooks.Open (Application.DefaultFilePath & "\NameOfOtherWorkbook.xlsm")
End Sub

The following sub checks every second during e. g. 10 seconds, if the other workbook is still open. If the other workbook has done it's work and closed itself in the meantime, you can do the remaining work.

If the other workbook doesn't close itself, you need to recognize the finish of the other workbook's tasks by e. g. a value in it's first cell. This is also handled by this sub:

Private Sub CheckIfOtherWorkbookFinished()
    Dim secondsElapsed As Double
    secondsElapsed = Timer - TimerStart
    On Error Resume Next
    Set OtherWorkbook = Workbooks("NameOfOtherWorkbook.xlsm")
    On Error GoTo 0
    If OtherWorkbook Is Nothing Then
        MsgBox "Other workbook is closed. Now I do the remaining work ..."
        ' do the remaining work here, if other workbook is closed within 10 seconds
    ElseIf OtherWorkbook.Worksheets(1).Range("A1").Value = "ready" Then
        MsgBox "Other workbook is ready. Now I do the remaining work ..."
        ' do the remaining work here, if other workbook said "ready" in it's first cell
        OtherWorkbook.Close
    ElseIf Int(SecondsToWait - secondsElapsed) > 0 Then
        Application.OnTime Now + TimeValue("00:00:01"), "CheckIfOtherWorkbookFinished"
    Else
        MsgBox SecondsToWait & " seconds elapsed, but other workbook still open?!"
    End If
End Sub