Results 1 to 7 of 7
  1. #1
    siragdj
    Guest

    Reading output from shell (English/Excel VBA/Office 2000)

    I regularly use a shell command to make external calls out of Excel's VBA, but the way I do it I can't get results from the external code passed directly back into Excel/VBA. I have managed to redirect output into a file, read the file into an Excel worksheet, parse it for the required information, and then delete the worksheet, but you can see that this is a pain and has many opportunities to fail.

    Is there a more straight forward way to make an external call and retrieve the output directly in the form of a string?

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Reading output from shell (English/Excel VBA/Office 2000)

    If you use the Command() function and assign its result to a variant, it will give you whatever the contents of the command line argument was. Don't even try to assign it directly to a string variable because it doesn't seem to work when you do that. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>
    Charlotte

  3. #3
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Reading output from shell (English/Excel VBA/Office 2000)

    Charlotte

    Could you give me an example. I am currently working on a shelled command that produces two text files. I check to see if the files were created, and if so I will know that the shelled process has ended.

    Your approch is better to indicate when the process has ended. Maybe pass a 0 when all is well, or a 1 when some error had stopped it.

    TIA

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Reading output from shell (English/Excel VBA/Office 2000)

    We're talking about two different things. I misunderstood the original question. The Command() function will return whatever was in the command line argument, but it won't return the result of running that command line argument.

    If you want to deal with the output from the command, you're probably going to have to abandon Shell and use automation instead. Otherwise, all Shell returns is a Task ID if the operation was successful.
    Charlotte

  5. #5
    siragdj
    Guest

    Re: Reading output from shell (English/Excel VBA/Office 2000)

    Here is the VB code I use to run an external code when I want the VB to wait for it to finish. I have another routine that I use when I want the VB to start the process and then proceed. As Charlotte pointed out, however, this routine doesn't return much. If my external routine sends output to the DOS window I'd like to capture that output and return it into the VB code.

    David

    Declare Function OpenProcess Lib "kernel32" _
    (ByVal dwDesiredAccess As Long, _
    ByVal bInheritHandle As Long, _
    ByVal dwProcessId As Long) As Long

    Declare Function GetExitCodeProcess Lib "kernel32" _
    (ByVal hProcess As Long, _
    lpExitCode As Long) As Long

    Sub SyncShell(Program As String, Windowstyle As Integer)
    ' executes a DOS shell synchronously
    Dim TaskID As Long
    Dim hProc As Long
    Dim lExitCode As Long

    ACCESS_TYPE = &H400
    STILL_ACTIVE = &H103

    'Start the task
    TaskID = Shell(Program, Windowstyle)

    ' Get the process handle
    hProc = OpenProcess(ACCESS_TYPE, False, TaskID)

    Do ' until process is done
    GetExitCodeProcess hProc, lExitCode
    DoEvents ' allowing other system events to occur while waiting
    Loop While lExitCode = STILL_ACTIVE
    End Sub

    'Sub test()
    ' MsgBox "start"
    ' 'testTaskID = Shell("calc", 1)
    ' SyncShell "calc", 1
    ' MsgBox "done"
    'End Sub

  6. #6
    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: Reading output from shell (English/Excel VBA/Office 2000)

    Might be simpler: (1) manipulate the text file in memory using either the old Open/Line Input syntax or the more modern FileSystemObject/TextStream syntax; or (2) pass the text through the clipboard.

  7. #7
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Reading output from shell (English/Excel VBA/Office 2000)

    Your work is well beyond my skill level, but I'm curious; if ACCESS_TYPE = &H400 and STILL_ACTIVE = &H103 are the two returns you are inspecting can your external routine write them to and all routines read them from the Command Environment space? In VBA this would be the ENVIRON function.
    -John ... I float in liquid gardens
    UTC -7ąDS

Posting Permissions

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