VBA: Web scraping con QueryTables

Repasaremos hoy un viejo amigo, en desuso casi, como era el Importador de datos Web (recuerda otros tiempos)... pero desde su lado de programación.
Aunque en las versiones 'modernas' esta posibilidad ya no está a simple vista, dentro del editor de VBA sigue estando plenamente operativo.
Por tanto, esto nos permite recuperar datos de tablas de un web, i.e., hacer WEB Scraping, pero sin emplear los navegadores y/o sus librerias necesarias!!!

Así pues emplearemos el objeto QueryTables y alguno de sus métodos (.Add) y propiedades (.Connection, .Destination, .WebSelectionType, .WebTables, .Name, . Refresh, etc.)

Veamos el código insertado en un módulo estándar de nuestro editor de VB.

Sub WebQueryTable()

Dim url As String
'indicamos la web de donde obtendremos la información
url = "https://es.wikipedia.org/wiki/IBEX_35"

Dim tbl As QueryTable
'verificamos que solo trabajamos con una sola query en la hoja
'la que se llame 'Datos_Excelforo_1'(OJO con el nombre!!)
If Hoja1.QueryTables.Count > 0 Then 'si existe alguna consulta...
    'reasignamos el nombre a la primera consulta
    'OJO y cuidado si tienes más consultas en la misma hoja
    'así evitamos posibles fallos... también puedes controlar el fallo con On Error Resume Next
    Hoja1.QueryTables.Item(1).Name = "Datos_Excelforo_1"
    'nos referimos a nuestra consulta creada en una ejecución previa
    Set tbl = Hoja1.QueryTables("Datos_Excelforo_1")
    
    With tbl
        .Connection = "URL;" & url
        .Destination = Hoja1.Cells(1)
        
        .Refresh    'refrescamos la consulta
    End With
Else
    'creamos la query
    Set tbl = Hoja1.QueryTables.Add("URL;" & url, Hoja1.Cells(1))
    'y definimos sus condiciones
    With tbl
        .Name = "Datos_Excelforo_1"
        .WebSelectionType = xlSpecifiedTables  'xlAllTables 'xlEntirePage  'xlSpecifiedTables
        .WebTables = "3"        'debe ser un String, o una cadena "1,2,3"
        .WebFormatting = xlWebFormattingRTF 'xlWebFormattingAll 'xlWebFormattingNone 'xlWebFormattingRTF
        
        .Refresh    'refrescamos la consulta
    End With
End If

End Sub

Básicamente el código anterior realiza un proceso donde la primera vez que se ejecuta crea la consulta en la Hoja1, en la celda A1:
Set tbl = Hoja1.QueryTables.Add("URL;" & url, Hoja1.Cells(1))
Recuperando de la URL informada una tabla específica, la número 3 en concreto.

En siguientes ejecuciones de la macro, detecta si existe alguna consulta en esa Hoja1 y simplemente refresca la conexión previamente creada.
En el ejemplo, para evitar posibles fallos, he incluido un par de líneas donde marca la primera consulta de la hoja con el nombre 'Datos_Excelforo_1'.
Si bien, esto se podría controlar con alguna sentencia del tipo On Error Resume Next

Un punto importante es reconocer cuál es el número de tabla que queremos recuperar!!.
Normalmente ejecutaríamos, la primera vez, la macro anterior con una modificación. En lugar de:

With tbl
        .Name = "Datos_Excelforo_1"
        .WebSelectionType = xlSpecifiedTables  'xlAllTables 'xlEntirePage  'xlSpecifiedTables
        .WebTables = "3"        'debe ser un String, o una cadena "1,2,3"
        .WebFormatting = xlWebFormattingRTF 'xlWebFormattingAll 'xlWebFormattingNone 'xlWebFormattingRTF
        
        .Refresh    'refrescamos la consulta
    End With

Tendríamos:

With tbl
        .Name = "Datos_Excelforo_1"
        .WebSelectionType = xlAllTables  'xlAllTables 'xlEntirePage  'xlSpecifiedTables
        '.WebTables = "3"        'debe ser un String, o una cadena "1,2,3"
        .WebFormatting = xlWebFormattingRTF 'xlWebFormattingAll 'xlWebFormattingNone 'xlWebFormattingRTF
        
        .Refresh    'refrescamos la consulta
    End With

Lo que nos devolvería la totalidad de las tablas existentes en la web, separadas por una fila en blanco, en nuestra hoja destino...
Nos queda identificar cuál es y qué posición (1,2,3,..) ubica, y con esa información, volveríamos al código indicado más arriba para informar en .WebTables el valor o lista de valores de tablas a recuperar.

Un último apunte respecto al .Name de la consulta.
Notarás que en ocasiones el nombre aparece numerado (xxx_1 por ejemplo), a pesar que en tu código hayas puesto solo 'xxx'...
Esto ocurrirá siempre que previamente hubiera existido una consulta con el nombre 'xxx' (aunque haya existido años atrás!!), en ese caso las nuevas consultas que intentes renombrar como 'xxx' automáticamente se designarán como 'xxx_1', 'xxx_2', etc.
Por este motivo, es recomendable, desde un principio, añadir al nombre ese sufijo _n.

Una sencilla macro que te ayudará a eliminar 'de verdad' las consultas existentes (aunque las elimines manualmente desde el panel de consultas y conexiones parece quedan como resistentes..) sería:

Sub EliminaQuerys()
For Each qr In Hoja1.QueryTables
    qr.Delete
Next qr
End Sub

Espero te sea de utilidad ;-)