×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

stop VBA execution while in external application
3

stop VBA execution while in external application

stop VBA execution while in external application

(OP)
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?

RE: stop VBA execution while in external application

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)

RE: stop VBA execution while in external application

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

RE: stop VBA execution while in external application

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)

RE: stop VBA execution while in external application

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.

RE: stop VBA execution while in external application

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)

RE: stop VBA execution while in external application

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.

RE: stop VBA execution while in external application

(OP)
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.

RE: stop VBA execution while in external application

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

-handleman, CSWP (The new, easy test)

RE: stop VBA execution while in external application

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?

RE: stop VBA execution while in external application

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)

RE: stop VBA execution while in external application

(OP)
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?

RE: stop VBA execution while in external application

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.

RE: stop VBA execution while in external application

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)

RE: stop VBA execution while in external application

2
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.

CODE

    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.

CODE

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

Eric

RE: stop VBA execution while in external application

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

-handleman, CSWP (The new, easy test)

RE: stop VBA execution while in external application

(OP)
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.

RE: stop VBA execution while in external application

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

-handleman, CSWP (The new, easy test)

RE: stop VBA execution while in external application

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
 

RE: stop VBA execution while in external application

(OP)
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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources