Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Return value from vbscript to VBA

Posted on 2013-11-04
5
Medium Priority
?
5,280 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

664 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