如何在 vba 中使用此 API？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/39846190/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do I use this API in vba?
提问by D.Loo
so I've never used API's before and I'm fairly new to VBA. However, I'm trying to use the following API http://api.scb.se/OV0104/v1/doris/sv/ssd/START/FM/FM0401/MFIM1to automatically download data into VBA (The source is goo.gl/NgMBe , where you also can see the table), but I'm stuck.
所以我以前从未使用过 API，而且我对 VBA 还很陌生。但是，我正在尝试使用以下 API http://api.scb.se/OV0104/v1/doris/sv/ssd/START/FM/FM0401/MFIM1自动将数据下载到 VBA（来源是 goo. gl/NgMBe ，您还可以在其中看到表格），但我被卡住了。
Does anyone of you have an example on a working VBA with the same purpose? Of any links to books? I've tried googling, but stack is stack.
你们中有人有一个关于具有相同目的的工作 VBA 的例子吗？有书的链接吗？我试过谷歌搜索，但堆栈是堆栈。
采纳答案by Jbjstam
That's JSON data, which, for example, can be parsed using JScript:
这是 JSON 数据，例如，可以使用 JScript 对其进行解析：
VBA module:
VBA模块：
Option Explicit
Function GetData(myUrl As String) As String
Dim winHttpReq As Object
Set winHttpReq = CreateObject("Microsoft.XMLHTTP")
winHttpReq.Open "GET", myUrl, False
winHttpReq.Send
GetData = winHttpReq.ResponseText
End Function
''http://stackoverflow.com/questions/14822672/parsing-a-json-object-array-in-excel-vba#14823059
Sub OutputJsonStuff()
Dim FSO
Dim JScriptTS As TextStream
Dim JScriptText As String
Dim JSONdata As String
Set FSO = CreateObject("Scripting.FileSystemObject")
JSONdata = GetData("http://api.scb.se/OV0104/v1/doris/sv/ssd/START/FM/FM0401/MFIM1")
Set JScriptTS = FSO.OpenTextFile("jsonFunctions.js", ForReading)
JScriptText = JScriptTS.ReadAll
JScriptTS.Close
Dim oScriptEngine
Set oScriptEngine = CreateObjectx86("ScriptControl")
oScriptEngine.Language = "JScript"
oScriptEngine.Eval "var obj=(" & JSONdata & ")"
oScriptEngine.AddCode JScriptText
Dim valueTexts() As String, i
valueTexts = Split(oScriptEngine.Run("getValueTexts"), ";")
''output all value texts
For i = 1 To UBound(valueTexts)
Debug.Print valueTexts(i)
Next i
Dim title As String
Dim variablesCode As String
title = oScriptEngine.Run("getTitle")
variablesCode = oScriptEngine.Run("getVariablesCode")
Debug.Print title
Debug.Print variablesCode
DisposeScriptEngine
End Sub
''This is not really necessary if youre not on 64 bit: http://stackoverflow.com/questions/9725882/getting-scriptcontrol-to-work-with-excel-2010-x64/38134477
Public Sub DisposeScriptEngine()
CreateObjectx86 Empty
End Sub
Function CreateObjectx86(sProgID)
Static oWnd As Object
Dim bRunning As Boolean
#If Win64 Then
bRunning = InStr(TypeName(oWnd), "HTMLWindow") > 0
If IsEmpty(sProgID) Then
If bRunning Then oWnd.Close
Exit Function
End If
If Not bRunning Then
Set oWnd = CreateWindow()
oWnd.execScript "Function CreateObjectx86(sProgID): Set CreateObjectx86 = CreateObject(sProgID): End Function", "VBScript"
End If
Set CreateObjectx86 = oWnd.CreateObjectx86(sProgID)
#Else
If Not IsEmpty(sProgID) Then Set CreateObjectx86 = CreateObject(sProgID)
#End If
End Function
Function CreateWindow()
' source http://forum.script-coding.com/viewtopic.php?pid=75356#p75356
Dim sSignature, oShellWnd, oProc
On Error Resume Next
sSignature = Left(CreateObject("Scriptlet.TypeLib").GUID, 38)
CreateObject("WScript.Shell").Run "%systemroot%\syswow64\mshta.exe about:""<head><script>moveTo(-32000,-32000);document.title='x86Host'</script><hta:application showintaskbar=no /><object id='shell' classid='clsid:8856F961-340A-11D0-A96B-00C04FD705A2'><param name=RegisterAsBrowser value=1></object><script>shell.putproperty('" & sSignature & "',document.parentWindow);</script></head>""", 0, False
Do
For Each oShellWnd In CreateObject("Shell.Application").Windows
Set CreateWindow = oShellWnd.GetProperty(sSignature)
If Err.Number = 0 Then Exit Function
Err.Clear
Next
Loop
End Function
If you're not on a 64-bit machine, you can use CreateObject instead of CreateObjectx86.
如果您不在 64 位计算机上，则可以使用 CreateObject 而不是 CreateObjectx86。
jsonFunctions.JS (if you want, you can use string literals in the VBA-code instead of loading the functions from a file):
jsonFunctions.JS（如果需要，您可以在 VBA 代码中使用字符串文字，而不是从文件加载函数）：
function getValueTexts() {
var valueTexts = obj.variables[0].valueTexts;
var result = "";
for(var i = 0; i < valueTexts.length; i++) { result += valueTexts[i] + ";"; }
return result.substring(0, result.length-1);
}
function getTitle() {
return obj.title;
}
function getVariablesCode() {
return obj.variables[0].code;
}
/*
var obj;
loadObj();
WScript.Echo(getTitle());
WScript.Echo(getVariablesCode());
WScript.Echo(getValueTexts());
*/
/*
function getData() {
var data = "";
var url = 'http://api.scb.se/OV0104/v1/doris/sv/ssd/START/FM/FM0401/MFIM1'; // set your page url here
with (new ActiveXObject("Microsoft.XmlHttp")) {
open('GET', url, false);
send('');
data = responseText;
}
return data;
}
function loadObj(){
eval("obj = (" + getData() + ");");
}
*/
The code that is commented out in the .js-file is how I developed the script before calling it from VBA
在 .js 文件中注释掉的代码是我在从 VBA 调用它之前开发脚本的方式
Edit: This is helpful for understanding the data structure: http://jsonprettyprint.com
编辑：这有助于理解数据结构：http: //jsonprettyprint.com
