Solved

Return value from vbscript to VBA

Posted on 2013-11-04
5
4,498 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 500 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

803 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