JavaScript调用带有参数的VBA例程 [英] JavaScript to call a VBA routine with parameters
问题描述
-
我需要通过HTA的JavaScript将参数传递给Excel VBA代码。
- 我可以成功调用VBA函数，但无法正确传递字符串参数。
- JavaScript函数可以传递不同的字符串参数。
- 以下是简单和演示形式的代码。
- Excel-VBA代码
Sub subTest（strName As String）
MsgBox strName
End Sub
使用Javascript的HTA代码
<！DOCTYPE html>
< html>
< head>
< title> HTA< / title>
< hta：application
id =oHta
applicationname =htaNavi
border =1
borderstyle = normal
contextmenu =是
caption =导航器
sysmenu =是
WINDOWSTATE =最大化
>
< / head>
< body>
< input type =buttonvalue =testingonclick =funRun（'testng string'）/>
< input type =buttonvalue =testing secondonclick =funRun（'testng'）/>
< / body>
< script>
var objExl;
var objWb;
var objExl = new ActiveXObject（Excel.Application）;
objExl.Visible = true;
var objWb = objExl.Workbooks;
var strpath ='\path\testing_excel_web.xls';
objWb.Open（strpath）;
函数funRun（strName）
{
alert（strName）;
objWb.Application.Run（'testing_excel_web.xls！subTest（strName）'）;
}
< / script>
< / html>
我可以调用subTest，但是消息框填充strName作为字符串，但不将'测试字符串'作为文本。
我在想你想要的：
objWb.Application.Run（'testing_excel_web.xls！subTest（'+ strName +'）'）;
这样，变量值 strName 连接到您尝试运行的命令。
我对这个VBA函数的调用一无所知，所以我不知道你是否需要code code code code code code code code code code $ c $另外，为了安全起见，如果你的 strName 值包含，你应该使用这个：
objWb.Application.Run（'testing_excel_web.xls！subTest（'+ strName.replace（// g，\） +'）'）;
希望这样， strName 可以是
这里的测试一词是
或
这是一个报价
，它仍然可以工作。
关键是如果该字符串包含，则Javascript将失败，如果绝对不会包含，那么请忘记它。但是我认为这是需要的，因为 strName 的值中的任何将会将其作为参数传递。 / p>
I need to pass arguments to a Excel VBA code from JavaScript of HTA.
- I can successfully call VBA function, but unable to pass string arguments correctly.
- JavaScript function can pass different string arguments.
- Below is code in simple and demo form.
- Excel-VBA code
Sub subTest(strName As String) MsgBox strName End Sub
HTA code with Javascript
<!DOCTYPE html>
<html>
<head>
<title>HTA</title>
<hta:application
id="oHta"
applicationname="htaNavi"
border="1"
borderstyle = normal
contextmenu = "yes"
caption="Navigator"
sysmenu="yes"
WINDOWSTATE="maximize"
>
</head>
<body>
<input type="button" value="testing" onclick="funRun('testng string')" />
<input type="button" value="testing second" onclick="funRun('testng')" />
</body>
<script>
var objExl;
var objWb;
var objExl =new ActiveXObject("Excel.Application");
objExl.Visible = true;
var objWb = objExl.Workbooks;
var strpath = '\path\testing_excel_web.xls';
objWb.Open(strpath);
function funRun(strName)
{
alert(strName);
objWb.Application.Run('testing_excel_web.xls!subTest(strName)');
}
</script>
</html>
I can call subTest, but message box populates strName as string but not 'testing string' as text.
I'm thinking you want:
objWb.Application.Run('testing_excel_web.xls!subTest("' + strName + '")');
This way, the value of the variable strName is concatenated to the command you are attempting to run.
I know nothing about this calling of a VBA function, so I'm not sure if you need the " around the strName like I provided or not.
In addition, to be safe, in case your strName value contains ", you should use this:
objWb.Application.Run('testing_excel_web.xls!subTest("' + strName.replace(/"/g, "\"") + '")');
Hopefully with this, the value of strName could be
The word "testing" here
or
"Here's a quote"
and it will still work.
The point is that if the string contains ", the Javascript would/could fail. If it would absolutely never contain ", then forget about it. But I think it's needed since any " in the value of strName will break the passing of it as a parameter.
这篇关于JavaScript调用带有参数的VBA例程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
