asked on

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

Steve

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

ASKER CERTIFIED SOLUTION

[ fanpages ]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

cynx

ASKER

Excellent solution. Thanks for your assistance. Perfect answer. If possible, would have granted 1000 points!

cynx

ASKER

@The_Barman Thanks for your assistance.

[ fanpages ]

:)

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