VBA stop running after Closing another Excel workbook

Question

Problem : VBA stop running after manually closing another Excel workbook

We have one Excel workbook (our application) with macros.
In this Excel workbook, we will import data from an Excel source file (E.g data.xlsx) then write data into new files.

This process is as below:

Use an EXE to define the columns that we want to import from an Excel source file (data.xlsx)
According to the format, read the source file (data.xlsx) and keep all the data in an array.
Write the array to a new file.

At first, we open our application (the Excel workbook with macros), then open “data.xlsx” to check the contents.
Then we run our macro to do import. At first use a function to run the Exe to define the import format. The start and end of Exe function is attached below (code snippet)

The dialog of Exe remains open, then the User close the “data.xlsx”.
The problem is, the VBA just stopped running without any warning or information.

I also found, “App_WorkbookBeforeClose” function did not run after the User closed the “data.xlsx”.

Please advise why the VBA stopped when another workbook is closed.
Do you have any suggestion how to resolve this issue?
Thank you in advance.

Code snippet

Public Sub StartEndProcess( _
ByVal exec_param As String, Optional strnum As String, _
Optional ByVal str_param As String, _
Optional ByVal dir_param As String)

Set fso = CreateObject("Scripting.FileSystemObject")

If Trim(strnum) <> "" Then
    exec_param = exec_param + " " + strnum
End If
start_info.cb = Len(start_info)
ret_no = CreateProcessA(vbNullString, exec_param, _
    0&, 0&, 0&, NORMAL_PRIORITY_CLASS, 0&, _
    dir_param, start_info, proc_info)
If ret_no = 0 Then
    If Err.LastDllError = 2 Then
        MsgBox "Cannot find system file.", vbInformation
        Err.Clear
    End If
    Exit Sub
End If

ret_no = CloseHandle(proc_info.hThread)

Do
    Do Until MsgWaitForMultipleObjects(1&, proc_info.hProcess, _
        0&, INFINITE, QS_ALLINPUT) <> WAIT_OBJECT_0 + 1
        Do While PeekMessage(lMsg, MainHwnd, WM_MOUSEFIRST, _
                         WM_MOUSELAST, PM_REMOVE)
            If lMsg.message = WM_LBUTTONUP Or _
                lMsg.message = WM_RBUTTONUP Or _
                lMsg.message = WM_MBUTTONUP Then
                lMsg.hwnd = ActivateWindow
                If lMsg.hwnd <> 0 Then
                    Call SetWindowPos(lMsg.hwnd, HWND_TOP, 0, 0, 0, 0, SWP_NOMOVE Or SWP_NOSIZE Or SWP_SHOWWINDOW)
                    ViewErrHwnd = ViewErrorWindow
                    If ViewErrHwnd <> 0 Then
                        Call SetWindowPos(ViewErrHwnd, HWND_TOP, 0, 0, 0, 0, SWP_NOMOVE Or SWP_NOSIZE Or SWP_SHOWWINDOW)
                    End If
                End If
            End If
            Call GetExitCodeProcess(proc_info.hProcess, lExitCode)
            If lExitCode <> STILL_ACTIVE Then
                CloseHandle (proc_info.hProcess)
                Exit Sub
            End If
            DoEvents 'HanSoong-18, let CPU to do other thing
        Loop

        Do While PeekMessage(lMsg, 0&, WM_PAINT, WM_PAINT, PM_REMOVE)
            Call DispatchMessage(lMsg)
            Call GetExitCodeProcess(proc_info.hProcess, lExitCode)
            If lExitCode <> STILL_ACTIVE Then
                CloseHandle (proc_info.hProcess)
                Exit Sub
            End If
            DoEvents 
        Loop
        Call GetExitCodeProcess(proc_info.hProcess, lExitCode)
        If lExitCode <> STILL_ACTIVE Then
            CloseHandle (proc_info.hProcess)
            Exit Sub
        End If
        DoEvents 
    Loop
    Call GetExitCodeProcess(proc_info.hProcess, lExitCode)
Loop While lExitCode = STILL_ACTIVE
ret_no = CloseHandle(proc_info.hProcess)

End Sub

Share via

VBA stop running after Closing another Excel workbook