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/MFIM1 to automatically download data into VBA (The source is goo.gl/NgMBe , where you also can see the table), but I'm stuck.
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.
That's JSON data, which, for example, can be parsed using JScript:
VBA module:
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.
jsonFunctions.JS (if you want, you can use string literals in the VBA-code instead of loading the functions from a file):
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
Edit: This is helpful for understanding the data structure: http://jsonprettyprint.com
