My macro is not returning the desired results from a web request
This macro uses the LLC name in the sheet and looks up the website (using their API ) to better match the LLC and returns this company information, specifically UBI, back to the worksheet.
Currently my code is not returning anything and I don't understand why. Here's what I have ...
Sub TEST ()
Dim w As Worksheet
Set w = ActiveSheet
Dim Search As String
Search = Range("E2")
Dim url As String
url = "http://www.sos.wa.gov/corps/search_results.aspx?name_type=contains&name=" & Search & "&format=json"
Dim Http As New WinHttpRequest
Http.Open "GET", url, False
Http.Send
Dim resp As String
resp = Http.ResponseText
Dim slines As String
Dim Values As Variant
Values = Split(resp, ",")
For i = 0 To UBound(Values)
slines = Values(i)
w.Cells(i + 2, 20) = Replace(Values(1), Chr(34) & Chr(173) & Chr(176) & Chr(135) & Chr(133) & Chr(72), "")
Next i
End Sub
source to share
It would seem that the problem is in handling the returned string
Example string from URL
{"results": {"Total": "1", "result": [{"UBI": "602024039", "BusinessName": "THE BEN LOMAND ASSOCIATES LLC"}}}}
Expected Result
602024039
Here is an example of processing this line
Sub TEST()
Dim i As Long, j As Long
Dim w As Range
Dim Search As String
Dim url As String
Dim Http As New WinHttpRequest
Dim resp As String
Search = Range("E2")
url = "http://www.sos.wa.gov/corps/search_results.aspx?name_type=contains&name=" & Search & "&format=json"
Http.Open "GET", url, False
Http.Send
resp = Http.ResponseText
Set w = ActiveCell
i = InStr(resp, "{ ""UBI"":")
Do While i > 0
resp = Mid$(resp, i)
i = InStr(8, resp, """")
j = InStr(i + 1, resp, """")
w = Mid$(resp, i + 1, j - i - 1)
i = InStr(2, resp, "{ ""UBI"":")
Set w = w.Offset(1, 0)
Loop
End Sub
As mentioned in the comments, there are many ways to handle a string. An alternative using Split
could be
Dim values() As String
values = Split(resp, "{ ""UBI"": """)
For i = 1 To UBound(values)
j = InStr(values(i), """")
w = Left$(values(i), j - 1)
Set w = w.Offset(1, 0)
Next
I assumed the result should be returned to the active sheet starting from the active cell. Adjust according to your needs.
It is also assumed that web search can return multiple results
source to share