Fetching values from ThingSpeak using vba
Thingspeak is web service that allows you to upload and store data from IoT devices.
Picture above shows cheap chinese IoT modules: a temp sensor and an esp8266-01 (25 mm x 15 mm)
Today I want to demonstrate a user defined function that downloads data from ThingSpeak to Excel so you can easily analyze and chart the data yourself, in excel.
The way this works is that using a simple http address with your channel and field number and parameters, you can easily fetch values from your feed.
The http address returns xml data, something like this:
The following user defined function lets you specify a channel, field and some more parameters. The function then removes xml tags and returns data to worksheet.
Remember to enter this as an array formula, 3 columns wide.
Function ThingSpeak(ch As Long, fld As Integer, prm As String)
'Add question mark if missing
If prm <> "" Then prm = "?" & prm
'Build url
url = "https://api.thingspeak.com/channels/" & ch & "/fields/" & fld & ".xml" & prm
On Error Resume Next
'Download data
Set http = CreateObject("MSXML2.XMLHTTP")
http.Open "GET", url, False
http.Send
tmp = http.responseText
'Check for errors
If Err <> 0 Then
MsgBox "Error fetching values"
On Error GoTo 0
Exit Function
End If
On Error GoTo 0
'Remove beginning xml data
tmp = Right(tmp, Len(tmp) - InStr(tmp, "") + 1)
'Clear xml tags with RemoveFromStr udf and then return values to worksheet
ThingSpeak = RemoveFromStr(tmp)
End Function
Function RemoveFromStr(tmp As Variant)
Dim tmp2 As Variant
'Clear xml tags using a regular expression
regexpattern = "<.*?>"
With CreateObject("vbscript.regexp")
.Global = True
.MultiLine = True
.IgnoreCase = False
.Pattern = regexpattern
Set Results = .Execute(tmp)
End With
If Results.Count <> 0 Then
With Results
For d = 0 To .Count - 1
tmp = Replace(tmp, .Item(d), ",")
Next
End With
End If
'Remove spaces
tmp = Replace(tmp, " ", "")
'Remove newline
tmp = Replace(tmp, vbLf, "")
'Replace T and Z with space character
tmp = Replace(tmp, "T", " ")
tmp = Replace(tmp, "Z", " ")
'Combine multiple commas
For i = 1 To Len(tmp)
If Mid(tmp, i, 1) = "," And Mid(tmp, i, 1) = Mid(tmp, i + 1, 1) Then
Else
tmp1 = tmp1 & Mid(tmp, i, 1)
End If
Next i
'Remove beginning and ending commas
If Left(tmp1, 1) = "," Then tmp1 = Right(tmp1, Len(tmp1) - 1)
If Right(tmp1, 1) = "," Then tmp1 = Left(tmp1, Len(tmp1) - 1)
'Split text into 1D array
tmp1 = Split(tmp1, ",")
'Count array
j = ((UBound(tmp1) - LBound(tmp1) + 1) / 3) - 1
'Build 2D array
ReDim tmp2(0 To j, 0 To 2)
ro = 0
co = 0
For i = LBound(tmp1) To UBound(tmp1)
tmp2(ro, co) = tmp1(i)
co = co + 1
If co = 3 Then
co = 0
ro = ro + 1
End If
Next i
'Return array
RemoveFromStr = tmp2
End Function
Download excel *.xlsm
Extract cell references from a formula
I am trying to build a regular expression that matches cell references in a formula. A regular expression is a [โฆ]
Count matching strings using regular expressions
A regular expression is a pattern containing specific characters to search for sub-strings in strings. The patterns are at first [โฆ]
Working with the LIKE OPERATOR
The LIKE operator allows you to match a string to a pattern in excel vba. The following characters are specifically [โฆ]
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
English
Spanish
