?
Solved

Return value from vbscript to VBA

Posted on 2013-11-04
5
Medium Priority
?
5,668 Views
Last Modified: 2013-11-11
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
0
Comment
Question by:cynx
  • 2
  • 2
5 Comments
 
LVL 24

Expert Comment

by:Steve
ID: 39623988
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
 
LVL 35

Accepted Solution

by:
[ fanpages ] earned 2000 total points
ID: 39624057
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
 
LVL 1

Author Closing Comment

by:cynx
ID: 39638841
Excellent solution. Thanks for your assistance. Perfect answer. If possible, would have granted 1000 points!
0
 
LVL 1

Author Comment

by:cynx
ID: 39638844
@The_Barman Thanks for your assistance.
0
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 39638908
:)

You're very welcome.  Good luck with the rest of your project.
0

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

621 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question