-1

I am executing VBS files from VBA using Wscript.Shell and passing some arguments to VBS. Now, I want the Wscript.Shell wait for the return of the arguments/variable from VBS. Any lead, how do I achieve this? Thanks

VBA code is:

Sub LaunchScript()

    Dim wsh As Object
    Dim waitOnReturn As Boolean: waitOnReturn = True
    Dim windowStyle As Integer: windowStyle = 1 
    Dim scriptPath As String, MyMsg As String
    Dim Return_VBS_Val
    
    scriptPath = "C:\path\x.vbs"
    MyMsg = "I got into VBS"
     
     Set wsh = VBA.CreateObject("WScript.Shell")
     wsh.Run """" & scriptPath & """ """ & MyMsg & """", windowStyle, waitOnReturn
    
    'Return_VBS_Val = argument from x.vbs  >>>>> I want to pull the argument from VBS here and store it in this VBA Variable

End sub

x.vbs code:

Dim Return_VBS_Val 

'something to do here

Msgbox Wscript.Arguments(0)

Return_VBS_Val = "Hello VBA"
Goku
  • 89
  • 7
  • Do you know you can execute commands from inside VBA without needed to use `WScript.Shell`? Also, if you pass the arguments into the VBS you already have them available in the VBA? – user692942 Sep 28 '21 at 18:28
  • You only need to escape the literal quotes that are part of the command by doubling them, so say you're trying to execute `cscript.exe "C:\path with spaces\x.vbs" "I got into VBS"` then when run with `wsh.Run()` the string would be `"cscript.exe ""C:\path with spaces\x.vbs"" ""I got to VBS"""` or with variables, it would be `"cscript.exe """ & scriptPath & """ """ & MyMsg & """"`. You also cannot execute VBS directly you should be calling through `cscript.exe`. – user692942 Sep 28 '21 at 18:45
  • To pass an argument to VBScript is simple. To send an argument back to VBA, I think it cannot be done in the way you try. The last code line of the VBA procedure should be the one running the script. Otherwise, Excel wait for the rest of the code to be run. To sent back a function result or something else, VBS in discussion must create an object from the existing Excel session and run a procedure of the respective workbook, using the necessary argument as Sub parameter. – FaneDuru Sep 28 '21 at 18:58
  • 1
    Does this answer your question? [WScript.Shell.Exec - read output from stdout](https://stackoverflow.com/q/32492879) – user692942 Sep 28 '21 at 18:59
  • Does this answer your question? [Capture Output of WScript Shell Object](https://stackoverflow.com/a/50631335) – user692942 Sep 28 '21 at 19:05
  • 1
    There is no reason to do this. Sounds like an XY Problem. If you tell us what you are actually trying to do, instead of how you are trying to do it, then we can help you better. – HackSlash Sep 28 '21 at 23:02
  • @HackSlash Thanks for your response, Tim's answer has satisfied my question! – Goku Sep 29 '21 at 08:50

1 Answers1

1

Example using StdOut:

Sub LaunchScript()

    Dim scriptPath As String, MyMsg As String
    
    scriptPath = "C:\path\x.vbs"
    MyMsg = "I got into VBS"
     
    Debug.Print "Ouput: " & ExecShellCmd("cscript.exe """ & _
                         scriptPath & """ """ & MyMsg & """")
    
End Sub

Public Function ExecShellCmd(FuncExec As String) As String
    ExecShellCmd = VBA.CreateObject("WScript.Shell") _
                    .exec("cmd.exe /c " & FuncExec).stdout.readall
End Function

VBS file:

Msgbox  Wscript.Arguments(0) 'read input
Wscript.Echo "Hello VBA"   'pass output
Tim Williams
  • 154,628
  • 8
  • 97
  • 125
  • 1
    Just another take on [Capture Output of WScript Shell Object](https://stackoverflow.com/a/50631335). – user692942 Sep 28 '21 at 19:06
  • 1
    Pretty much everything here is “just another take on xxxx” if you want to look at it that way… – Tim Williams Sep 28 '21 at 19:55
  • Exactly, so adding another is helping who? Let's face it we are talking about languages that have been around for 20+ years, if how to read standard output from a command in VBScript hasn't been answered by now it never will. You just add to the problem when you answer questions like this. – user692942 Sep 28 '21 at 20:12
  • @TimWilliams Thanks Tim, that helped me to move on! – Goku Sep 29 '21 at 08:48