How to import data from the internet to excel vba

Question

How to import data from the internet to excel vba

Navigation

#1 by (1 votes)

2

I'm trying to download a site's table: " link , and I've already" got it ". But there's a problem with my code. I want the user to set the team. This is done by selecting in a combo box on the site, well, I even change the combo box data, however the table is not updated.

Below the code for you to 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 you want to filter, since this one comes by default 25 and I would like for at least 100 not to run the risk of getting player out of the search) ...

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

macro excel vba

asked by anonymous 31.12.2016 / 21:59

1 answer

I can not do a click counter on a JButton what is the procedure to insert 1 only 1 checkbox value in mysql with php?

score 1 · Accepted Answer

The following code follows:

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