1

I'm launching a program from a VBA macro and I need to wait for it to finish running before I can go and post-process the resulting data. So far, I've been satisfied with the following as the running time was pretty consistent (about 2sec.) :

    While FileLen(outputFilename) < 9000 And (Timer - Chrono) < 10
    Wend

Indeed I've observed that all the result data is written in one go and before that, the output size doesn't exceed 9Ko. Anyway, for some reason, the program isn't consistent anymore so I looked a bit more into a clean solution to wait for the shell process to stop. I found ideas about WaitForSingleObject and the module ShellAndWait which I found here. This post is what helped me with finding some functions.

My problem now is that the macro keeps running while the Shell is still open. From what I understand, WaitForSingleObject returns 0, which should mean the Shell is closed (or finished running), but it's not. More specifically, in ShellAndWait,

WaitRes = WaitForSingleObject(ProcHandle, DEFAULT_POLL_INTERVAL)

raises 0. I tried to extract the significant part of the code by adapting it a bit :

Private Declare Function WaitForSingleObject Lib "kernel32" (ByVal hHandle As Long, ByVal dwMilliseconds As Long) As Long
Private Declare Function OpenProcess Lib "kernel32" (ByVal dwDesiredAccess As Long, ByVal bInheritHandle As Long, ByVal dwProcessId As Long) As Long
Private Declare Function CloseHandle Lib "kernel32" (ByVal hObject As Long) As Long

Private Const SYNCHRONIZE = &H100000
Public Const INFINITE = &HFFFF
Public Const PROCESS_ALL_ACCESS = &H1F0FFF

Sub launch()

analysisPath = "Path to the files to feed to the programm"
ShellCommand = "Program to run + arguments"

ChDrive ThisWorkbook.Path
ChDir analysisPath

fileID = Shell(ShellCommand, vbNormalFocus)

ProcHandle = OpenProcess(SYNCHRONIZE, False, fileID)

If ProcHandle<>0 Then
    WaitRes = WaitForSingleObject(ProcHandle, INFINITE)
Else
    MsgBox "Failed running Nastran"
End If
fileID = CloseHandle(ProcHandle)

End Sub

And WaitResstill raises 0. Am I using WaitForSingleObject wrong and if so, could you point me in the right direction ?

edit : It also doesn't work with the solution accepted in this post (meaning the shell runs but doesn't stop the macro to keep going). Could it be due to the specific program I'm running which would behave strangely ?

Community
  • 1
  • 1
BluK
  • 53
  • 2
  • 7
  • Have you tried calling something trivial like notepad to make sure it's working? – Charlie May 18 '16 at 15:56
  • If I try running Notepad in the Shell, it opens Notepad and it does freeze the macro, until I close Notepad. The difference wih my case is that there isn't a proper application opening. It's just a shell being prompted. Would that make a difference in the way the code understands it ? – BluK May 18 '16 at 16:31
  • Yes I believe WaitForSingleObject looks for very specific exit conditions. I would try putting a System Exit in your script to make it look like your script has "failed" and then detect the WAIT_FAILED. That might be easier than trying to get it to register as a "success." – Charlie May 18 '16 at 16:44
  • I'm not sure I understand. Could you develop ? – BluK May 19 '16 at 06:20
  • I'm happy to help but I need to see what you're calling. Can you tell me more about what SYNCHRONIZE is and post any relevant code or links? – Charlie May 19 '16 at 12:35
  • What I'm calling is not a script of my own, it's a compiled program (.exe). So I don't have latitude to change the way it behaves. SYNCHRONIZE is just a part of the code I copied from`ShellAndWait`. From what I understand from the [Windows support](https://msdn.microsoft.com/en-us/library/windows/desktop/ms687032(v=vs.85).aspx), it is necessary for WaitForSingleObject to work. I'm afraid it's not that helpful :s – BluK May 19 '16 at 15:46
  • Makes sense. Sorry I'm out of suggestions. If you're trying to wait on a program you can't control and it's not outputting the correct exist signal then I think you're stuck. – Charlie May 19 '16 at 16:13
  • No worries. I'll keep trying different things and see if something works. – BluK May 20 '16 at 11:03

1 Answers1

0

So, I kept searching and I found a bit of code that worked. I actually found it here.

BluK
  • 53
  • 2
  • 7