An example of getting WebHMI data from Excel

Getting data from event log

Below is a simple example of a query from Excel to the WebHMI API. In this example, data is read from the event log. This example uses an Active-X object and works only on Windows platforms. Additionally, a library is used for parsing JSON responses.

Download the example Файл:API Example.xlsm

Const URl As String = "http://192.168.0.1/api/event-data/1"
Sub xmlHttp()

    Dim xmlHttp As Object
    Set xmlHttp = CreateObject("MSXML2.ServerXMLHTTP.6.0")
    xmlHttp.Open "GET", URl, False
    xmlHttp.setRequestHeader "Content-Type", "text/xml"
    xmlHttp.setRequestHeader "Accept", "application/json"
    xmlHttp.setRequestHeader "Host", "192.168.0.1"
    xmlHttp.setRequestHeader "Cookie", " "
    xmlHttp.setRequestHeader "X-WH-APIKEY", "6E51E728896794EBF406E2F070BE7AFBE49E90D4"
    xmlHttp.setRequestHeader "X-WH-START", "1388948941"
    xmlHttp.setRequestHeader "X-WH-END", "1399208143"
    xmlHttp.send


    Dim JSON As New JSON

    Dim p As Object
    Set p = JSON.parse(xmlHttp.ResponseText)
    
    i = 2
    j = 1
    For Each Item In p ' rows
       If (j = 1) Then
          Cells(i, j).NumberFormat = "yyyy-mm-dd hh:mm:ss"
       End If
       If (j = 2) Then
          Cells(i, j).NumberFormat = "#.#" '"$#,##0.00_);[Red]($#,##0.00)"
       End If
       For Each Item2 In Item 'columns
         For Each Item3 In Item2
           If (j = 1 And i > 1) Then
             Cells(i, j) = (Item2(Item3) / 86400) + 25569
           Else
            Cells(i, j) = Item2(Item3)
           End If
           
           Cells(1, j) = Item3
         Next
        j = j + 1
       Next
         i = i + 1
         j = 1
       
    Next
   
 End Sub

Reading/Writing current register values

The second example shows how to read the current values ​​of the registers and write the new value into one of the registers. This example uses the https://github.com/VBA-tools/VBA-Web library

Download the example Файл:WebHMI registers read and write.xlsm

Sub GetRegisters()    
    Dim Client As New WebClient
    Client.BaseUrl = "http://192.168.1.1/api"
    
    Dim Request As New WebRequest
    Request.Resource = "register-values"
    
    Request.Method = WebMethod.HttpGet
    Request.RequestFormat = WebFormat.JSON
    Request.ResponseFormat = WebFormat.JSON
    
    Request.AddHeader "X-WH-APIKEY", "8DA00F5F9B42A8D070651C58F495DB1C3191AF19"
    
    Dim Response As WebResponse
    Set Response = Client.Execute(Request)
    Cells.Range("A2:C1000").Clear

    If Response.StatusCode = WebStatusCode.Ok Then
       ' Success, parse response
       Dim RegValue As Object
       Set RegValues = Response.Data
        
       i = 3
       For Each strKey In RegValues.Keys() ' rows
        Cells(i, 1) = RegValues(strKey)("r")
        Cells(i, 2) = RegValues(strKey)("v")
        Cells(i, 3) = RegValues(strKey)("s")
        i = i + 1
      Next
    Else
      ' Error, show message
      Cells(3, 1) = "Error"
      Cells(3, 2) = Response.StatusCode
      Cells(3, 3) = Response.Content
    End If
    
End Sub

Sub WriteValue()
    Cells(1, 9) = "Writing..."
    Dim Client As New WebClient
    Client.BaseUrl = "http://192.168.1.1/api"
    
    Dim Request As New WebRequest
    Request.Resource = "register-values/{Id}"
    
    Request.Method = WebMethod.HttpPut
    Request.RequestFormat = WebFormat.JSON
    Request.ResponseFormat = WebFormat.JSON
    
    Request.AddBodyParameter "value", Cells(2, 7)
    Request.AddUrlSegment "Id", Cells(1, 7)
    
    Request.AddHeader "X-WH-APIKEY", "8DA00F5F9B42A8D070651C58F495DB1C3191AF19"
    
    Dim Response As WebResponse
    Set Response = Client.Execute(Request)
    
    If Response.StatusCode = WebStatusCode.Ok Then
        ' Success
        Cells(2, 10) = "OK"
        Application.Wait (Now + TimeValue("00:00:01")) ' Need to wait for PLC to be updated
        Call GetRegisters
    Else
        ' Error, display error
        Cells(2, 10) = "ERROR: " + Response.Content
    End If
    
End Sub

Reading register log

The third example shows how to read data for registers with ID = 1 and 21 from the logged during the last 20 minutes. This example uses the https://github.com/VBA-tools/VBA-Web library

Download the example: Файл:WebHMI registers log read.xlsm

Sub GetRegistersLog()
    Dim Client As New WebClient
    Client.BaseUrl = "http://192.168.1.1/api"
    
    Dim Request As New WebRequest
    Request.Resource = "register-log"
    
    Request.Method = WebMethod.HttpGet
    Request.RequestFormat = WebFormat.JSON
    Request.ResponseFormat = WebFormat.JSON
    
    timeZoneOffset = 2

    ' Now is minus 20 minutes
    Request.AddHeader "X-WH-START", ((Now - DateSerial(1970, 1, 1)) * 86400 - 60 * 60 * timeZoneOffset - 60 * 20)

    ' Сейчас
    Request.AddHeader "X-WH-END", (Now - DateSerial(1970, 1, 1)) * 86400 - 60 * 60 * timeZoneOffset
    
    Request.AddHeader "X-WH-APIKEY", "72B17C6D7B956F9F964258DFFCBAE0B67B8DFF7A"
    Request.AddHeader "X-WH-REG-IDS", "1,21"
    
    Dim Response As WebResponse
    Set Response = Client.Execute(Request)
    Cells.Range("A4:J1000").Clear

    
    If Response.StatusCode = WebStatusCode.Ok Then
        ' Success, parse response
        Dim RegValue As Object
        Set RegValues = Response.Data
        
       i = 4
       For Each strKey In RegValues.Keys() ' rows
        Cells(i, 1) = strKey
        Cells(i, 2) = (strKey / 86400) + 25569 + (timeZoneOffset / 24)
        Cells(i, 2).NumberFormat = "yyyy-mm-dd hh:mm:ss"
          j = 1
          For j = 1 To RegValues(strKey).Count
            Cells(i, j * 3) = RegValues(strKey)(j)("r")
            Cells(i, j * 3 + 1) = RegValues(strKey)(j)("v")
            Cells(i, j * 3 + 2) = RegValues(strKey)(j)("s")
          Next
         i = i + 1
      Next
    End If
    
End Sub