Solved

Return value from vbscript to VBA

Posted on 2013-11-04
5
4,210 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
Comment Utility
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
Comment Utility
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
Comment Utility
Excellent solution. Thanks for your assistance. Perfect answer. If possible, would have granted 1000 points!
0
 
LVL 1

Author Comment

by:cynx
Comment Utility
@The_Barman Thanks for your assistance.
0
 
LVL 35

Expert Comment

by:[ fanpages ]
Comment Utility
:)

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

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
EXCEL Addin problem 7 23
Excel - Page layout - Margins 7 32
Merging multiple rows to one 22 33
How to Add / Edit Windows Menu 4 23
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
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.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
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.

772 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now