Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    May 2002
    Location
    London, Gtr London, England
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    SHELLing to DOS window (VBA / WORD 2000 /9-0-2720)

    He there,
    I have a DOS .exe ( written in Python) that gets data from an IP address. It works fine when called from a DOS Command window or when I start it from Windows using a small .bat file.
    However, when I call the .exe or the .bat from Word VBA the command window flashes up and then closes before the program can run?

    I use this command:

    Dim RetVal
    RetVal = Shell("C:GCLDatatagger.exe output.txt input.txt 212.161.86.222 1050", 1)

    and for the .bat

    Dim RetVal
    RetVal = Shell("C:GCLDatatagger.bat", 1)


    The parameters (output.txt input.txt 212.161.86.222 1050) are correct as they are the ones in the .bat so shouldn't matter.

    Any ideas how I can encourage the DOS window to remain around long enough to execute?

    Thanks for any help
    Richard Byrne

  2. #2
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SHELLing to DOS window (VBA / WORD 2000 /9-0-2720)

    I'm not quite sure what you're after here, but I'm presuming you want the VBA code to wait until the shell command has completed, before recommencing execution. I understand that this is quite doable, but not entirely straightforward. You might want to look at this article, or do a Google search using keywords:
    vba shell wait

    Alan

  3. #3
    New Lounger
    Join Date
    May 2002
    Location
    London, Gtr London, England
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SHELLing to DOS window (VBA / WORD 2000 /9-0-2720)

    Thanks for the reply.
    What I want to know is why the DOS box closes without running the program. Is this some feature of Word shelling to a DOS app?

  4. #4
    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: SHELLing to DOS window (VBA / WORD 2000 /9-0-2720)

    VBScript also offers a shell, which is the programmable equivalent of the Windows Start>Run box. If you get fed up with VBA's Shell, you could look into that. Here's a neat example from last Fall:
    <hr>Subject: Re: IP Address (Word 97SR2) [#190665]
    Poster: Andrew Cronnolly
    Posted on: 23-Oct-02 09:42

    Kevin, just another little tweak to make it more generic :

    <pre> Function IPAddr() As String
    Dim sIP As String
    Dim objShell As Object
    Dim objWshScriptExec As Object
    Dim objStdOut As Object
    Dim strline As String
    Dim intIPStart As Integer, intIPLen As Integer

    Set objShell = CreateObject("WScript.Shell")
    Set objWshScriptExec = objShell.Exec("ipconfig /all")
    Set objStdOut = objWshScriptExec.StdOut

    While Not objStdOut.AtEndOfStream
    strline = objStdOut.ReadLine
    If InStr(strline, "IP Address") Then
    intIPStart = InStr(strline, ": ") + 2
    intIPLen = Len(strline) - intIPStart
    sIP = Mid(strline, intIPStart, intIPLen)
    End If
    Wend

    IPAddr = sIP

    End Function </pre>


    This worked on NT where sIP = Mid(strline, 32, 16) would be required otherwise.

    I initially used an arrary because it is possible to have more that one IP addresss if more tham one network (including virtual) exists. If more then one IP address exists the above will only return the last one encountered by ipconfig, which may not be the correct one !<hr>
    There's some documentation on MSDN for the Windows Script Host's Shell object. IE 5.x or higher probably is required.

  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: SHELLing to DOS window (VBA / WORD 2000 /9-0-2720)

    Richard,
    Put your command in a batch file and run the batch file with a PAUSE at the end. That way you can see any error messges.


    Shell("TAGGER.BAT")

    tagger.bat:
    C:GCLDatatagger.exe output.txt input.txt 212.161.86.222 1050
    pause

    You may have fo fully qualifiy the output.txt and input.txt files:

    c:gcldatatagger.exe c:gcldataoutput.txt c:gcldatainput.txt 212.161.86.222 1050 <-- like this
    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
    Feb 2001
    Location
    Youngstown, Ohio, USA
    Posts
    705
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: SHELLing to DOS window (VBA / WORD 2000 /9-0-2720)

    If you have a fairly good idea on how long it would take for the shelled command to finish executing you can have a wait-type function follow the shell command. This is the approach that I used in one of my recent projects:<pre>Sub macWait10()
    NewHour = Hour(Now())
    NewMinute=Minute(Now())
    NewSecond = Second(Now() + 10 'Incorporate a 10-second delay
    WaitTime = TimeSerial(NewHour, NewMinute, NewSecond)
    Application.Wait WaitTime</pre>

    This bit of code was not my creation, but was found in the VBA Help files. When executed, this code will cause the application to pause until the internal clock reaches the time set. It's not so much as 'Wait 10 seconds' but 'Wait until the time is...'; however since the calculated 'Wait until' time is based on Now() it has the same effect.

    The sequence I use this function is:<pre>Call macCreateDirList 'executes batch file via Shell command
    Call macWait10 'gives Shell command time to finish before next command executes
    Call macRefreshDirList 'pulls the file created by macCreateDirList</pre>

    I've found this Wait function to be useful in other areas as well. The program this example comes from uses the list generated to open, print, and close a directory listing of Excel workbooks. When the code was allowed to run unrestricted the system would invariably crash, but by inserting a two-second delay between closing one file and opening the next I was able to give the computer a little more breathing room and it hasn't crashed since.

Posting Permissions

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