Results 1 to 14 of 14
  1. #1
    5 Star Lounger
    Join Date
    Mar 2001
    Location
    New York, NY
    Posts
    922
    Thanks
    2
    Thanked 12 Times in 11 Posts

    VBA Shell (Word XP sr1)

    I'm running a batch file from a template: rc = Shell (c:batchfile.bat), but the DOS window is not closing, despite the fact that the batch file ends with Exit on the final line. Is there a trick to closing this from VBA? Or do I need to cycle through Tasts and close it when I come to it?

    Thanks,
    Richard Barrett

  2. #2
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: VBA Shell (Word XP sr1)

    First, try NOT using Exit on the last line. I have not found it to be necessary with Windows 98 or 2000.

    Second, if the regular Shell method doesn't work, you could try the Windows Script Host's Shell.Run method, although this would entail more work and require that the system not have those components disabled.

  3. #3
    Gold Lounger
    Join Date
    Dec 2000
    Location
    Hollywood (sorta), California, USA
    Posts
    2,759
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA Shell (Word XP sr1)

    That's weird. It should not matter whether you have EXIT or not in your batch file. What's the OS?

    Just for grins, what happens when you execute the file like this:

    shell "cmd /c c:batchfile.bat"
    Kevin <IMG SRC=http://www.wopr.com/w3tuserpics/Kevin_sig.gif alt="Keep the change, ya filthy animal...">
    <img src=/w3timages/blackline.gif width=33% height=2><img src=/w3timages/redline.gif width=33% height=2><img src=/w3timages/blackline.gif width=33% height=2>

  4. #4
    5 Star Lounger
    Join Date
    Mar 2001
    Location
    New York, NY
    Posts
    922
    Thanks
    2
    Thanked 12 Times in 11 Posts

    Re: VBA Shell (Word XP sr1)

    Thanks. That didn't close it either. My O/S is XP. And if I add the code to cycle through Tasks and close cmd.exe when it finds it, it doesn't close either. If I run that same little block of code a few seconds later, it does close. I think that's probably a timing issue.

    The reason for all of this.... We have a little application that tracks the amount of time spend on each document in Word, Excel & Powerpoint. Some clients are billed for this time. Because we don't want to bill for time spent away from the desk with the document open, the time tracking app times out after 120 seconds of no keyboard or mouse activity. This constant monitoring consumes a lot of resource, but it's readily given up to any other application that needs it.

    The problem is that when this is run from a template at the DocumentOpen event, all Office applications become quite sluggish. If we run this application from Start|Run (Manually) we do not have this trouble at all. It seems (and I'm guessing) that starting an application with the Shell command from a template runs that application within Office's memory space. We thought that if we ran a batch file from the template, and let the batch file start the billing application, the batch file would end and Word would "let go."

    In fact, that does seem to help quite a bit, except for the little problem with closing the Command window. It doesn't remedy the problem completely; if this billing application is running on three or more Word documents, PowerPoint is very slow.

    Do you by any chance know anything about the way that the Shell command runs an application (batch file or otherwise) and how that differs from running a program directly from the command line (Start|Run)?

    Thanks,
    Richard

  5. #5
    Gold Lounger
    Join Date
    Dec 2000
    Location
    Hollywood (sorta), California, USA
    Posts
    2,759
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA Shell (Word XP sr1)

    Richard,

    Pitch the VBA Shell command and use the API ShellExecute function.

    It declares like this. Example taken from API-Guide.

    Private Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" (ByVal hwnd As Long, ByVal lpOperation As String, ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long
    Const SW_SHOWNORMAL = 1
    Private Sub Form_Load()
    'KPD-Team 1998
    'URL: http://www.allapi.net/
    'E-Mail: KPDTeam@Allapi.net
    'Send an E-Mail to the KPD-Team
    ShellExecute Me.hwnd, vbNullString, "mailto:KPDTeam@Allapi.net", vbNullString, "C:", SW_SHOWNORMAL
    End Sub
    Kevin <IMG SRC=http://www.wopr.com/w3tuserpics/Kevin_sig.gif alt="Keep the change, ya filthy animal...">
    <img src=/w3timages/blackline.gif width=33% height=2><img src=/w3timages/redline.gif width=33% height=2><img src=/w3timages/blackline.gif width=33% height=2>

  6. #6
    5 Star Lounger
    Join Date
    Mar 2001
    Location
    New York, NY
    Posts
    922
    Thanks
    2
    Thanked 12 Times in 11 Posts

    Re: VBA Shell (Word XP sr1)

    Thanks, Kevin.

    Are you suggesting this as a way to solve the resource issue?

    Richard

  7. #7
    Gold Lounger
    Join Date
    Dec 2000
    Location
    Hollywood (sorta), California, USA
    Posts
    2,759
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA Shell (Word XP sr1)

    I have no way of knowing definitely (since I cannot reproduce your problem -- my DOS box windows close), but it's worth a try.

    BTW, do you have this problem on all your XP boxes?
    Kevin <IMG SRC=http://www.wopr.com/w3tuserpics/Kevin_sig.gif alt="Keep the change, ya filthy animal...">
    <img src=/w3timages/blackline.gif width=33% height=2><img src=/w3timages/redline.gif width=33% height=2><img src=/w3timages/blackline.gif width=33% height=2>

  8. #8
    5 Star Lounger
    Join Date
    Mar 2001
    Location
    New York, NY
    Posts
    922
    Thanks
    2
    Thanked 12 Times in 11 Posts

    Re: VBA Shell (Word XP sr1)

    No, it does not. And testing that showed me something.... the Command box will close on its own; it just waits until the exe which it launched exits. In other words, the command box remains open until that particular instance of the bill tracking application exits, which is when the related document is closed.

  9. #9
    Gold Lounger
    Join Date
    Dec 2000
    Location
    Hollywood (sorta), California, USA
    Posts
    2,759
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA Shell (Word XP sr1)

    This is a little confusing. You've got a VB or VBA app (which is it?) that runs a batch file that starts a bill tracking app?

    Why not just use ShellExecute to start the BT app?

    edited: I see from above it's VBA, sorry.
    Kevin <IMG SRC=http://www.wopr.com/w3tuserpics/Kevin_sig.gif alt="Keep the change, ya filthy animal...">
    <img src=/w3timages/blackline.gif width=33% height=2><img src=/w3timages/redline.gif width=33% height=2><img src=/w3timages/blackline.gif width=33% height=2>

  10. #10
    5 Star Lounger
    Join Date
    Mar 2001
    Location
    New York, NY
    Posts
    922
    Thanks
    2
    Thanked 12 Times in 11 Posts

    Re: VBA Shell (Word XP sr1)

    Running it from the template in VBA slows other Office applications down considerably. There is about a five second delay in PowerPoint between the time you type and the text appears. Since this app needs to be launched at the DocumentOpen event, it has to be done from Word. We were hoping that launching it indrectly would remedy this, and it does help tremendously. But there's still a bit of sluggishness.

  11. #11
    Gold Lounger
    Join Date
    Dec 2000
    Location
    Hollywood (sorta), California, USA
    Posts
    2,759
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA Shell (Word XP sr1)

    Running ShellExecute from VBA slows Office?
    Kevin <IMG SRC=http://www.wopr.com/w3tuserpics/Kevin_sig.gif alt="Keep the change, ya filthy animal...">
    <img src=/w3timages/blackline.gif width=33% height=2><img src=/w3timages/redline.gif width=33% height=2><img src=/w3timages/blackline.gif width=33% height=2>

  12. #12
    5 Star Lounger
    Join Date
    Mar 2001
    Location
    New York, NY
    Posts
    922
    Thanks
    2
    Thanked 12 Times in 11 Posts

    Re: VBA Shell (Word XP sr1)

    That overstates it. What I'm saying is that the billing application which we are running uses a lot of resources because it is constantly monitoring for keystrokes or mouse movement in Word, Excel and PowerPoint, applications in which we track time spent on documents. (No keystrokes or mouse movement for 2 minutes means it times out.) When this application is run from VBA (Shell "billtrack.exe") Office programs slow down significantly., especially PowerPoint. This does NOT occur (sluggishness in Office apps) if billtrack.exe is run "manually" from Start|Run. In that case, no sluggishness occurs at all. I would add, too, that we have a document management system (iManage) in the mix as well.

    It seems, and I'm guessing a bit about this, that Shell runs the application in Office's memory space., while running billtrack.exe from the command line runs it in its own memory space. In the latter case, no sluggishness occurs in Office applications. My guess about memory space might be off, but the visible difference is quite apparent.

  13. #13
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: VBA Shell (Word XP sr1)

    My understanding of how Windows applications relate to one another is at the level of voodoo, but it does seem possible that because Office started the second application, it is the second application's "parent" and the second application could just like to phone home a lot.

    Is there a way to create a task for the operating system to start the other application, rather than starting it from inside Office? Maybe that's a question for Kevin. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    Now, as for this entire concept of billing for time spent in a document...

  14. #14
    5 Star Lounger
    Join Date
    Mar 2001
    Location
    New York, NY
    Posts
    922
    Thanks
    2
    Thanked 12 Times in 11 Posts

    Re: VBA Shell (Word XP sr1)

    My understanding of this is about the same of yours, but I the anecdotal evidence is there... the situation does not occur if I start the appllication from the command line. And there is never a problem when the DMS (iManage) isn't in the mix. iManage of course monitors document events via COM, and apparently these things don't play nice together.

    My next step is to use the API ShellExecute as Kevin suggested. Perhaps that will put enough distance between Word and the application.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •