Consulting

Results 1 to 8 of 8

Thread: Shell Command results in Runtime Error 5

  1. #1
    VBAX Regular
    Joined
    Apr 2017
    Posts
    14
    Location

    Shell Command results in Runtime Error 5

    Hi all,

    currently I'm busy with creating a link between Excel and Minitab by setting up a shell command.
    First I just pasted the Windows path of the Minitab program, which worked perfectly. After that I wanted to go one step further: Opening a specific Minitab-file.
    Therefore I created this code:

    Private Sub Image2_Click()
    'analysis - open Minitab

    Dim WinPath As Range
    Set WinPath = ThisWorkbook.Sheets("Hypothesis").Range("K7")

    If IsEmpty(WinPath.Value) = True Then
    MsgBox "Go to the dashboard and click the Six Sigma Tool button twice to enter your Minitab path."
    Else
    Call Shell(WinPath, 1)

    End If
    End Sub

    So the user fills in the Mintab path into cell K7, which then shall be opened.
    The error message I receive is:
    Run-time error 5: Invalid procedure call or argument

    Thanks in advance for your help!

    Greets,
    Sari

  2. #2
    VBAX Guru Kenneth Hobs's Avatar
    Joined
    Nov 2005
    Location
    Tecumseh, OK
    Posts
    4,910
    Location
    Likely it will fail if WinPath is incorrect. One thing you can do is to check if the file exists. e.g.
    If Dir(WinPath)<>"" then Shell(WInPath, 1)

  3. #3
    VBAX Wizard
    Joined
    Apr 2007
    Posts
    7,214
    Location
    After that I wanted to go one step further: Opening a specific Minitab-file.
    What is the command to open the file directly from the command line?
    ---------------------------------------------------------------------------------------------------------------------

    Paul

    Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  4. #4
    VBAX Guru Kenneth Hobs's Avatar
    Joined
    Nov 2005
    Location
    Tecumseh, OK
    Posts
    4,910
    Location
    Like most Shell() solutions, you need to embed quote marks around paths with space characters. It would go something like:
    Sub Main()
      Dim exe$, mtb$, q$, s$
      
      exe = "c:\Program Files (x86)\Minitab\Minitab 18\mtb.exe"
      mtb = "C:\Users\Ken\Downloads\CementData.mtb"
      q = """"
      
      If Dir(mtb) = "" Then
        MsgBox "mtb's path does not exist.", vbCritical, "Macro Ending"
        Exit Sub
      End If
      If Dir(exe) = "" Then
        MsgBox "mtb.exe's path does not exist.", vbCritical, "Macro Ending"
        Exit Sub
      End If
      
      s = q & exe & q & " " & q & mtb & q & " 1"
      'Debug.Print s
      '"c:\Program Files (x86)\Minitab\Minitab 18\mtb.exe" "C:\Users\Ken\Downloads\CementData.mtb" 1
      Shell s, vbNormalFocus
    End Sub
    
    
    'CementData.mtb
    '# https://support.minitab.com/en-us/datasets/regression-data-sets/cement-composition-data/
    'Retrieve "C:\Users\Ken\Downloads\CementData.MTW"
    'HISTOGRAM C1 C2;
    '    YFrequency;
    '    Bar;
    '    Panel.

  5. #5
    VBAX Regular
    Joined
    Apr 2017
    Posts
    14
    Location
    Hi Kenneth,
    as my file is created for different users, the path needs to be flexible. In cell K7 the path of the file will be noted, for example: C:\Windows\Desktop\Myfile.mpj
    This cell I setup as WinPath.
    Indeed there are no quote mark at the path. When I set Winpath with quotes, vba doesn't recognize this.

    @Paul,
    the command to open the file is:
    Call Shell(WinPath, 1)
    In this case Winpath is setup as range and the range refers to the the cell in which the user needs to type in the path of his file.
    I used the property menu of the file, to see the Winpath.

  6. #6
    VBAX Regular
    Joined
    Apr 2017
    Posts
    14
    Location
    @Kenneth,

    I also tried to add the quotes to the path in ell K7, but this is still not working.
    Do I need to change the Call Shell command? As when I try it only with the path of minitab itself, the program opens without any issues, but as soon as I fill in the name of a specific minitab-file, it results in error.

    Thanks for the help!

  7. #7
    VBAX Guru Kenneth Hobs's Avatar
    Joined
    Nov 2005
    Location
    Tecumseh, OK
    Posts
    4,910
    Location
    Sounds like you are still having problems as I explained in post #2 or you did not add the quotes as I demonstrated in post #4 as I built s.

    Paul asked you to post your command line string. I showed you how to get that via the Debug.Print line in post #4. It puts the result in VBE's Immediate Window. Of course the Dir() commands that I used there might have shown the problem. Another way to get your command line string correctly would be to do the Run manually. Win+R opens a dialog to Run shell commands. Of course you would need to add the quotes around the paths there as well. You could copy and paste the string from the Immediate Window to that Run Window. e.g.
    "c:\Program Files (x86)\Minitab\Minitab 18\mtb.exe" "C:\myfiles\MiniTab\MiniTab.MPJ" 1
    Of course you don't really need the 1 for an MPJ file. An mtb file would expect the number of runs. I had to guess what type of "file" you wanted to pass to mtb.exe in post #4.

    So, this is mostly the same as post #4 but I show you how to get the Desktop path and to run pass the MPJ file to mtb.exe.
    Sub Main()  
      Dim exe$, mpj$, q$, s$, d$, p$
      
      d = CreateObject("Wscript.Shell").Specialfolders("Desktop") & "\"
      exe = "c:\Program Files (x86)\Minitab\Minitab 18\mtb.exe"
      
      'mpj = d & "Myfile.mpj"
      [F7] = "C:\myfiles\MiniTab\MiniTab.MPJ"
      mpj = [F7]
      
      q = """"
      
      If Dir(mpj) = "" Then
        MsgBox "mpj's path does not exist.", vbCritical, "Macro Ending"
        Exit Sub
      End If
      If Dir(exe) = "" Then
        MsgBox "mtb.exe's path does not exist.", vbCritical, "Macro Ending"
        Exit Sub
      End If
      
      s = q & exe & q & " " & q & mpj & q & " 1"
      Debug.Print s
      '"c:\Program Files (x86)\Minitab\Minitab 18\mtb.exe" "C:\Users\Ken\Downloads\CementData.mtb" 1
      '"c:\Program Files (x86)\Minitab\Minitab 18\mtb.exe" "C:\myfiles\MiniTab\MiniTab.MPJ" 1
      Shell s, vbNormalFocus
    End Sub

  8. #8
    VBAX Regular
    Joined
    Apr 2017
    Posts
    14
    Location
    Hi Kenneth,

    thank you very much for your help! It indeed didn't recognize the WinPath.
    Thanks a lot! This is saving me a lot of investigation hours!!

    Have a great day!

Posting Permissions

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