VBA stop running after Closing another Excel workbook
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