将参数从vba传递给vbs
问题描述:
我有一个vb脚本和带有命令按钮的excel页面。将参数从vba传递给vbs
VB脚本--- test.vbs
MsgBox("Hello world")
Excel中的VBA代码
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
当我点击Excel中的按钮,它执行VBScript
并显示MessageBox
。现在,我需要将TextBox
值从Excel VBA
到VBScript
并且该值应该与VBScript
MessagBox
一起显示。
我该怎么做?
答
您可以将参数发送到VBScript。看看下面的链接:
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
答
一个简单的测试脚本来处理未命名的参数(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
一个简单的VBA子调用.VBS与未命名的参数(含空格):
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
mehow,感谢您的有用编辑 – Pinky