Download html table with vba excel

Question

Download html table with vba excel

Navigation

#1 by (1 votes)

0

Hello everyone and thanks in advance.

I'm trying to download a table to excel from the page mismarcadores.com.

I've got a macro on the internet. The problem is that the html tag of the table has no ID, and whenever I execute the macro, the following error occurs:

Error 438 occurred at runtime: The object does not support this property or method

The macro that I execute is the following:

  Public Sub importartblhtml()

    Dim appIE As Object
Dim allRowOfData As Object
Dim curHTMLRow As Object
Dim Fila As Long, Col As Long
Dim Celda As Object

Set appIE = CreateObject("internetexplorer.application")

With appIE

    .Visible = True
    .navigate "https://www.mismarcadores.com/futbol/espana/laliga/partidos/"

    While .Busy = True Or .readyState < 4: DoEvents: Wend

 ThisWorkbook.Sheets("Hoja1").Activate
 Cells.Select
 Selection.ClearContents

    Set allRowOfData = appIE.document.getElementsById("")

    For Fila = 1 To allRowOfData.Rows.Length - 1

        Set curHTMLRow = allRowOfData.Rows(Fila)

        For Col = 0 To curHTMLRow.Cells.Length - 1

            Set Celda = Sheets("Hoja1").Cells(Fila + 1, Col + 1)

            Celda.Value = "'" & curHTMLRow.Cells(Col).innertext

        Next Col

    Next Fila

    .Quit

End With

Set appIE = Nothing
Set curHTMLRow = Nothing
Set Celda = Nothing
End Sub

The error jumps when the line is executed

Set allRowOfData = appIE.document.getElementsById("")

I've tried with the getElementsByTag("table")(0) method and it does not work either It matters the whole page except what I want, which are the matches.

I have not found much information on the internet about how to solve the error.

The html code from where I want to get the data is the following

Does anyone know if I need to activate a reference or some other method to download this table?

Thanks.

Greetings.

html vba macros excel

asked by Fco. Javier 02.08.2018 в 20:42

source

1 answer

Run PHP with cron Allow special characters in .htaccess

score 1 · Answer 1

You can copy paste the "outerHtml" of the tournament table with the ID (Sorry for my Spanish)

id

Show:

VBA:

Option Explicit
Public Sub importartblhtml()
    Dim appIE As Object, ws As Worksheet, hTable As Object, clipboard As Object
    Application.ScreenUpdating = False
    Set ws = ThisWorkbook.Sheets("Hoja1")
    Set appIE = CreateObject("internetexplorer.application")

    With appIE
        .Visible = True
        .navigate "https://www.mismarcadores.com/futbol/espana/laliga/partidos/"

        While .Busy = True Or .readyState < 4: DoEvents: Wend
        Set hTable = .document.getElementById("#fs-fixtures")
        .Quit
    End With
    With ws
        .Cells.ClearContents
        Set clipboard = New MSForms.DataObject
        'Set clipboard = GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
        clipboard.SetText hTable.outerHTML
        clipboard.PutInClipboard
        .Cells(1, 1).PasteSpecial
    End With
    Application.ScreenUpdating = True
End Sub

References:

Microsoft Forms 2.0 Object Library