stop VBA execution while in external application 3

[dogarila]

I am running a small VBA program which at some point may call in an external exe program, then returns to VBA. The problem I have is it does not return to the point where the exe program was called. It keeps running to the end, in parallel. How do I stop it while running the exe to return in the same point?

 

[handleman]

How are you calling the exe program? What does it do? I think you'll have three (possibly more) options:

1. Guess an amount of time that the exe will take (I'm assuming no user interaction with the exe), add a safety factor, and use a the Timer function and a DoEvents inside a While loop to pause the macro execution.

2. Have your VBA code somehow check for the results of the exe program within a loop containing a DoEvents statement. Depending on what these results are, code to check for results may be processor intensive.

3. Have your VBA code check the running processes and see if your exe is running. Once again, the loop needs to contain DoEvents. There are plenty of places to get sample code to check for running processes. Here's one I found. Never used any of the code though.

http://www.vbforums.com/showthread.php?p=2748682

-handleman, CSWP (The new, easy test)

 

[TheTick]

How does your VBA app tell when the exe is done? How does the VBA app get the result from the exe?

 

[handleman]

Tick,

I think that's the problem. The VBA app doesn't know whether the exe is done or not, and does not pass a result to the VBA. For example, maybe he's trying to automate opening a model, allowing the user to change custom properties using PropaGator or the like, then save as PDF. There would be no way for the VBA macro to know that the user was finished with PropaGator without looking for the PropaGator process.

-handleman, CSWP (The new, easy test)

 

[TheTick]

Trying to elicit some useful info from OP. Either way, OP has not posted anything helpful. No code. Just a cry for help and the hope that we are psychic and omniscient.

 

[handleman]

Actually, any one of the three options I posted would work just fine, although it is true that a better solution may exist depending on any additional information.

-handleman, CSWP (The new, easy test)

 

[TheTick]

Your option 2 is my usual route through this sort of thing. Option 3 is better but requires cumbersome WinAPI.

Wait loops in VBA are a pain because they consume processor power without doing much when done poorly.

 

[dogarila]

I've always had the feeling that you guys were psychic omniscient. It's as handleman said. The user opens a model and starts a macro to export the flat pattern. The program checks if the name of the laser program exists and if not launches the custom properties exe program. The user enters the name of the laser program and exit the custom properties application. I would like to return were I left and continue with the saving of the flat pattern.

So there is user interaction with the external program. It can't be timed. I looked at the link in option 3 and got scared.

 

[handleman]

I agree wholeheartedly on all three of those points. Heck, they consume processor power even when done well!

-handleman, CSWP (The new, easy test)

 

[TheTick]

Perhaps use a modal form to launch the exe? Then the VBA app will resume from where it started when the form is closed.

Since you are only dealing with custom properties and maybe need to make a form, perhaps just code the custom property entries into your VBA app?

 

[handleman]

If all you care about is entering one custom property wouldn't it be easier to just ask the user for it in the body of the macro? An InputBox or even a small form seems better than calling an external exe file.

-handleman, CSWP (The new, easy test)

 

[dogarila]

Coding the custom property entry in the body of the macro is my second choice. I may endup doing it that way.

Now, going back to the option 3. I got those functions from there and into my application. How do I use them? Within a Do While loop?

 

[TheTick]

Instead of automatically launching the app, have it launched from a form that pops up if the exe is required. The form can have two buttons, one to launch the app and one to close the form, plus instructional text. If the form is opened as modal (which it is by default), the VBA app will pause while the exe is running and resume when the form is closed. No loop, no API.

 

[handleman]

Heck, if all you want is a modal stop, use a MsgBox immediately after launching the exe:

MsgBox "Do not click OK until you have entered laser program!"

If you really want to make sure the user goes to the exe's window, use AppActivate to bring it to the front prior to the MsgBox.

Of course, whether you use a modal form or just a MsgBox, your code will have to trap for the possibility of the user not following instructions and clicking "OK" too soon.

-handleman, CSWP (The new, easy test)

 

[EEnd]

If you include the "Windows Script Host Object Model" in your project then you can use a WScript to launch the program in a manner which lets you query for termination. The following example launches notepad.

    Dim exec As WshExec
    Set exec = CreateObject("WScript.Shell").exec("notepad")
    
    Do While exec.Status = 0
         Sleep 100
    Loop

I am unfamiliar with the DoEvents / Timer technique that handleman was talking about, so I used the Sleep sub to include the delay in the polling loop. To gain access to the Sleep sub, you will need the following line somewhere above where sleep is used, and outside of any sub or function definitions.

Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

Eric

 

[handleman]

Great tip Eric! I forgot about Sleep. Sleep is better than Timer/DoEvents.

-handleman, CSWP (The new, easy test)

 

[dogarila]

It works great with notepad. With my CustomProperties.exe the window of the exe gets behind and it doesn't show in the taskbar and the computer freezes.

 

[handleman]

Can you use an AppActivate statement to bring your exe to the front?

-handleman, CSWP (The new, easy test)

 

[EEnd]

I wonder if the problem lies in using sleep. The macro is running in SolidWorks and your executable is also interacting with SolidWorks. It is possible that telling SolidWorks to sleep is preventing the executable from running. You might try handleman’s timer approach for the delay in the polling loop.

Eric

 

[dogarila]

Eric, I think you are right. Unfortunately I can't use the timer idea because there is user interaction with the exe and I can't guess how long that's going to be.

I will create a small form inside the macro where the user can enter the missing information instead of runnin an external application.

Thank you guys for your support. Stars for you.