I've put together a spreadsheet that works in Excel for Windows that downloads one ICP's details and populates cells with the following parameters with good consistency:
ICP, Unit, Number, Street, Suburb, Town, Region, Post Code, Network, GXP, Reconciliation Type, Generation, Fuel, Network Price Category, Loss Factor, Trader, MEP, Profile, AMI flag.
Simply call the function "=getICPdetail( cell with icp in it )"
This places the string in a cell.
There is also another macro that coverts text to columns (I didn't get JSON parseing functioning).
Drop me a note and I'll make the sheet available.
I've also put together a function that accepts street addresses and returns ICP's if that is of interest...
Also have some macros that work on excel for Mac.
Regards, Malcolm
-------------GETICPDETAIL FUNCTION FOR WINDOWS EXCEL------------------
Public Function getICPdetail(ICP As String) As Variant
Dim xmlhttp As Object
Dim strURL As String
Dim CurrentYear As String
Dim x As String
Dim key As String
On Error GoTo FuncFail
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
strURL = "https://emi.azure-api.net/ICPConnectionData/?id="
key = "the API key goes here"
Set xmlhttp = CreateObject("MSXML2.XMLHTTP.6.0")
With xmlhttp
.Open "get", strURL & ICP, False, key
.setRequestHeader "Ocp-Apim-Subscription-Key: ", key
.send
x = .ResponseText
End With
getICPdetail = x
Set xmlhttp = Nothing
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Exit Function
FuncFail:
getICPdetail = CVErr(xlErrNA)
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Function
----------------------------------------------------------
