Created
August 13, 2020 18:16
-
-
Save jppg/6aabeeeddc4a4a1d2e0b28114bc4c9b7 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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