Shell Command results in Runtime Error 5

[Sari]

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

[Kenneth Hobs]

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)

[Paul_Hossler]

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?

[Kenneth Hobs]

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.

[Sari]

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.

[Sari]

@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!

[Kenneth Hobs]

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

[Sari]

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!