Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Shelling Out to a batch file

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Posts
    60
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    What are the Excel Visual Basic command(s)I should use to Shell out to a DOS batch program? "Shell" doesn't seem to work.

    Mechanic

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Christchurch New Zealand
    Posts
    1,030
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    try
    Sub RunPcDownloadprodreview() '
    '
    '
    Dim TaskID As Long
    Dim hProc As Long
    Dim lExitCode As Long
    Dim access_Type As Variant
    Dim STILL_ACTIVE As Variant
    Dim Program As Variant

    access_Type = &H400
    STILL_ACTIVE = &H103
    ChDir (wkbname)
    Program = wkbname & "filename.bat"
    TaskID = Shell(Program, 1)
    hProc = OpenProcess(access_Type, False, TaskID)
    Do
    GetExitCodeProcess hProc, lExitCode
    DoEvents
    Loop While lExitCode = STILL_ACTIVE

    End Sub

    this will hold the window open till the bat file finishes.

  3. #3
    Board Regular
    Join Date
    Mar 2002
    Posts
    60
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I tried the code you gave me and receive an error on OpenProcess command. It tells me that it doesn't recognize the function or Sub program.

    Mechanic

  4. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Christchurch New Zealand
    Posts
    1,030
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    sorry mate add this to your code
    at the top in the declarations
    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

  5. #5
    MrExcel MVP Mark O'Brien's Avatar
    Join Date
    Feb 2002
    Location
    Columbus, OH, USA
    Posts
    3,530
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default


    How about something like this which uses the Shell function:

    '


    Dim sbatfilepath As String
    Dim sBatFile As String


    sBatFile = "test.bat"
    sbatfilepath = "C:Temp"

    ChDir sbatfilepath

    'You might want to put a test in to make sure that the .bat file exists
    If Environ("OS") <> "" Then
    ' It's NT
    Shell "cmd /C " & sBatFile, 1
    Else
    ' It's 95 or 98
    Shell "COMMAND.COM /C " & sBatFile, 1
    End If

    End Sub '


    HTH

  6. #6
    Board Regular
    Join Date
    Mar 2002
    Posts
    60
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thank you, that worked.
    Mechanic

  7. #7
    Board Regular
    Join Date
    Mar 2002
    Posts
    60
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    By the way,
    What does the "COMMAND" part of the Shell statement look like for Windows 2000?

    Mechanic

  8. #8
    MrExcel MVP Mark O'Brien's Avatar
    Join Date
    Feb 2002
    Location
    Columbus, OH, USA
    Posts
    3,530
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    It's "cmd" as well. You can test this out by going to Start|Run (or WINDOWS KEY + R, if you want to be flash) and type in "cmd" into the run dialogue box.

  9. #9
    Board Regular
    Join Date
    Mar 2002
    Posts
    60
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    What do the results of the Environ("OS")statement look like for Windows 2000?

    Mechanic

  10. #10
    MrExcel MVP Mark O'Brien's Avatar
    Join Date
    Feb 2002
    Location
    Columbus, OH, USA
    Posts
    3,530
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Windows 2000 is also known as NT5, so Environ("OS") returns:


    Windows_NT


    MS are good at hitting their own deadlines and keeping naming conventions going, hence the two different names for the OS.

    I'll need to test ME when I go home, but I'm pretty sure everything in the Win9x family, which ME is does not have Environment parameters. That's why the test is just Environ("OS")<>"".
    _________________
    [b] Mark O'Brien

    [ This Message was edited by: Mark O'Brien on 2002-04-16 11:42 ]

    [ This Message was edited by: Mark O'Brien on 2002-04-16 11:44 ]

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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