64 位 Excel VBA 调用 JavaScript 函数 [英] 64 bit Excel VBA to call a JavaScript function
问题描述
我需要使用 VBA ScriptControl 对象来调用 JavaScript 函数,但它给了我一个类未注册"错误.我从 Tools->References 添加了 Microsoft Script Control 1.0我需要调用 JavaScript 从这个 Rest API 用于计算 Excel 宏中的值.
I need to use a VBA ScriptControl object to call a JavaScript function , but it gave me a "Class Not Registered" error. I have added Microsoft Script Control 1.0 from Tools->References I need to call JavaScript to get a JSON object from this Rest API to calculate values in an Excel Macro.
这篇文章告诉我ScriptControl是用来使用的仅限 32 位.我正在使用 64 位 Excel.我也尝试使用这个 link 中提到的方法,但它没有用,因为 VBA 没有t 识别 ActiveXObject
This post told me that ScriptControl is for use in 32 bit only. I am using 64 bit Excel. I also tried using the method mentioned in this link but it didn't work since VBA doesn't recognise the ActiveXObject
我的Excel VBA代码调用一个简单的JS函数:
My Excel VBA code to call a simple JS function:
Private Sub CommandButton1_Click()
Dim jsObj As MSScriptControl.ScriptControl, result As Integer
Set jsObj = CreateObject("MSScriptControl.ScriptControl")
jsObj.Language = "JScript"
With jsObj
.AddCode ("function prod1(a,b){return a*b;}")
result = .Run("prod1", 2, 3)
End With
MsgBox result
End Sub
我收到了一行 Set jsObj = CreateObject("MSScriptControl.ScriptControl") 的类未注册错误有没有其他方法可以从 VBA 调用 JavaScript 函数?还是我遗漏了什么?
I am getting a class not registered error for the line Set jsObj = CreateObject("MSScriptControl.ScriptControl") Is there an alternate way to call a JavaScript function from VBA? Or am I missing something?
推荐答案
不需要 ScriptControl 对象:您可以使用 XMLHTTP 和 VBA-JSON.
There's no need for a ScriptControl object: you can use XMLHTTP and VBA-JSON.
例如:
Public Sub Tester()
'Import the "JsonConverter.bas" file from
' https://github.com/VBA-tools/VBA-JSON
'and add a reference to the Microsoft Scripting Runtime library
Dim http As Object, JSON As Object, i As Integer, o As Object, k
Set http = CreateObject("MSXML2.XMLHTTP")
http.Open "GET", "https://www.alphavantage.co/query?" & _
"function=CURRENCY_EXCHANGE_RATE&from_currency=USD" & _
"&to_currency=JPY&apikey=demo", False
http.Send
Debug.Print http.responseText
Debug.Print "-----------------------------------"
Set JSON = ParseJson(http.responseText)
Set o = JSON("Realtime Currency Exchange Rate")
For Each k In o.keys
Debug.Print k, o(k)
Next k
End Sub
这篇关于64 位 Excel VBA 调用 JavaScript 函数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!