VBA and Shell command

[Anonymous]

I have an app, that consists of many different projects. Each project contains many different modules. The user selects from a list of different VBA projects to run by check boxes. In some cases, I have to run a batch routine which saves drawings, copies and moves files(done through the shell command in VBA). Anything to do with the shell command, or batch routine, I've placed that into a module that runs at the very end of the selected project.

When I run each process individually (select the specific process) in the application, everything works fine. But if I run multiple projects in the application, (select more than one specifc process) sometimes the dos command prompt is still running, when the other vba project starts. There are files that are required for both projects. As a result, with the dos prompt still running and the vba starting from the next selected project, the VBA crashes because the files are currently locked up in the dos command process.

How can I control the next project so that it doesn't start until the previous project is completely finished runnning? Including the dos prompt?

[Anonymous]

Instead of using the SHELL command, why not use
the Microsoft Scripting Runtime to copy and move files?

--
Matt W
 
There are 3 kinds of people:
  Those
who can count, and those who can't.

style="PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">

"kb" <
href="mailto:kbowman@city.grande-prairie.ab.ca">kbowman@city.grande-prairie.ab.ca>
wrote in message
href="news:f199c30.-1@WebX.maYIadrTaRb">news:f199c30.-1@WebX.maYIadrTaRb...

I
have an app, that consists of many different projects. Each project contains
many different modules. The user selects from a list of different VBA projects
to run by check boxes. In some cases, I have to run a batch routine which
saves drawings, copies and moves files(done through the shell command in VBA).
Anything to do with the shell command, or batch routine, I've placed that into
a module that runs at the very end of the selected project.

When I run each process individually (select the specific process) in the
application, everything works fine. But if I run multiple projects in the
application, (select more than one specifc process) sometimes the dos command
prompt is still running, when the other vba project starts. There are files
that are required for both projects. As a result, with the dos prompt still
running and the vba starting from the next selected project, the VBA crashes
because the files are currently locked up in the dos command process.

How can I control the next project so that it doesn't start until the
previous project is completely finished runnning? Including the dos
prompt?

[Anonymous]

Sorry, I should have mentioned. Some of the batch routines not only copy and move files, they run the SDFLoader as well.

[Anonymous]

There are a couple of Win32 API functions that can help you, ShellExecuteEx and
WaitForSingleObject.

If you don't have an API reference, look here
http://www.mentalis.org/agnet/apiguide.shtml.


"kb" wrote in message
news:f199c30.1@WebX.maYIadrTaRb...
> Sorry, I should have mentioned. Some of the batch routines not only copy and
move files, they run the SDFLoader as well.

[Anonymous]

Hi Chuck,

I think the 'WaitForSingleObject' function may work, at least from what I understood from the API link you gave me. Have you ever used these in the past? I have no idea how to implement this, could you give me a small example?

Thanks for the help.

[Anonymous]

Hi Chuck,

I think the 'WaitForSingleObject' function may work, at least from what I understood from the API link you gave me. Have you ever used these in the past? I have no idea how to implement this, could you give me a small example?

Thanks for the help.

[Anonymous]

I just wanted to let you know I haven't forgotten about you, and I'm not
ignoring you. I just don't have a ready-made example available at the moment.
I do have one at home, though, and I will try to post it this evening. I
haven't used ShellExecuteEx in the way you will need to use it in several
months, and I'm having a tough time remembering exactly how to do it (and I
don't have the time to research it). Otherwise I'd just whip you up an example
now.


"kb" wrote in message
news:f199c30.4@WebX.maYIadrTaRb...
> Hi Chuck,
> I think the 'WaitForSingleObject' function may work, at least from what I
understood from the API link you gave me. Have you ever used these in the past?
I have no idea how to implement this, could you give me a small example?
>
>
> Thanks for the help.

[Anonymous]

Wow. The way I did this before is not at all what I thought I remembered.
Here it is. It doesn't use exactly the same API functions I pointed you to,
but it works. I can't remember why I chose this route in lieu of
ShellExectuteEx.

Option Explicit

Private Const INFINITE As Long = &HFFFFFFFF
Private Const SYNCHRONIZE As Long = &H100000

Private Declare Function CloseHandle Lib "kernel32" ( _
ByVal hObject 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 WaitForSingleObject Lib "kernel32" ( _
ByVal hHandle As Long, _
ByVal dwMilliseconds As Long) As Long

Public Function ShellWait(strCommand As String) As Boolean
Dim lngProcID As Long
Dim lngHwnd As Long
ShellWait = False
lngProcID = Shell(strCommand, vbNormalFocus)
If lngProcID <> 0 Then
lngHwnd = OpenProcess(SYNCHRONIZE, 0, lngProcID)
If lngHwnd <> 0 Then
WaitForSingleObject lngHwnd, INFINITE
CloseHandle lngHwnd
ShellWait = True
End If
End If
End Function

"kb" wrote in message
news:f199c30.4@WebX.maYIadrTaRb...
> Hi Chuck,
> I think the 'WaitForSingleObject' function may work, at least from what I
understood from the API link you gave me. Have you ever used these in the
past? I have no idea how to implement this, could you give me a small
example?
>
>
> Thanks for the help.

[Anonymous]

Ok, so where does this code get placed? I've got a master module with all my functions in it, which I use to call any generic functions. But, when I place this code into it, or any other module for that matter, the 'Option explicit' part of this code ends up apart of any previous function. Only the actual 'Public' function is seperated. So where do I place the 'Option explicit' part of the code? In each module, or just the one that calls the shell command.
I appeciate all the help. It's been very insightful.

[Anonymous]

Lookup Option Explicit in the help file. Here is an extract:

If used, the Option Explicit statement must appear in a module before any procedures.

Regards - Nathan

[Anonymous]

Yes, but I'm not sure as to where to put the code. Do I put it in the module that calls the shell command? Or do I put it into a module that is run after the module with the shell command? I tried placing it into my master module, and then just calling it, but that doesn't work.

[Anonymous]

After I had a morning coffee, I opened my eyes and figured out what I was doing wrong. The code works just fine, thanks.

[Anonymous]

When you say it doesn't work, what do you mean? Does it raise an error?

I tried the following in AutoCAD 2000i VBA and it worked exactly as I would
expect it to.

Sub Test()
ShellWait "notepad.exe"
MsgBox "done"
End Sub

To answer your other question, I put it in a standard module by itself.


"kb" wrote in message
news:f199c30.9@WebX.maYIadrTaRb...
> Yes, but I'm not sure as to where to put the code. Do I put it in the module
that calls the shell command? Or do I put it into a module that is run after the
module with the shell command? I tried placing it into my master module, and
then just calling it, but that doesn't work.

[Anonymous]

I did put it in a Master Module, and then just call the function. What I was doing wrong was, I didn't have the shellwait ("somefile") as a string. I forgot the " around the file location and name. It seems to be working fine. I haven't had a chance to test it on a full scale app just yet, but I will in the next few days. Thanks, again.