VBA Macro fetch information on site

Question

VBA Macro fetch information on site

Navigation

#1 by (5 votes)

9

I need to search for information on a real estate site and bring it to excel. I made the macro below:

Sub zap()

Set ie = CreateObject("InternetExplorer.Application")

With ie
.Navigate "http://www.zap.com.br/imoveis/fipe-zap/"
.Visible = True
End With

Do While ie.Busy
Loop
Do While ie.readyState <> 4
Loop
Call aguarda

Dim CurrentWindow As HTMLWindowProxy: Set CurrentWindow = ie.Document.parentWindow

'SELECIONA O NUMERO DE DORMITORIOS:  (0)=Todos  (1)=1 dormitório  (2)=2 dormitórios  (3)=3 dormitórios  (4)=4 dormitórios
ie.Document.All.Item("ctl00$ContentPlaceHolder1$quartosFipe")(0).Checked = True

'SELECIONA O PERÍODO:  (0)=Ano corrente  (1)=Últimos 12 meses  (2) =Todo o período
ie.Document.All.Item("ctl00$ContentPlaceHolder1$")(2).Checked = True

'SELECIONA SE É VENDA OU ALUGUEL:  (0)=Venda  (1)=Aluguel
ie.Document.All.Item("ctl00$ContentPlaceHolder1$radTransacao")(0).Checked = True

'SELECIONA A CIDADE:  (0)=FipeZap Ampliado  (1)=FipeZap Composto  (2)=Belo Horizonte  (3)=Brasilia  (4)=Curitiba  (5)=Florianopolis  (6)=Fortaleza  (7)=Niteroi  (8)=Porto Alegre  (9)=Recife  (10)=Rio de Janeiro  (11)=Salvador  (12)=Santo Andre  (13)=São Bernardo do Campo  (14)=São Caetano do Sul  (15)=São Paulo  (16)=Vila Velha  (17)=Vitoria
ie.Document.All.Item("ctl00$ContentPlaceHolder1$ddlCidadeIndiceFipeZap")(10).Selected = True

'ATUALIZA O GRÁFICO
ie.Document.parentWindow.execScript ("javascript:AtualizaGraficoIndice()")

    For Each element In ie.Document.getElementsByTagName("circle")
    x = element.getAttribute("cx")
    y = element.getAttribute("cy")
    Workbooks("FipeZap.xlsm").Worksheets("Plan1").Range("A1000000").End(xlUp).Offset(1, 0).Value = x
    Workbooks("FipeZap.xlsm").Worksheets("Plan1").Range("B1000000").End(xlUp).Offset(1, 0).Value = y
Next



End Sub

Sub Awaits:

Sub aguarda()
newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 1
waitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait waitTime
End Sub

However, not all values returned by the site hit the values inspected in the chart. Although column B hits the values, the values in column A do not match either.

Does anyone know if it's a macro problem (I believe not) or am I letting some of the settings that ZAP Real Estate is doing on the page before plotting the chart?

Editing

Personal, the code just opens the ie, makes the selection of the criteria in the chart and inserts into excel.

Follow the debug image of the zap page and return the macro.

One of the information is not faithfully brought. See the screen below, the underlined information is brought correctly, while the information circled comes wrong.

link

excel vba internet-explorer

asked by anonymous 06.03.2014 / 18:29

1 answer

Save in the database the binary content of an image Synchronized in static methods, and non-static methods

score 5 · Accepted Answer

I already found the solution.

Actually, ZAP Real Estate works with SVG in the graph section.

My macro collects information from the x coordinate points of Internet Explorer, however there is some difference in IE's SVG chart implementation for the site version in Chrome (where I was inspecting elements).

In addition to this difference in SVG implementation between browsers, I changed the code in some sections because it was updating the values of the graph before the graph had fully loaded. As it is an SVG with animation, when changing options, the animation changed the values cx and c dynamically and these were being captured by the macro before reaching their definitive value.

Follow the final macro. It is a great example of a crawler and can be easily exploited in the future.

Big hug!

Sub zap()

Dim ie As Object
    Set ie = CreateObject("InternetExplorer.Application")
    With ie
        .Visible = True
        .Navigate "http://www.zap.com.br/imoveis/fipe-zap/"
    End With


Do Until (ie.readyState = 4 And Not ie.Busy)
    DoEvents
Loop

Dim CurrentWindow As HTMLWindowProxy: Set CurrentWindow = ie.Document.parentWindow

'SELECIONA O NUMERO DE DORMITORIOS:  (0)=Todos  (1)=1 dormitório  (2)=2 dormitórios  (3)=3 dormitórios  (4)=4 dormitórios
ie.Document.All.Item("ctl00$ContentPlaceHolder1$quartosFipe")(1).Checked = True

'SELECIONA O PERÍODO:  (0)=Ano corrente  (1)=Últimos 12 meses  (2) =Todo o período
ie.Document.All.Item("ctl00$ContentPlaceHolder1$")(0).Checked = True

'SELECIONA SE É VENDA OU ALUGUEL:  (0)=Venda  (1)=Aluguel
ie.Document.All.Item("ctl00$ContentPlaceHolder1$radTransacao")(0).Checked = True

'SELECIONA A CIDADE:  (0)=FipeZap Ampliado  (1)=FipeZap Composto  (2)=Belo Horizonte  (3)=Brasilia  (4)=Curitiba  (5)=Florianopolis  (6)=Fortaleza  (7)=Niteroi  (8)=Porto Alegre  (9)=Recife  (10)=Rio de Janeiro  (11)=Salvador  (12)=Santo Andre  (13)=São Bernardo do Campo  (14)=São Caetano do Sul  (15)=São Paulo  (16)=Vila Velha  (17)=Vitoria
ie.Document.All.Item("ctl00$ContentPlaceHolder1$ddlCidadeIndiceFipeZap")(10).Selected = True

'ATUALIZA O GRÁFICO
ie.Document.parentWindow.execScript ("javascript:AtualizaGraficoIndice()")

'AGUARDA A ATUALIZAÇÃO
Application.Wait (Now + TimeValue("00:00:06"))

For Each element In ie.Document.getElementsByTagName("circle")
    x = element.getAttribute("cx")
    y = element.getAttribute("cy")
    Workbooks("FipeZap.xlsm").Worksheets("Plan1").Range("A1000000").End(xlUp).Offset(1, 0).Value = x
    Workbooks("FipeZap.xlsm").Worksheets("Plan1").Range("B1000000").End(xlUp).Offset(1, 0).Value = y
Next

End Sub