Shell Command With Parameters

[atr140]

I am trying to run a program from excel using shell; however, the program will execute but seems to be failing and closing after the first parameter is passed.

Test code:

Sub Run_BXLRFDtst()
    Dim Dummy As Double
    Dim BXLRFD_Input_File_Name As String
    Dim BXLRFD_Output_File_Name As String

    Dim BXLRFD_Location As String

    BXLRFD_Location = "\\files\trans\__Standards-Examples-Templates\DP Design Programs\50\15 PS 3 (LFD)\PS3 v3.6.0.3\PS3.exe"

    BXLRFD_Input_File_Name = "C:\Users\arovenolt\Desktop\t\TEST.txt"
    BXLRFD_Output_File_Name = "C:\Users\arovenolt\Desktop\t\TEST_IN.OUT"
    
    Dummy = Shell(Chr(34) & BXLRFD_Location & Chr(34) & " " & Chr(34) & BXLRFD_Input_File_Name & Chr(34) & " " & "N" & " " & "N" & " " & Chr(34) & BXLRFD_Output_File_Name & Chr(34), 1)        

End Sub

In research, I realized there may be an issue passing file paths with spaces so I wrapped all variables with Chr(34) as advised in other threads.

Also of note, if I only execute the program using shell, I can manually enter the other information and the program functions fine.

Test Code:

Sub Run_BXLRFDtst()
    Dim Dummy As Double
    Dim BXLRFD_Input_File_Name As String
    Dim BXLRFD_Output_File_Name As String

    Dim BXLRFD_Location As String

    BXLRFD_Location = "\\files\trans\__Standards-Examples-Templates\DP Design Programs\50\15 PS 3 (LFD)\PS3 v3.6.0.3\PS3.exe"

    BXLRFD_Input_File_Name = "C:\Users\arovenolt\Desktop\t\TEST.txt"
    BXLRFD_Output_File_Name = "C:\Users\arovenolt\Desktop\t\TEST_IN.OUT"
    
    Dummy = Shell(Chr(34) & BXLRFD_Location & Chr(34) , 1)        

End Sub

Any help would be appreciated!

 

[cmowla]

I'm not at all sure this will work, but this is the approach that worked for me with VBScript code when calling a DOS batch file which took arguments (well, just about. It seems that I needed to create a shell object here to get it to at least start to work).

Sub Test_Shell()

    Dim Dummy As Double
    Dim BXLRFD_Input_File_Name As String
    Dim BXLRFD_Output_File_Name As String

    Dim BXLRFD_Location As String

    BXLRFD_Location = "\\files\trans\__Standards-Examples-Templates\DP Design Programs\50\15 PS 3 (LFD)\PS3 v3.6.0.3\"

    BXLRFD_Input_File_Name = "C:\Users\arovenolt\Desktop\t\TEST.txt"
    BXLRFD_Output_File_Name = "C:\Users\arovenolt\Desktop\t\TEST_IN.OUT"


'-------------New Code-------------------
On Error GoTo Restore_Directory 'Uncomment when trying to debug!  But here because it will potentially through off Windows directory temporarily if not done!
 
    With CreateObject("WScript.Shell")
        Dim s As String
        s = Chr(34) & " " & Chr(34)
        Dim directoryBefore As String
        directoryBefore = .CurrentDirectory
        .CurrentDirectory = BXLRFD_Location
        .Run "PS3.exe" & " " & Chr(34) & BXLRFD_Input_File_Name & s & "N" & s & "N" & s & BXLRFD_Output_File_Name & Chr(34)
Restore_Directory:
        .CurrentDirectory = directoryBefore
    End With

End Sub

Regarding the beginning of BXLRFD_Location, I'm not familiar with \\ in VBScript (or VBA) (I know that's required in JavaScript, for example.) So for my VBScript, I literally just used:

 BXLRFD = CreateObject("Scripting.FileSystemObject").GetAbsolutePathName(".")

to get the parent folder path of the EXE that I was going to call the batch file when the user clicked a button on it . . . and I had the batch file in a subfolder of the same parent folder. Maybe you will need to manually put in that folder path (the immediate parent) in for it (so that it starts with the Drive's letter). But it appears that it is what you have (obviously). But I don't know if it will recognize the \\. Not sure if this is a shared drive, etc.

 

[atr140]

Thanks for the reply. I tested the code with the same results. It still seems the Input File Name is not "reaching" the command prompt. I can see the program open (similar to when i only execute the program and not passing any input file name) and then it just closes. Not sure why. Very frustrating but I really appreciate the help.

Regarding the location of the .exe. You are correct in that is a shared network folder. I tried it both ways using the acutal path on my machine and the shard network path. Both seem to be able to execute the program so I left it the shared path for the hope I can get this functional for multiple users. Thanks Again!

 

[cmowla]

Yeah, what I did for my app was make it completely portable. So I didn't have that issue. (And part of that was to have the EXE in the same parent folder, etc.) But sorry I couldn't help. Hopefully someone else will step in and show us how this is done!

 

[cmowla]

I recalled having this website bookmarked VBScript Collection for System Administrators. It has no search, but I happened to find this page in one of the "Chapters": Scripting Files, Folders and Shared Folders using VBScript, he lists code for "Mapping a Network Share to a Local Folder" that you may either be able to use directly or tweak. (I don't know, as I don't have access to a shared drive to check.) If you manage to get it to work, please share the code here for others!

 

[atr140]

Same results unfortunately but thanks for passing it along!

 

[cmowla]

Well if worst comes to worst, you can have VBA programmatically make a copy of the entire folder (containing the .exe and its input files) to the drive of whoever is accessing it, run the EXE from there, and delete that copied folder afterwards . . . This way you can update the EXE as need be, and all users will still be accessing the most up-to-date version.

 

[atr140]

I dont know that the share folder is the issue. I tested with the .exe on my local drive with the same results. Also, I can open the program if I only call the .exe in the shell from the shared folder and my local, but when i pass the input file name, something seems to go wrong. This is possibly an issue with the program. I am not sure...