CODEWITHSUNDEEP
vbavbscript
Pinky
Pinky

Reputation: 701

passing argument from vba to vbs

I have one vb script and excel page with command button.

vb script---test.vbs

MsgBox("Hello world")

excel vba code

Private Sub CommandButton1_Click()
  Dim SFilename As String
    SFilename = "C:\Users\mkamaraj\Desktop\test.vbs" 'Change the file path

    ' Run VBScript file
    Set wshShell = CreateObject("Wscript.Shell")
    wshShell.Run """" & SFilename & """"
End Sub

When I click the button in Excel it executes the VBScript and the MessageBox is displayed. Now, I need to pass the TextBox value from Excel VBA to VBScript and that value should be displayed with that VBScript MessagBox.

How can I do that?

Upvotes: 2

Views: 10890

Answers (3)

Trezore
Trezore

Reputation: 11

I found this to be straight forward, simple and less double quotes.
Need to remember to pass within the string command the spaces between "your.vbs arg1 arg2"
And you shouldn't need to have each arg encapsulated with double quotes.

sCmd = "\\your\VBS\file\path\selectArgs.vbs"
arg1 = "ThisArg1"
arg2 = "ThisArg2"
sRun = sCmd & " " & arg1 & " " & arg2
Dim wsh As Object
Set wsh = CreateObject("Wscript.Shell")
wsh.Run "" & sRun & ""

'.Run will look like this:
'wsh.Run ""\\your\VBS\file\path\selectArgs.vbs ThisArg1 ThisArg2""

Upvotes: 0

Mark
Mark

Reputation: 2221

You can send Parameters to the VBScript. Have a look at the link below:

Can I pass an argument to a VBScript (vbs file launched with cscript)?

VBScript:

MsgBox("Hello " & WScript.Arguments(0))

VBA:

Private Sub CommandButton1_Click()
  Dim SFilename As String
    SFilename = "C:\Users\mkamaraj\Desktop\test.vbs " & """Something Else""" 'Change the file path

    ' Run VBScript file
    Set wshShell = CreateObject("Wscript.Shell")
    wshShell.Run """" & SFilename & """"
End Sub

Upvotes: 4

Ekkehard.Horner
Ekkehard.Horner

Reputation: 38775

A simple test script to deal with unnamed arguments (showparms.vbs):

Option Explicit

Function qq(s)
  qq = """" & s & """"
End Function

Function Coll2Arr(oColl, nUB)
  ReDim aTmp(nUB)
  Dim i : i = 0
  Dim e
  For Each e In oColl
      aTmp(i) = e
      i       = i + 1
  Next
  Coll2Arr = aTmp
End Function

Dim oWAU  : Set oWAU = WScript.Arguments.Unnamed
Dim aWAU  : aWAU     = Coll2Arr(oWAU, oWAU.Count - 1)
Dim sArgs : sArgs    = "no arguments given"
If -1 < UBound(aWAU) Then
    sArgs = qq(Join(aWAU, """ """))
End If
MsgBox sArgs ' WScript.Echo sArgs

A simple VBA sub to call a .VBS with unnamed arguments (containing spaces):

Option Explicit

Sub callVBS()
  Dim sFSpec As String: sFSpec = "p:\ath\to\showparms.vbs"
  Dim sParms As String: sParms = "one ""t w o"" three"
  Dim sCmd As String: sCmd = """" & sFSpec & """ " & sParms
  Dim oWSH: Set oWSH = CreateObject("WScript.Shell")
  oWSH.Run sCmd
End Sub

Upvotes: 0

Related Questions