Skip to content

Instantly share code, notes, and snippets.

@jppg
Created August 13, 2020 18:16
Show Gist options
  • Save jppg/6aabeeeddc4a4a1d2e0b28114bc4c9b7 to your computer and use it in GitHub Desktop.
Save jppg/6aabeeeddc4a4a1d2e0b28114bc4c9b7 to your computer and use it in GitHub Desktop.
Option Explicit
Sub GETLEIMacro()
Dim rng As Range, lei As Range
Dim objRequest As Object
Dim strUrl As String
Dim blnAsync As Boolean
Dim strResponse As String
Dim strLeiName As String
Dim rnum As Integer
Set rng = Range("A3:A3000")
rnum = 3
For Each lei In rng
If HasContent(lei) = False Then
GoTo endloop
End If
Set objRequest = CreateObject("MSXML2.XMLHTTP")
strUrl = "https://leilookup.gleif.org/api/v2/leirecords?lei=" + lei
blnAsync = True
With objRequest
.Open "GET", strUrl, blnAsync
.SetRequestHeader "Content-Type", "application/json"
.Send
'spin wheels whilst waiting for response
While objRequest.readyState <> 4
DoEvents
Wend
strResponse = .ResponseText
Dim Result() As String
Result = Split(strResponse, "$")
Result = Split(Result(2), "}")
strLeiName = Result(0)
strLeiName = Replace(strLeiName, """:""", "")
strLeiName = Replace(strLeiName, """", "")
Cells(rnum, 2) = strLeiName
End With
rnum = rnum + 1
Next lei
endloop:
End Sub
Public Function HasContent(text_box As Object) As Boolean
HasContent = (Len(Trim(text_box.Value)) > 0)
End Function
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment