Usando el Traductor de Google en Excel (II)

Con el lanzamiento de Windows 11, se terminó de sepultar al “viejo” Internet Explorer. Ya no es solo que no venga instalado en dicho nuevo sistema operativos, sino que es incompatible, es decir, así lo descargues, no podrías instalarlo. Todo tiene como consecuencia que aquellos/as que, en VBA y otros, usan el objeto Internet Explorer para hacer Web Scraping, pues, ya no podrán hacerlo. Por supuesto que se ha dicho muchas veces que hay formas más eficientes de hacer Web Scraping, pero es innegable que el uso de dicho navegador ha sido muy difundido para ese fin.

En mi caso hice varios ejemplos usando justamente IE, como por ejemplo usar el traductor de Google en Excel (Enlace), el cual, obviamente ya no funciona en Windows 11 (ni con el modo IE de Edge, ya que, obviamente no es el objeto IE). En esta ocasión veremos cómo poder hacerlo desde Windows 11 (y cualquier otro en realidad).

Si entramos a la web del traductor del Google y queremos traducir “hola” en inglés, quedaría así el enlace después de realizarlo:

https://translate.google.com/?hl=es&sl=es&tl=en&text=Hola&op=translate

Para este ejemplo, quitaremos la última parte y agregaremos lo siguiente:

https://translate.google.com/m?hl=es&sl=es&tl=en&q=Hola

¿Notan la “m”? Es para usar la versión móvil, que es más fácil de manejar en este caso. Lo de hl, sl y tl ya lo expliqué en el primer artículo, así que supondré que ya lo leyeron.

Algo importante es ver el código de la web, así sabremos en cuál de los elementos/objetos se da el resultado de la traducción.

Como ven, según su clase es "result-container", así con comillas, a su lado hay un signo de “mayo que”, después el texto traducido y por último el siguiente código html: “</div>” (sin las comillas). Esos son datos que usaremos después.

A la versión inicial vamos a agregar el uso del objeto MSXML2.XMLHTTP60 además de usar el método GET con dicho objeto (ya escribí sobre ellos, así que también asumo que a esta altura lo leyeron jejeje). Ah, previo a eso vamos a declarar y darle valor a la siguiente variable:

Dim objHTML As Object 
Set objHTML = New HTMLDocument 

Y ya luego vamos a agregar esto en nuestro código (No olvidar declarar las referencias a “Microsoft XML, v6.0” - el 6 puede variar dependiendo de tu versión de Office):

Dim objHTTP As New MSXML2.XMLHTTP60 

With objHTTP
     .Open "GET", web, False 
    .send
     objHTML.body.innerHTML = .responseText 
End With

Let codigodelaweb = objHTML.body.innerHTML 

Si se dieron cuenta, también he aprovechado y agregado al cuerpo del objeto HTML el texto de respuesta del objeto MSXML2.XMLHTTP60 el cual, por decirlo de un modo, el código fuente de la web resultado del GET y lo hemos pasado a una variable (no olviden declararla).

Ahora vamos a encontrar la primera posición de <div class=""result-container""> haciendo uso de InStr:

Dim posicion1&, posicion2&, posicion3& 
Let posicion1 = InStr(codigodelaweb, "<div class=""result-container"">") 

Como sabemos que después del <div class=""result-container""> está el texto traducido, ahora obtendremos la primera posición de dicho texto:

Let posicion2 = posicion1 + Len("<div class=""result-container"">")

Y, por último, usaremos nuevamente InStr para saber la última posición del texto traducido, ya que sabemos que después de él tenemos </div>.

Let posicion3 = InStr(posicion2, codigodelaweb, "</div>")

Luego, con Mid$, y ya sabiendo las diversas posiciones, extraemos el texto traducido en una variable:

Let textotraducido = Mid$(codigodelaweb, posicion2, posicion3 - posicion2)

Y listo, ya tenemos nuestro traductor de Google que funciona en Windows 11. La macro completa quedará así:

Option Explicit 

Sub Traducir() 

Dim Celda As Range, CeldaaEncontrar As Range 
Dim MiHoja As Worksheet
Dim ValoraBuscar$, PrimerIdioma$, SegundoIdioma$, web$, codigodelaweb$, textotraducido$ 
Dim posicion1&, posicion2&, posicion3& 
Dim objHTML As Object 

Set MiHoja = Worksheets("Traductor") 

If MiHoja.Range("A2") = "" Or MiHoja.Range("C2") = "" Or MiHoja.Range("D2") = "" Then
     MsgBox "No debe dejar vacío los campos del texto a traducir o los idiomas", vbOKOnly, "Todos Sobre Excel"     
     Exit Sub
End If 

If Len(MiHoja.Range("A2")) >= 4000 Then
     MsgBox "No debes exceder de 4000 caracteres, por favor cambiar. Tienes " & Len(MiHoja.Range("A2")) & " caracteres en este momento", vbOKOnly, "Todos Sobre Excel"     
	 Exit Sub 
End If 

Application.ScreenUpdating = False 

MiHoja.Unprotect "1234" 
MiHoja.Range("F1").Value = MiHoja.Range("A2").Value 

With MiHoja.Range("F1") 'Convertimos los signos % a hexadecimal, lo hacemos antes de los demás ya que % es un signo usado 
'varias veces y entrariamos casi en un bucle de convertirlo junto con todo los otros signos     
     .Replace What:="%", Replacement:="%25", LookAt:=xlPart, _
	          SearchOrder:=xlByRows 
'en este bucle convertimos los signos a hexadecimal     
     For Each Celda In Worksheets("Signos").Range("C2:C20")         
         .Replace What:=ChrW(Celda.Value), Replacement:="%" & Celda.Offset(0, -2).Value, LookAt:=xlPart, _             SearchOrder:=xlByRows
	 Next Celda 
'aquí lo hacemos con el signo ?, ya que si se usa el Replace junto con los demás signos y este, en un bucle, transforma todo
     .Replace What:="~?", Replacement:="%3F", LookAt:=xlPart, _
         SearchOrder:=xlByRows 
End With   

'El uso de Let no es necesario, se puede dar el valor sin usarlo (es equivalente), pero 
'tengo la costumbre de dejarlo. Igual, en casos como este se puede omitir Value, pero 
'considero que colocarlo permite entender mejor a que propiedad se hace referencia 
Let ValoraBuscar = MiHoja.Range("C2").Value 
Set CeldaaEncontrar = Worksheets("Signos").Range("F2:F62").Find(ValoraBuscar) 
Let PrimerIdioma = CeldaaEncontrar.Offset(0, 1).Value 
Let ValoraBuscar = MiHoja.Range("D2").Value 
Set CeldaaEncontrar = Worksheets("Signos").Range("F3:F62").Find(ValoraBuscar) 
Let SegundoIdioma = CeldaaEncontrar.Offset(0, 1).Value 
Set objHTML = New HTMLDocument 

web = "https://translate.google.com/m?hl=" & PrimerIdioma & "&sl=" & PrimerIdioma & "&tl=" & SegundoIdioma & "&q=" & MiHoja.Range("F1").Value 

'Creamos el objeto MSXML2.XMLHTTP60 
Dim objHTTP As New MSXML2.XMLHTTP60 

With objHTTP
     .Open "GET", web, False
	 .send
	 objHTML.body.innerHTML = .responseText 
End With 

Let codigodelaweb = objHTML.body.innerHTML 

'Encontramos la posición del texto traducido dentro del código fuente de la web 
Let posicion1 = InStr(codigodelaweb, "<div class=""result-container"">") 
Let posicion2 = posicion1 + Len("<div class=""result-container"">") 
Let posicion3 = InStr(posicion2, codigodelaweb, "</div>") 
Let textotraducido = Mid$(codigodelaweb, posicion2, posicion3 - posicion2) 

With MiHoja
     .Range("A5").Value = textotraducido
	 .Range("F1").ClearContents
	 .Protect "1234" 
End With 

'En módulos estándar no es necesario colocar en True el ScreenUpdating ya que con el 
'End Sub lo hace, pero es mi costumbre de programador colocarlo 
Application.ScreenUpdating = True 

'Asimismo, el End Sub libera las variables, pero siempre uso el Nothing por costumbre de programador 
Set CeldaaEncontrar = Nothing: Set objHTML = Nothing: Set MiHoja = Nothing 

MsgBox "Traducción realizada", vbOKOnly, "Todos Sobre Excel" 

End Sub 

Espero les guste, hasta la próxima.

Abraham Valencia
Lima, Perú

Descargue el ejemplo aquí

Interactuando con internet (III): Actualizar y extraer datos de tablas web

Ya en artículos anteriores hemos aprendido como interactuar con una página web (Enlace) y también a como extraer datos de tablas ubicadas en webs (Enlace). En el último caso lo hicimos con tablas cuyos datos eran estáticos, es decir, no cambiaban. Lo que haremos ahora es extraer datos de tablas cuyos datos pueden cambiar.

Para el ejercicio vamos a usar esta web como ejemplo: Enlace. Como vemos, hay una tabla, llamémosla, principal, cuyos datos dependen de una lista desplegable con tres opciones y un control de fechas (“picker”). Si bien hay un botón “Descargar datos”, en esta ocasión lo que nos interesa es extraer los datos directamente desde la web.

Lo primero es obtener el nombre, id o clase de los objetos que queremos usar así que usaremos la herramienta “Inspeccionar” de Chrome.

Entonces, el id del combobox es “data_interval” y el del picker es, justamente, “picker”. Unos detalles importantes a tomar en cuenta son qué; en el combobox hay tres opciones por ende, y viendo el orden, usaremos la propiedad “SelectedIndex” para elegir lo que deseamos (no olvidar que los índices comienzan en 0) y lo otro es que, veremos la forma en que en el picker están ingresados los valores de fechas predeterminadas, lo que nos da luces de como ingresarlos nosotros. Otro detalle es que cuando se elige una opción en el combobox o en el picker, los datos se actualizan de manera automática. Esto último es algo a lo que debemos tomar mucha atención pues, como verán, si la web no detecta estás acciones, no actualizará los datos de la tabla.

Entonces, si queremos elegir “Mensual” y fechas entre 01/01/2017 y el 01/03/2019, nuestro código quedaría así (no olvidar activar las referencias a “Microsoft HTML Object Library”):

Dim IE As Object
Dim doc As HTMLDocument 
Dim htmTable As HTMLTable

Set IE = CreateObject("InternetExplorer.Application") 
IE.navigate "https://es.investing.com/equities/cellnex-telecom-historical-data" 

Do Until IE.ReadyState = 4
     DoEvents 
Loop 

With IE
     .document.getElementById("data_interval").selectedIndex = 2
     .document.getElementById("picker").Value = "01/01/2017 - 01/03/2019"
     .Visible = True 
End With 

Set IE = Nothing 

La web cargada se verá así:

Como ven el combobox nos muestra “Mensual” pero ¡el picker no cambió las fechas! ¿Qué pasó? Si le damos un simple click al botón del picker que despliega los calendarios, veremos esto:

¡Aparecen las fechas enviadas con VBA! Es decir, sí fueron ingresadas pero no mostradas. Ah, otro dilema es que ¡la tabla no actualizó los datos! Pero si presionamos el botón “Aceptar” que aparece en la imagen anterior lograremos actualizar los datos. Entonces, usando el “Inspeccionar” de Chrome veremos que el id del botón para desplegar el picker es “widget” y el del botón “Aceptar” es “applyBtn” y para que las fechas se vean tal cual las mandamos y se actualice la tabla lo que haremos ahora es desplegar los calendarios del picker, enviarle un click al botón “Aceptar” y replegar el picker. Entonces, en nuestro código la parte del “With” debería quedar así:

With IE
     .document.getElementById("data_interval").selectedIndex = 2
     .document.getElementById("picker").Value = "01/01/2017 - 01/03/2019"
     .document.getElementById("widget").Click
     .document.getElementById("applyBtn").Click
     .document.getElementById("widget").Click
     .Visible = True 
End With 

Corremos la macro y ahora sí tenemos la tabla actualizada:

Ahora sí, solo nos falta extraer los datos de la tabla. Como ya hemos visto cómo hacerlo en un artículo anterior, básicamente usaremos los mismos códigos para lograrlo, pero en esta ocasión en lugar de usar el objeto “Tag” y el índice de la tabla (aunque también podríamos usarlo) lo haremos a través de su id que ya vimos es “curr_table”. Como no en realidad no es de nuestro interés ver la web, nos bastará trabajar en ella sin mostrarla e incluso cerraremos el Internet Explore usando “Quit”. Ah, por cierto, agregaremos un “Application.Wait” para dar un segundo para asegurar de que los datos de la tabla tengan tiempo de cargar. Nuestra macro quedará así:

Sub ExtraerDatos()

Dim IE As Object 
Dim doc As HTMLDocument 
Dim htmTable As HTMLTable 
Dim nFilas As Integer, nColumnas As Integer, x As Integer, y As Integer 

Set IE = CreateObject("InternetExplorer.Application") 
IE.navigate "https://es.investing.com/equities/cellnex-telecom-historical-data" 

Do Until IE.ReadyState = 4
     DoEvents 
Loop 

With IE
     .document.getElementById("data_interval").selectedIndex = 2
     .document.getElementById("picker").Value = "01/01/2017 - 01/03/2019"
     .document.getElementById("widget").Click
     .document.getElementById("applyBtn").Click
     .document.getElementById("widget").Click 
End With 

Application.Wait (Now + TimeValue("00:00:01")) 

Set doc = IE.document 
Set htmTable = doc.getElementById("curr_table") 
Let nFilas = htmTable.Rows.Length 
Let nColumnas = htmTable.Rows(0).Cells.Length 

For x = 1 To nFilas
      For y = 1 To nColumnas
          Cells(x, y).Value = htmTable.Rows(x - 1).Cells(y - 1).innerText
      Next y 
Next x 

IE.Quit 

Set IE = Nothing: Set doc = Nothing: Set htmTable = Nothing 

End Sub  

Como verán, en nuestra hoja activa hallaremos todos los datos que hemos elegido y ni siquiera fue necesario que se vea el Internet Explorer. Espero se haya entendido. Hasta la próxima.

Abraham Valencia