Hello Dear programmers! Please help me solve the following problem with the vba code: the button in the excel 2003 book copies the application file "form_v1.exe" (this is a window with fields and the Close button) to the temporary folder from which this file is started immediately after copying. The user working with the book can complete the process "form_v1.exe" (close the window) or not. Question: As when closing this book: 1) complete the running process "form_v1.exe" if it is still running, i.e. interrupt it if necessary by force, 2) delete the process file "form_v1.exe" from the temporary folder. Note: The process file "form_v1.exe" may be with a different name, but the name always starts with "form *". The code below works correctly only if the user has completed the process before closing the book. Thanks for any of your tips.

Private Sub Workbook_BeforeClose(Cancel As Boolean) Dim Process As Object For Each Process In GetObject("winmgmts:").ExecQuery("Select * from Win32_Process") If Process.Caption Like "form*" Then Process.Terminate End If Next Kill Environ("temp") & "\" & "form*" End Sub
  • I can imagine how happy the user will be if the process (just for example) FormatFactory.exe, which has converted the video several hours before it is killed) when it closes the Excel workbook) - Aleksei

2 answers 2

Well, for starters - at least

 For Each Process In GetObject("winmgmts:").ExecQuery( _ "Select * from Win32_Process Where Caption Like 'form%'") HResult = Process.Terminate Select Case HResult Case 0 MsgBox "Successful completion" CASE 2 MsgBox "Access denied" CASE 3 MsgBox "Insufficient privilege" CASE 8 MsgBox "Unknown failure" CASE 9 MsgBox "Path not found" CASE 21 MsgBox "Invalid parameter" CASE Else MsgBox "Other error" End Select Next

The code below works correctly only if the user has completed the process before closing the book.

So in this case, only Kill works ... why would it not work correctly?

And what is observed if the user has NOT completed the process?

  1. the process is not unloaded (hardly)
  2. the process is unloaded, but the file is not deleted (most likely)

UPDATE

Yes, the process is unloaded, but the file is not deleted from the temporary folder.

The file is not deleted because the upload is performed asynchronously. As a result - at the time of the Kill attempt, the Terminate command has already been sent, but has not yet been completed, and the file is correspondingly locked. Solution - after the first receipt of processes and sending commands to upload again request in a cycle with a small (100 ms) delay list of processes until it becomes empty, and only then delete files.

    The code below - to launch the application "form_v1.exe", is triggered by clicking on the button in the excel 2003 book, and will not close the book until the application is closed, which means the process "form_v1.exe" will not need to be tracked and interrupted:

     Dim sFileName As String, sNewFileName As String sFileName = "\\server\form_v1.exe" sNewFileName = Environ("temp") & "\" & "form_v1.exe" FileCopy sFileName, sNewFileName Dim FullPath As String Dim wsh As Object Dim waitOnReturn As Boolean Dim windowStyle As Integer Application.ScreenUpdating = True Set wsh = VBA.CreateObject("WScript.Shell") waitOnReturn = True windowStyle = 1 FullPath = Environ("temp") & "\" & "form_v1.exe " & Chr(34) & ThisWorkbook.FullName & Chr(34) wsh.Run FullPath, windowStyle, waitOnReturn

    It is necessary only when you close the book to clear from the temporary folder the application traces:

     Dim sFileName As String sFileName = Environ("temp") & "\" & "form*" If Dir(sFileName, 16) <> "" Then Kill sFileName End If

    Tell me your opinion?