How to import data from internet to excel vba

Question

I’m trying to download a table from the site: "http://www.scoutscartola.com/mercado", and I already "got". But there is a problem in my code. I want the user to define the team. This is done by selecting a combo box on the site, well, I even change the combo box data, but the table is not updated.

Down with the code so you can see what I’m talking about.

Sub navega_cartola_fc()

   Dim ie As Object
   Dim Nome As String

   Set ie = New InternetExplorer

   Nome = "Botafogo"

   ie.navigate "http://www.scoutscartola.com/mercado"

   ie.Visible = True

   While ie.Busy: Wend

   For Each obj In ie.Document.all.Item("data[filtro_time]")

        If obj.innerText = Nome Then

            obj.Selected = True

        End If

   Next obj

   Dim r, c As Integer
   Dim elemCol As Object

   Set elemCol = ie.Document.getElementsByTagName("tbody")

   Application.Wait TimeSerial(Hour(Now()), Minute(Now()), Second(Now()) + 3)

   For r = 0 To elemCol(0).Rows.Length - 1

     For c = 0 To elemCol(0).Rows(r).Cells.Length - 1

       ActiveSheet.Cells(r + 1, c + 1) = elemCol(0).Rows(r).Cells(c).innerText

     Next c

   Next r

After correctly activating this combo box, excel should also change another combo box at the bottom of the site (one that says how many players want to filter, because this comes by default 25 and I would like to at least 100, not to risk staying player out of the search)...

Would anyone know what to do in this case? Because all the materials I see, the person changes the combo box as I did, but there is a button on the site itself that "updates" the data. But in this my case has no update button, the update is done so the team is selected in combo box - by the site - and this does not occur when I do by vba.

Answer 1

Follow the following code:

Sub navega_cartola_fc()

  Dim ieApp As Object
  Dim Nome As String

  Set ieApp = New InternetExplorer

  Nome = "Botafogo"

  ieApp.navigate "http://www.scoutscartola.com/mercado"

  ieApp.Visible = True
  While ieApp.Busy: Wend

  'atualiza a pagina depois de preencher os valores dos campos a procurar
  Do While ieApp.Busy: DoEvents: Loop
  Do Until ieApp.readyState = READYSTATE_COMPLETE: DoEvents: Loop

  'altera o valor da combobox
  Set ieDoc = ieApp.Document
  Set ObjAaa =ieDoc.getElementById("tmercado_length").getElementsByTagName("select")(0)
  Dim evt As Object
  Set evt = ieDoc.createEvent("htmlevents")
  evt.initEvent "change", True, False

  ObjAaa.Value = 100
  'Vai selecionar este valor -> <option value="-1">1000</option>
  'Se quiseres outro valor altera consoante o valor do "value"
  ObjAaa.dispatchEvent evt

  '<<<< new update >>>>
  Set ieDoc1 = ieApp.Document
  Set ObjAaaTime = ieApp.Document.all.Item("data[filtro_time]")(0)
  Dim evt1 As Object
  Set evt1 = ieDoc.createEvent("htmlevents")
  evt1.initEvent "change", True, False

  ObjAaaTime.Value = Nome
  'Vai selecionar este valor -> <option value="botafogo">Botafogo</option>
  'Se quiseres outro valor altera consoante o valor do "value"
  'se vais fornecer este valor pelo excel deves de criar uma espécie de uma lista fixa com todos os valores possíveis
  ObjAaaTime.dispatchEvent evt1

  'volta a atualizar a página
  Do While ieApp.Busy: DoEvents: Loop
  Do Until ieApp.readyState = READYSTATE_COMPLETE: DoEvents: Loop

  Dim r, c As Integer
  Dim elemCol As Object

  Set elemCol = ieApp.Document.getElementsByTagName("tbody")

  Application.Wait TimeSerial(Hour(Now()), Minute(Now()), Second(Now()) + 3)

  For r = 0 To elemCol(0).Rows.Length - 1
      For c = 0 To elemCol(0).Rows(r).Cells.Length - 1
          ActiveSheet.Cells(r + 1, c + 1) =elemCol(0).Rows(r).Cells(c).innerText
      Next c
  Next r

  ieApp.Quit

  Set elemCol = Nothing
  Set ieApp = Nothing

 End Sub