passing argument from vba to vbs

Question

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?

mehow,thanks for your useful edit – Pinky Aug 22 '13 at 10:23 — Pinky, Aug 22 '13 at 10:23

score 4 · Accepted Answer · edited May 23 '17 at 11:53

4

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

edited May 23 '17 at 11:53

Community

1
1

answered Aug 22 '13 at 10:37

Mark

2,184
1
16
28

Hi Mark Wylde, i tried your code. It showing error like "Method Run of object IWshShell3 failed". – Pinky Aug 22 '13 at 10:52
I've edited my original response to include double quotes in between the Something Else. Try again :) – Mark Aug 22 '13 at 10:55
Mark, parameter should be added with Run method. i tried code like wshShell.Run """" & SFilename & """" & "SomethingElse" . Now it displays MessageBox with Hello SomethingElse. thanks for help. – Pinky Aug 22 '13 at 11:12

score 0 · Answer 2 · answered Aug 22 '13 at 10:34

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

score 0 · Answer 3 · answered Mar 09 '21 at 21:31

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""

passing argument from vba to vbs

3 Answers3

Linked