Excel x Web Scraping - FIPE table giving error

Asked

Viewed 287 times

1

The code below works perfect to return data by Excel with Web Scraping. I installed in the file the module VBA-JSON-2.3.1 - module name Jsonconverter.bas. I enabled the Microsoft Scripting Runtime reference. Return all brands, brands and models, etc. The error happens when I try to return the current value of a specific brand, model and year. Gives incompatible types error in this line, er if comment the line also gives error in the others.:

Planilha4.Cells(I, 1).Value = Item("referencia")

I believe it is because the return comes this way, no brackets at the beginning and at the end:

{"referencia": "maio de 2019", "fipe_codigo": "001267-0", "name": "Palio 1.0 ECONOMY Fire Flex 8V 4p", "combustivel": "Gasolina", "marca": "Fiat", "ano_modelo": "2013", "preco": "R$ 22.533,00", "key": "palio-2013", "time": 0.0, "veiculo": "Palio 1.0 ECONOMY Fire Flex 8V 4p", "id": "2013"}

I can’t seem to solve the problem. Below is the complete code.

Sub RetornaPrecoMarcaCarroModeloAno()
    Dim http        As Object
    Dim JSON        As Object
    Dim I           As Integer
    Dim Item        As Variant
    Set http = CreateObject("MSXML2.XMLHTTP")
    http.Open "GET", "http://fipeapi.appspot.com/api/1/carros/veiculo/21/4828/2013-1.json", False
    http.Send
    Set JSON = ParseJson(http.responseText)
    I = 2
    For Each Item In JSON
        Planilha4.Cells(I, 1).Value = Item("referencia")
        Planilha4.Cells(I, 2).Value = Item("fipe_codigo")
        Planilha4.Cells(I, 3).Value = Item("name")
        Planilha4.Cells(I, 4).Value = Item("combustivel")
        Planilha4.Cells(I, 5).Value = Item("marca")
        Planilha4.Cells(I, 6).Value = Item("ano_modelo")
        Planilha4.Cells(I, 7).Value = Item("preco")
        Planilha4.Cells(I, 8).Value = Item("key")
        Planilha4.Cells(I, 9).Value = Item("veiculo")
        Planilha4.Cells(I, 10).Value = Item("id")
        I = I + 1
    Next
    MsgBox ("complete")
    Set http = Nothing
    Set JSON = Nothing
End Sub
excel vba

1 answer

0

Good night,

I believe that in time no longer need the answer, for future queries the error is in the return of the query that does not present the "[ ]":

Response to the consultation (http://fipeapi.appspot.com/api/1/carros/veiculos/13.json): [{"fipe_brand": "Citro u00ebn", "name": "AIRCROSS 100 Years 1.6 Flex 16V Aut.", "brand": ... , "fipe_name": "ZX Volcane 3p and 5p"}]

Response to the consultation (http://fipeapi.appspot.com/api/1/carros/veiculo/59/5218/2014-1.json):

{"reference": "June 2020", "fipe_codigo": "005072-5", "name": "Golf GLX 2.0 4p", "fuel": "Gasoline", "brand": "VW - Volkswagen", "ano_modelo": "1995", "price": "R$ 8.204,00", "key": "golf-1995", "time": 0.0, "vehicle": "Golf GLX 2.0 4p", "id": "1995"}

Within the VBA-JSON-2.3.1 it understands as the beginning of the code "[{", so to resolve fix the input:

Set JSON = Parsejson("[" & http.responseText & "]")

I hope I helped! D

Browser other questions tagged excel vba

You are not signed in. Login or sign up in order to post.