Return value from vbscript to VBA

Hi Guys,

I am calling a vbscript from VBA for Excel and passing few parameters to the script. I need to return value from script to VBA. How can I achieve this? echo results in a popup message, also tried wscript.quit. I want the value from script in VBA routine.

Thanks.

VBA code below and script attached.

Public r_obj

Sub Call_Script()

Dim wsh As Object
Set wsh = CreateObject("WScript.Shell")

r_obj = wsh.Run("WScript C:\Users\xxx\Desktop\test\file.vbs ""C:\Users\xxx\Desktop\test\folder1"" ""C:\Users\xxx\Desktop\folder2"" ""12345"" ""True"" ")

End Sub

Open in new window

file.vbs
LVL 1
cynxAsked:
Who is Participating?
 
[ fanpages ]Connect With a Mentor IT Services ConsultantCommented:
Hi,

Depending on how the VBScript file is invoked from the MS-Excel Visual Basic for Applications [VBA] code, there are a number of options available to you, some of these are:

a) Redirect the output from the VBScript to an external (ASCII) text file, or
b) Write the return value to an external (ASCII text) file within the VBScript statements, or
c) Write the value to the console (stdout) window & capture this output from within MS-Excel, or
d) Invoke the VBScript file via the "CreateProcess" Application Programming Interface [API] Windows Software Development Kit [SDK] library routine & use the "ReadFile" API routine to retrieve the output.

I see above that you are creating a WScript.Shell object.

I would suggest implementing these changes, incorporating the use of CScript:

a) Instead of using WScript.Echo "<some text>" within your "file.vbs" VBScript file, use WScript.StdOut.WriteLine "<some text>" instead.

b) Add WScript.Quit(0) to the end of the VBScript.

Then, change the MS-Excel code to read something like this:

Public Sub Q_28285490()

  Dim objWScript_Shell                                  As Object
  Dim objWScript_Shell_Execute                          As Object
  
  Set objWScript_Shell = CreateObject("WScript.Shell")
  Set objWScript_Shell_Execute = objWScript_Shell.Exec("CScript //nologo ""C:\Users\xxx\Desktop\test\file.vbs"" ""C:\Users\xxx\Desktop\test\folder1"" ""C:\Users\xxx\Desktop\folder2"" ""12345"" ""True""")
  
  Debug.Print "Value read from VBScript file:" & objWScript_Shell_Execute.StdOut.ReadAll
  
  Set objWScript_Shell_Execute = Nothing
  Set objWScript_Shell = Nothing
  
End Sub

Open in new window


I have amended your original VBScript file & attached it here.

BFN,

fp.
Q-28285490.vbs
0
 
SteveCommented:
Is there a reason for not simply having the VBS in Excel VBA? They are pretty much interchangeable and so would save on having to pass variables.

You could write the variable(s) to a temp text file and open that in VBA.
The code below demostrates writing to text in a simple manner:
Dim outLoc: outLoc = "C:\temp\"
Dim fso, MyFile, FileName, TextLine, x
Set fso = CreateObject("Scripting.FileSystemObject")
FileName = outLoc & "temp.txt"

Set MyFile = fso.OpenTextFile(FileName, 2, True, -2)

for x = 1 to 10
MyFile.WriteLine "Line" & x
next

MyFile.Close

Open in new window

0
 
cynxAuthor Commented:
Excellent solution. Thanks for your assistance. Perfect answer. If possible, would have granted 1000 points!
0
 
cynxAuthor Commented:
@The_Barman Thanks for your assistance.
0
 
[ fanpages ]IT Services ConsultantCommented:
:)

You're very welcome.  Good luck with the rest of your project.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.