vba - 我的宏没有从网络查询返回所需的结果

Question

此宏使用工作表中的 LLC 名称，并在网站(使用 their API )中搜索最匹配的 LLC，并将该公司的信息(特别是 UBI)返回到工作表。

目前我的代码没有返回任何内容，我很困惑为什么。这是我所拥有的...

子测试()

    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

Answer 1

问题似乎出在处理返回的字符串

来自 URL 的示例字符串

{ "results" : { "Total": "1", "result": [ { "UBI": "602024039", "BusinessName": "THE BEN LOMAND ASSOCIATES LLC" } ] }}

预期结果

602024039

这是处理该字符串的示例

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

正如评论中提到的，处理字符串的方法有很多种。使用 Split 的替代方案可能是

    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

我假设结果将返回到事件工作表，从事件单元格开始。调整以满足您的需求。

还假设网络搜索可以返回多个结果